Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

Manual Access Table

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.

DAO Table Created

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.

Table with Primary Key

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.

Linked Table View

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!

SQL Passthrough Query SQL Passthrough Query Design View

Creating tables, indexes and queries programmatically is easy by working with DAO.