MS Access Using DAO - Creating Tables and Indexes and Linked Tables and SQL Server Queries
MS Access Using DAO
This article describes how to programmatically create local tables and indexes in MS Access and create linked tables and queries in Access to a SQL Server database.
Creating Access Tables with DAO
Creating a table in Access is not difficult. Go to Create, Table Design and type in the field name and select the data types and you are done.
However, if the need to create the same table(s) many times over in the same or various databases exists, a programmatic solution is the way to go. DAO in VBA provides a means to do this.
This example creates a local Access table called tblCustomers and the required fields in the database using DAO objects. Note the additional attributes added to the fldID field as it is intended to be the Index for the table.
Public Sub daoCreateTables()
'create some tables and fields using DAO
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fldID As DAO.Field, fldName As DAO.Field, fldAddr1 As DAO.Field
Dim fldAddr2 As DAO.Field, fldAddr3 As DAO.Field
Set db = CurrentDb
On Error Resume Next
'create the table definition
Set tdf = db.CreateTableDef("tblCustomers")
'create the field definitions
Set fldID = tdf.CreateField("ID", dbLong)
fldID.Attributes = dbAutoIncrField
fldID.Required = True
Set fldName = tdf.CreateField("CustName", dbText)
fldName.AllowZeroLength = False
fldName.Required = True
Set fldAddr1 = tdf.CreateField("Addr1", dbText)
Set fldAddr2 = tdf.CreateField("Addr2", dbText)
Set fldAddr3 = tdf.CreateField("Addr3", dbText)
'add the fields to the table
tdf.Fields.Append fldID
tdf.Fields.Append fldName
tdf.Fields.Append fldAddr1
tdf.Fields.Append fldAddr2
tdf.Fields.Append fldAddr3
'add the table to the database
db.TableDefs.Append tdf
'refresh the tables and database
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set fldID = Nothing
Set fldName = Nothing
Set fldAddr1 = Nothing
Set fldAddr2 = Nothing
Set fldAddr3 = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "done"
End Sub
A quick check of the table in the database shows that the table has been created with the fields, however, the ID field is not indicated as the Primary Key of the table.
Creating Indexes with DAO
The following code sets the table definition and creates a Primary Key on tblCustomers
Public Sub daoCreateIndexes()
'create some indexes on the DAO tables
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fldID As DAO.Field
Dim idx As DAO.Index
Set db = CurrentDb
Set tdf = db.TableDefs("tblCustomers")
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Required = True
idx.Unique = True
Set fldID = idx.CreateField("ID", dbLong)
idx.Fields.Append fldID
tdf.Indexes.Append idx
tdf.Indexes.Refresh
Set fldID = Nothing
Set idx = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
After running the code, opening the table in Design View shows that the Primary Key attribute\index was successfully added to the ID field.
Creating Linked SQL Tables with DAO
To create linked tables programmatically with DAO, the DAO database, tabledef and a connection string are required. The SQL table will be Appended to the table def (which puts the SQL Table in the Access database).
'create connections to SQL tables with DAO
Public Sub daoCreateSQLTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
On Error GoTo errblock
' SQL Server connection string
strConnect = "ODBC;DRIVER=SQL Server;SERVER=YourSQLServer;DATABASE=AdventureWorks;Integrated SSPI=true"
'set the database and the local name of the SQL Table
Set db = CurrentDb
Set tdf = db.CreateTableDef("SalesPerson")
'set the SQL Source Table name
tdf.SourceTableName = "Sales.SalesPerson"
tdf.Connect = strConnect
db.TableDefs.Append tdf
'Refresh the database and the Access application to make the new table show up
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set tdf = Nothing
Set db = Nothing
Exit Sub
errblock:
MsgBox Err.Description
Exit Sub
End Sub
A quick look at the Linked table manager shows that the SQL Server table has been added to the Access database.
Creating SQL Server Passthrough Queries with DAO - To create a SQL Server query in Access programmatically with DAO, a DAO database object, DAO querydef object and a connection string are required.
Public Sub daoCreateSQLQueries()
Dim db As DAO.Database
Dim strConnect As String
Dim qdf As DAO.QueryDef
Dim hit As Byte, q As DAO.QueryDef
On Error GoTo errblock
' DSN-less connection string to server
strConnect = "ODBC;DRIVER=SQL Server;SERVER=YourSQLServer;DATABASE=AdventureWorks;Integrated SSPI=true"
'set the database and delete any existing query with the name SQL_Passthrough
Set db = CurrentDb
'test existing queries to see if it already exists
For Each q In db.QueryDefs
If q.Name = "SQL_Passthrough" Then
hit = 1
End If
Next
'if query exists, delete it
If hit = 1 Then
db.QueryDefs.Delete "SQL_PassThrough"
End If
'set the querydef properties and connection
Set qdf = db.CreateQueryDef("SQL_PassThrough")
qdf.SQL = "SELECT * FROM [Purchasing].[ProductVendor]"
qdf.ReturnsRecords = True
qdf.Connect = strConnect
qdf.Close
'refresh the database and application
db.QueryDefs.Refresh
Application.RefreshDatabaseWindow
Set qdf = Nothing
Set db = Nothing
Exit Sub
errblock:
MsgBox Err.Description
Exit Sub
End Sub
A quick verification that the query has been created and the syntax is correct and we are done!
Creating tables, indexes and queries programmatically is easy by working with DAO.