Solved

ADO Delete Table

Posted on 2002-06-04
6
532 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
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!

 
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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

730 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