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

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! :clap:

Code:

---

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

---

Quote:

---

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.

Code:

---

rs.Open "HFM", cn, adOpenKeyset, adLockOptimistic
Do Until rs.EOF '<<<loop until End of File marker is reached
    rs.Delete
    rs.MoveNext
Loop

---

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.

Code:

---

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

---

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

HTH

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: 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:bug: