Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code

503

On Error GoTo 0

Set db = CurrentDb

Set tdfNew = db.CreateTableDef(“MyTempTable”)

With tdfNew

‘ Create fields and append them to the tdfNew:

.Fields.Append .CreateField(“FirstName”, dbText)

.Fields.Append .CreateField(“LastName”, dbText)

.Fields.Append .CreateField(“Phone”, dbText)

End With

‘ Append the new TableDef object to the current database:

db.TableDefs.Append tdfNew

db.Close

End Sub

Running this code in the Chapter13.accdb database creates a new table named

MyTempTable, a permanent addition to the database. Notice that the CreateNewTableDef

procedure deletes this table if it exists, before creating it as a new TableDef. Access won’t be able

to append the new TableDef object to its TableDefs collection if a table with the same name

already exists in the database.

Cross-Reference
The CreateNewTableDef procedure includes two statements that control how Access handles errors in this
code. Chapter 23 discusses the VBA error handling statements, and explains why you’d use On Error Resume
Next and On Error GoTo 0 in a procedure such as this.

TableDef objects are stored in the TableDefs collection. The following procedure displays the

names of all TableDef objects (including hidden and system tables) in the current database:

Public Sub DisplayAllTableDefs()

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Set db = CurrentDb

With db

Debug.Print .TableDefs.Count _

& “ TableDefs in “ & .Name

For Each tdf In .TableDefs

Debug.Print “ “ & tdf.Name

Next tdf

End With

End Sub

The DAO QueryDef object

A QueryDef object represents a saved query in an Access database. Using VBA code, you can

point a QueryDef object variable at an existing query (or, create a new query), and change the

query’s SQL statement, populate parameters used by the query, and execute the query. The query

could be a select query that returns a recordset, or an action query that modifies code in the tables

underlying the query.