Databases Reference
In-Depth Information
Dim ConnectString As String
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.Append
tbl.CreateField
(ColumnName, dbText)
'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
CreateFieldDAO = True
Exit Function
Table_Exists:
Debug.Print Error$, Err
MsgBox "Error: " & Err & ": " & Error$, vbOKOnly + vbCritical,
"Error Accessing: " & fullmdbpath
CreateFieldDAO = False
End Function
The function returns a Boolean value of True if the field was successfully generated in the
table. In the case shown in Figure 8.4, the field EMPLOYEEID was created in the table named
Employees. A similar function was constructed to allow fields to be created using SQL.
Function CreateFieldSQL(fullmdbpath As String, TableName As
String, _
FieldName As String) As Boolean
'Creates a Single Field named FieldName in the Specified Table
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
