Solved: Delete all records in Access using Excel VBA

[White_Nova]

Hey all

found the answer, posted it on KBase...

here is a copy for your viewing:

Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
    "", ""
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon

End Sub

Sub Can()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim SQL As String
Dim filenm As String

Sheets("Cancelled").Select
SQL = "Select * FROM [tblsal]" & _
    "   WHERE [tblsal].[Status]= '" & Range("C1").Value & "'"
filenm = (ActiveWorkbook.Path & "\Store.mdb")
Call GetCn(adoconn, adors, SQL, filenm, "", "")
Dim xlSht As Excel.Worksheet
Set xlSht = Sheets("Cancelled")
xlSht.Range("A6").CopyFromRecordset adors
SQL = "Delete * FROM [tblsal]" & _
    "   WHERE [tblsal].[Status]= '" & Range("C1").Value & "'"
Call GetCn(adoconn, adors, SQL, filenm, "", "")
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlSht = Nothing
End Sub