gregbinns
asked on
ADO Delete Table
Hi
How do you access the tables collection in ADO & Jet and how do you then delete a table?
How do you access the tables collection in ADO & Jet and how do you then delete a table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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(adSchemaTabl es, 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
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(adSchemaTabl
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
ASKER
Thanks I used this method and it worked to solve another problem as well.
There is so much to learn.....thanks for the points.
' Create an Index
Set aIndex = New ADOX.Index