delete records in Access through ABODB in Excel

[goman19]

I have experience coding in several languages (VBA not being one of them) and am trying to teach myself enough VBA to write intelligent macros in Excel and Access. I have gleaned a lot of the necessary code off the internet and managed to figure out how to add Excel records into Access but want to clear out existing Access records in this database first. The relevant code looks like this:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _"Data Source=C:\Documents and Settings\...\Desktop\testDB.mdb;"
Set rs = New ADODB.Recordset
rs.Open "PartInfo", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.Delete
.Update
End With

The actual ".Delete" command replaces all fields on the first row with "#Deleted" but doesn't actually complete the delete unless I close down the database. Moreover, it does not delete the entire table. I would like to replace this with a command or commands to clear out the whole thing at once. Does anyone know how to delete the contents of a database table wholesale through this ADODB connection?

 

[PHV]

Something like this ?
cn.Execute "DELETE FROM PartInfo"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,


Instead of opening the TABLE do a DELETE From YourTable COMMAND.

Skip,

_[red][/red]

 

[goman19]

Perfect!

 

[goman19]

Skip, I don't follow. Think of me as the worst programmer you've ever met. I can read code and understand it but I need the details thoroughly explained.

 

[SkipVought]

rs.Open "DELETE From PartInfo", cn, adOpenKeyset, adLockOptimistic, adCmdText

Skip,

_[red][/red]

 

[JerryKlmns]

The fast way

Dim lRecs
cn.Execute "DELETE FROM PartInfo", lRecs, 129 ' adCmdText+ adExecuteNoRecords

MsgBox lRecs & " records deleted.