View Full Version : Solved: Delete all records in Access using Excel VBA
White_Nova
01-10-2008, 05:34 AM
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
01-11-2008, 07:38 AM
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
01-14-2008, 03:30 AM
Hi Patrick
Perfect thanks, just wanted to chec something though.
I have specified the table name under the .RunSQL "DELETE * FROM SomeTable" part as Sales but it says the Microsoft engine could not find that table or query... any ideas?
matthewspatrick
01-14-2008, 07:35 AM
Please post the exact line as you are trying to use it.
White_Nova
01-15-2008, 07:30 AM
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
01-16-2008, 05:44 AM
anybody that can help with this one?
White_Nova
01-16-2008, 06:18 AM
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
01-17-2008, 11:22 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.