Databases Reference
In-Depth Information
On Error GoTo Table_Exists:
'Get default Workspace
Set ws = DBEngine.Workspaces(0)
'Establish a DSN-less connection to the MCS Sample Data Base
ConnectString = "ODBC;DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=" & fullmdbpath
Set MCSdb = DBEngine.OpenDatabase(fullmdbpath, , , ConnectString)
'Begin the Transaction
ws.BeginTrans
' Add a Column to the Specified Table
Set tbl = MCSdb.TableDefs(TableName)
'Create a Reference to the Table to be Operated On
tbl.Fields.Delete ColumnName
'Commit the Changes
ws.CommitTrans
' Refresh the cache to ensure that the latest data is available.
DBEngine.Idle dbRefreshCache
MCSdb.Close: ws.Close
Set MCSdb = Nothing: Set tbl = Nothing: Set ws = Nothing
DeleteFieldDAO = True
Exit Function
Table_Exists:
Debug.Print Error$, Err
MsgBox "Error: " & Err & ": " & Error$, vbOKOnly + vbCritical,
"Error Accessing: " & fullmdbpath
DeleteFieldDAO = False
End Function
A similar function has been constructed to allow fields to be deleted using SQL. Notice that
the ALTER command is again used in SQL along with the DROP command to delete an existing
field in a database. This function also returns TRUE upon success.
Function DeleteFieldSQL(fullmdbpath As String, TableName As
String, _
FieldName As String) As Boolean
'Deletes a Single Field named FieldName in the Specified Table
Using SQL
Dim Chan As Variant
Dim SQLStatement As String
Dim SQLOpenString As String
On Error GoTo Table_Exists:
'Establish a DSN-less connection to the Data Base
SQLOpenString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="
& fullmdbpath
Chan = SQLOpen(SQLOpenString)
