Access.2007.VBA.Bibl..

Argument names are inconsistently capitalized in VBA code. Regardless of how you type

them in, when your cursor leaves the line of code, some argument names are capitalized

(such as “Name”) and some are not (such as “sqltext”). The capitalization does not match the capital-

ization of the arguments in their Help topics, where they are usually represented as all lowercase.

You can also create a QueryDef corresponding to an action query and run it directly from code, to

create a table for use elsewhere in the code instead of a parameter query, to avoid errors that will

occur if a query criterion is looking for a value on a form that is closed when the code runs:

strFilter = “[InvoiceDate] = #” & dteDue & “#”

strSQL = “SELECT [InvoiceNo], InvoiceDate, _

Customer, Employee “ _

& “INTO tmakMatchingRecords “ _

& “FROM tblInvoices “ _

& “ WHERE “ & strFilter & “;”

Debug.Print “SQL string: “ & strSQL

Set qdf = dbs.CreateQueryDef(Name:=strQuery, _

sqltext:=strSQL)

qdf.Execute

You can create a QueryDef without a name, using just double quotes, as in this line

of code:

Set qdfTemp = dbs.CreateQueryDef(Name:=””, _

sqltext:=strSQL)

However, I generally prefer to create a named query, so I can examine it in the interface for debug-

ging purposes, if necessary.

TableDefs and Fields ......................................................................................

TableDefs correspond to tables in the interface. Although it is much more common to need to cre-

ate a query programmatically, sometimes you may need to create a table in code. When you create

a new table, you also need to create fields for it. The following code creates a new table in an exter-

nal database, with several fields of different data types. Each field is created (and its default value

set, for two of them), and is then appended to the new table. An error handler returns the user to

the input box where the new table name is entered, in case a table of that name already exists in

the database. Finally, all the TableDefs in the database are listed to the Immediate window, with the

new table as the last entry in the list:

Private Sub NewTable()

On Error Resume Next

Dim dbsNorthwind As DAO.Database

Dim tdfNew As DAO.TableDef

Dim fld As DAO.Field

TIPTIP

NOTENOTE

Part II Writing VBA Code to Exchange Data between Office Components