Solved

ADO Delete Table

Posted on 2002-06-04
6
531 Views
Last Modified: 2013-11-23
Hi
How do you access the tables collection in ADO & Jet and how do you then delete a table?
0
Comment
Question by:gregbinns
  • 4
6 Comments
 
LVL 17

Accepted Solution

by:
inthedark earned 100 total points
ID: 7055472
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 + ";"
CN.Open

' 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
CN.Close
Set CN = Nothing

msgbox "DONE"
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7055474
The example could ahve done with a comment like this:


' Create an Index
Set aIndex = New ADOX.Index


 
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7055767
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 17

Expert Comment

by:inthedark
ID: 7055865
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


etc......

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
Else
    DeleteTableOK = ExecuteSQLOK(CN, SQL)
End If


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

' Returns the last error on a connection

'Example:
' 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
Next
   
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"))
Do
    If Err.Number <> 0 Then
        TableExists = False
        Exit Do
    End If
    If RS.EOF Then
        TableExists = False
    Else
        TableExists = True
    End If
    RS.Close
    Exit Do
Loop

Set RS = Nothing

End Function



0
 

Author Comment

by:gregbinns
ID: 7058129
Thanks I used this method and it worked to solve another problem as well.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7058234
There is so much to learn.....thanks for the points.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to Add / Edit Windows Menu 4 67
How to make an ADE file by code? 11 94
How to Get Images From Server using App Tethering 11 39
MS Access from Delphi 31 59
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question