Monday, 8 September 2014

How to Delete all Records from a MS Access Table with Excel VBA Macro

Excel Macro to Delete all Records from a MS Access Table

Sub Delete_All_Records_Table()
Dim Str_MyPath As String, Str_DBName As String, Str_DB As String, Str_SQL As String
Dim ADO_RecSet As New ADODB.Recordset
Dim Conn_DB As New ADODB.Connection
Dim Str_SQL 
Dim DB_Table

Str_DBName = "Test_DB.accdb"
Str_MyPath = "C:\Users\Tamatam\Desktop\Temp"
Str_DB = Str_MyPath & "\" & Str_DBName

Conn_DB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & Str_DB
Set ADO_RecSet = New ADODB.Recordset
DB_Table = "MyTable"

Str_SQL = "Delete*from MyTable"  'Here you can Pass any SQL Query to Perform

ADO_RecSet.Open Source:=Str_SQL, ActiveConnection:=Conn_DB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

Conn_DB.Close
Set ADO_RecSet = Nothing
Set Conn_DB = Nothing
MsgBox "All Records from Table has been Deleted SuccessFully", vbOKOnly, "Job Done"

End Sub

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts