Delete an Access table using vba

[slamet Harto]

Guys,

Can you show me a way to delete a table in MS Access Database using VBA

I've browse in this best forum ("VBAX") with no luck

Thank you
Regards,
Harto

[CreganTur]

This code will delete the specified table via the ADOX catalog.

Sub DeleteTable(strTblName As String)

Dim conn As ADODB.Connection
Dim cat As ADOX.Catalog

On Error GoTo ErrorHandler

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.jet.OLEDB.4.0;" & _
"Data Source=C:\Acc07_ByExample\Northwind.mdb" '<<<Change to your DB
Set cat = New ADOX.Catalog
cat.ActiveConnection = conn
cat.Tables.Delete strTblName

Set cat = Nothing
conn.Close
Set conn = Nothing
Exit Sub

ErrorHandler:
MsgBox "Table " & strTblName & "cannot be deleted because it does not exist."
Resume Next

End Sub

[Bob Phillips]

You're not wrong



Public Function CreateAccessDatabase()
Const DB_PATH As String = "C:\test.mdb"
Dim Conn As Object
Dim sSQL As String

Set Conn = CreateObject("ADODB.Connection")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\test\Test.mdb"

sSQL = "DROP TABLE MyTable"
Conn.Execute sSQL

Set Conn = Nothing
End Function

[CreganTur]

You're not wrong

Yeah... a DDL query would probably be faster. Didn't think of that.