Solved

ADO Delete Table

Posted on 2002-06-04
6
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Technology Partners: 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!

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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