Delete an Access table 1

[jopaumier]

Using ADO to connect to an Access database. If the table exists in the mdb, how do I delete it? Optionally, since I will be recreating the table (possibly with different data), how do I delete all records and leave the table structure intact?

Jim

 

[cass60660]

you can use :

strSql = "drop table Table1"
connection.execute strSql

success

 

[jopaumier]

Cass,

Yup, success! Thanks. Along the same lines, how would I check to see if the table exists before I try to delete it? Right now I have On Error Resume Next, so the program just goes on, but that seems not so elegant a solution. I'd rather check before I delete.

Jim

 

[cass60660]

You can try something like this:

Private Sub Command6_Click()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables
'collection.
For Each obj In dbs.AllTables
' Print name of obj.
Debug.Print obj.Name
if obj.Name = "your_table" then runDelete
Next obj
End Sub

Success(again)