Monday, September 8, 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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog