Solved: Delete all records in Access using Excel VBA

[White_Nova]

Hi All

Here is a challenging one (well for me anyway)...

I have an excel spreadsheet that updates and edits data that is stored in Access (using VBA).

I would now like to reuse this in a new program, but this time i need to clear the database before uploading new data to it...

How would i go about using a delete function in Excel VBA???

Thanks again for all your help (no other forums seem to be as good)...

White_Nova

[matthewspatrick]

If you instantiate the Access.Application object, you can do this to clear tables:

Dim acApp As Object
 
Set acApp = CreateObject("Access.Application")
With accApp
.OpenCurrentDatabase "path to the mdb file"
With .DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM SomeTable"
.SetWarnings True
End With
'other code here perhaps
.CloseCurrentDatabase
.Quit
End With
 
Set acApp = Nothing

[White_Nova]

Hi Patrick

Perfect thanks, just wanted to chec something though.
I have specified the table name under the [VBA].RunSQL "DELETE * FROM SomeTable"[/VBA] part as Sales but it says the Microsoft engine could not find that table or query... any ideas?

[matthewspatrick]

Please post the exact line as you are trying to use it.

[White_Nova]

Hi Patrick, no matter, i found the problem out, was a fat fingers mistake...

wanted to ask though, is this possible:

1. using your code to delete the data in access then
2. upload new data to the database then
3. bring the data back to excel BUT delete the records these records straight after (in same code preferable)

Steps 1 and 2 i have been able to do successfully...

[White_Nova]

anybody that can help with this one?

[White_Nova]

Just to clarify

Im using ADO to bring the data back to Excel from Access, i am however brining the data back bit by bit according to certain criteria, what i need to do is delete the specific from access right after i have them in excel...

hope this helps...

[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