How to replace access data instead of adding to it with EXCEL macro & data

[truzilla]

Hi all,

So I created this macro below that essentially imports data from an excel spreadsheet to a specified table within an access database. I based it off of another one I found online.

Currently, I need a change that will replace the existing access data in the table instead of adding to it, which it currently does. Any help would be greatly appreciated!! Thanks!

[VBA]Sub WORKINGIMPORTER()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\Test dB.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "HFM", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Range("A" & r).Value
.Fields("Region") = Range("B" & r).Value
.Fields("LVL4") = Range("C" & r).Value
.Fields("Revenue") = Range("D" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub[/VBA]

[CreganTur]

Currently, I need a change that will replace the existing access data in the table instead of adding to it

What exactly do you mean by 'replace'? Do you want to overwrite the table every time you run your import, or do you only want to overwrite certain records?

If you want to overwrite the entire table, then you can accomplish that by 1 of 2 different methods.

1) Delete all records in the table.

I see that you'r familiar with ADO, so what you could do is add a Delete method to your above connection that will delete all of the records in the table.
[vba]rs.Open "HFM", cn, adOpenKeyset, adLockOptimistic
Do Until rs.EOF '<<<loop until End of File marker is reached
rs.Delete
rs.MoveNext
Loop[/vba]

The above is untested, but it's a basic loop to move through each record and then delete it.

2) Delete the Table

You could look at the table catalog object in the database and delete the entire table. - requires reference to Microsoft ADO Ext 2.8 for DDL and Security.

[vba]Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = cn
cat.Tables.Delete "HFM"
Set cat = Nothing[/vba]

But, of course, if you delete the table then you'll have to recreate it next.

HTH

NinjaEdit: forgot to add the MoveNext method to the delete code posted above.

Once you delete a a record via ADO, the record you deleted remains the current record. If you don't use a move command, then trying to do anything to the current record will generate a run-time error...since you're trying to mess with a deleted record