[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

ADO Delete Table

How do you access the tables collection in ADO & Jet and how do you then delete a table?
  • 4
1 Solution
Here is an adox examples of table management create but as with DAO once a field (column in ADOX) has been created
some of the items are hard to change.

' make project references to:
' Microsoft ActiveX Data Objects 2.x
' Microsoft ADO Ext DLL & Security

Dim CN As ADODB.Connection
Dim AccessMDBFile As String

AccessMDBFile = "D:\test.mdb"
Set CN = New ADODB.Connection

CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessMDBFile + ";"

' here are the main adox table/field objects
Dim aCat As ADOX.Catalog
Dim aTable As ADOX.Table
Dim aKey As ADOX.Key
Dim aIndex As ADOX.Index
Dim aCol As ADOX.Column ' a field object

' first get access to the Catalog
Set aCat = New ADOX.Catalog
Set aCat.ActiveConnection = CN

' now you can work with a table

Set aTable = New ADOX.Table

aTable.Name = "New Table3"
aCat.Tables.Append aTable     ' save the new table

Set aTable = aCat.Tables("New Table3")

Set aCol = New ADOX.Column

aCol.Name = "Field1"
aCol.DefinedSize = 20
aCol.Type = adVarWChar ' VB autosuggests here which helps

aTable.Columns.Append aCol  ' save the new field
Set aCol = Nothing

' create another column
Set aCol = New ADOX.Column
aCol.Name = "Field2"
aCol.DefinedSize = 10
aCol.Type = adVarWChar ' VB autosuggests here which helps

aTable.Columns.Append aCol  ' save the new field
Set aCol = Nothing

' Create a key
Set aKey = New ADOX.Key
aKey.Name = "Primary"
aKey.Type = adKeyPrimary ' you can only have one primary key
aKey.Columns.Append "Field1"
aKey.Columns.Append "Field2"

aTable.Keys.Append aKey ' save the key

Set aIndex = New ADOX.Index
aIndex.Name = "ByField2"

aIndex.Clustered = False
aIndex.Columns.Append "Field2"
aIndex.Columns.Append "Field1"

aTable.Indexes.Append aIndex ' save the index

Set aTable = Nothing

' Delete table
aCat.Tables.Delete "New Table3"

Set aCat = Nothing
Set CN = Nothing

msgbox "DONE"
The example could ahve done with a comment like this:

' Create an Index
Set aIndex = New ADOX.Index

That works, though by far the simplest method is this:

ADOConnection.Execute "Drop Table MyTable"

Another alternative to using ADOX is the .OpenSchema method of the ADO connection object, this exposes the database schema though you cannot manipulate it directly with this method.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tim's method is simplest but the full syntax which you will need if you have spaces etc. in your table names is:

ADOConnection.Execute "Drop Table [My Table]"

Or use a function becuase you can get bugs in your program becuase the table hasn't been created yet, so that needs to be handled:

OK = DeleteTableOK(CN, "My Table")
If Not OK Then
    Msgbox "Table in use!" + vbcrlf + ADO.GetLastError(CN)
End If


Function DeleteTableOK(CN As ADODB.Connection, TableName As String, Optional CheckExists As Boolean = False) As Boolean

Dim SQL As String
Dim ok

SQL = "Drop Table [" + TableName + "]"
If CheckExists Then
    If TableExists(CN, TableName) Then
        DeleteTableOK = ExecuteSQLOK(CN, SQL)
    End If
    DeleteTableOK = ExecuteSQLOK(CN, SQL)
End If

End Function
Public Function GetLastError(CN As ADODB.Connection) As String

' Returns the last error on a connection

' OK = ADO.ConnectOK(CN)
' If Not OK Then
'      MsgBox ADO.GetLastError(CN)

If CN Is Nothing Then
    GetLastError = "Connection is invalid"
    Exit Function
End If

Dim m$

Dim E As ADODB.Error
Dim Elist As ADODB.Errors
Set Elist = CN.Errors
For Each E In Elist
    m$ = m$ + CStr(E.Number) + " " + E.Description + " " + E.Source + " " + E.SQLState + vbCrLf
GetLastError = m$

End Function

Function TableExists(CN As ADODB.Connection, TableName As String) As Boolean

Dim RS As ADODB.Recordset
On Error Resume Next
Set RS = CN.OpenSchema(adSchemaTables, Array(Empty, Empty, TableName, "TABLE"))
    If Err.Number <> 0 Then
        TableExists = False
        Exit Do
    End If
    If RS.EOF Then
        TableExists = False
        TableExists = True
    End If
    Exit Do

Set RS = Nothing

End Function

gregbinnsAuthor Commented:
Thanks I used this method and it worked to solve another problem as well.
There is so much to learn.....thanks for the points.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now