Access VBA Macro Programming

This code initially creates aTableDefobject using theDimstatement. It then uses the

CreateTableDefmethod to create a new table called NewTable.

TheCreateFieldmethod is then used to create new fields for the table, in this case creating

a field called LastName with a text type and size of 100 characters. This is appended to the

new table definition.

Finally, the new table definition is appended to theTableDefscollection in the current

database.

Execute

Executeis a very useful method for executing a SQL statement. It must be a statement that

does not return records such as a delete, update, create table, or insert query.

CurrentDb.Execute ("delete * from newTable")

No warning messages are shown when theExecutestatement is run, so there is no need to

turn them off beforehand and reset them afterwards.

Name

TheNameproperty will provide the path and filename of the current database.

MsgBox CurrentDb.Name

If you only need the path without the filename, use thePathproperty of the current

project.

OpenRecordset

TheOpenRecordsetmethod creates aRecordsetobject and is used as a precursor to

manipulating the recordset in terms of deleting, adding, and editing records.

Dim RecSet As Recordset

Set RecSet = CurrentDb.OpenRecordset("MyTable")

This code creates aRecordsetobject based on the table MyTable. The parameter MyTable

can also be a query name or a SQL string that returns records.

Additional optional parameters can also be used:

 Type ARecordsetTypeEnumconstant that indicates the type of recordset to open

such as dynaset or snapshot. A dynaset is read-write whereas a snapshot is read-only.

 Options ARecordsetOptionEnumconstant that specifies characteristics of the new

recordset, such as Append Only or Read Only.

 LockEdit ALockTypeEnumconstant that determines locking for the recordset such

as Optimistic or Pessimistic.

TheRecordsetobject is discussed in more detail in the next section of this chapter.

206 Microsoft Access 2010 VBA Macro Programming