Databases Reference
In-Depth Information
IsSystemTable = True
Case "MSysACEs"
IsSystemTable = True
Case "MSysObjects"
IsSystemTable = True
Case "MSysQueries"
IsSystemTable = True
Case "MSysRelationships"
IsSystemTable = True
End Select
End Function
Now, the more difficult task of actually creating an Access table from within Excel is covered.
Two functions have been created named
, which
utilize SQL and DAO, respectively, to create new tables in Access. Both functions return a
Boolean value indicative of whether the function was successful in creating the table. The
and
CreateTableSQL
CreateTableDAO
function is presented first.
CreateTableDAO
Function CreateTableDAO(fullmdbpath As String, TableName As
String, Optional ColumnName As String = "ID") As Boolean
'Creates a Single Table named TableName in the Specified
Database with one Column named ColumnName
'DAO Cannot Create a Table without any fields in MS Access!
Dim MCSdb As Database
Dim tdef As TableDef
Dim ws As Workspace
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
Set tdef = MCSdb.CreateTableDef(TableName)
With tdef
.Fields.Append .CreateField(ColumnName, dbText)
End With
MCSdb.TableDefs.Append tdef
MCSdb.TableDefs.Refresh
ws.CommitTrans
' Refresh the cache to ensure that the latest data is available.
