Copyright: Build 6.99.00901: 01-Sep-95 at 1:01a


OpenDatabase Method (Microsoft Access)
OpenDatabase Method

In Microsoft Access, use the CurrentDb function to point a Database variable to the current database, rather than using the OpenDatabase method. For example, you can use the following code to get a variable that represents the current database.

Dim dbsCurrent as Database

set dbsCurrent = CurrentDb

Use the OpenDatabase method to open a database other than the current database.


Update Method (Microsoft Access)

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.


Container Object, Containers Collection (Microsoft Access)

In addition to the Container objects defined by the Microsoft Jet database engine, the following four Container objects are defined by Microsoft Access.

Container name	Contains information about
Forms	Saved forms
Modules	Saved modules
Reports	Saved reports
Scripts	Saved macros
The Container objects defined by Microsoft Access are Microsoft Access database objects, not data access objects. These Container objects provide the Jet database engine with information about Microsoft Access objects. The Jet database engine uses this information to implement security on Microsoft Access database objects in the same way it does for data access objects.

The Container objects defined by Microsoft Access and the Container objects defined by the Jet database engine are all included in the Containers collection in Microsoft Access.

The Documents collection of a Container object contains Document objects representing the individual objects of the type described by the Container object. For example, the Forms Container object has a Documents collection that might include a Document object corresponding to a form named Orders.

You can use Container objects to establish and enforce permissions for Microsoft Access database objects. To set permissions for a Container object, set the UserName property of the Container object to the name of an existing User or Group object. Then set the Permissions property for the Container object.

Note   Don't confuse the particular types of Container objects with the collection types of the same name. The Forms and Reports Container objects each contain Documents collections, which include individual Document objects representing each saved form or report. The Forms and Reports collections refer only to open forms or reports.


Database Object, Databases Collection (Microsoft Access)

When working with data access objects from Microsoft Access, you will often need a Database object variable that represents the current database. Use the CurrentDb function to return a Database object for the database that is currently open. This Database object is automatically appended to the Databases collection.

For example, suppose you are currently working with the Northwind sample database in Microsoft Access. You can create a Database object that refers to that database by first declaring a Database object variable, then pointing it to the Database object returned by the CurrentDb function.

Dim dbs As Database

Set dbs = CurrentDb

You don't need to know the name of the database or its position in the Databases collection in order to use the current database. If you do want to know the name of the current database, check the Name property of the Database object, which contains the path and filename of the database. To find its position in the Databases collection, enumerate through the collection.

You can open only one database at a time in the Microsoft Access window. From Visual Basic code, however, you can create multiple independent Database object variables to represent multiple open databases. In this way, you can manipulate more than one database at a time from code. You can also create multiple Database object variables and point them to the current database.

Note   In your Visual Basic code, use the CurrentDb function to return a Database object that refers to the current database, rather than the DBEngine(0)(0) syntax. The CurrentDb function creates another instance of the current database, while the DBEngine(0)(0) syntax refers to the open copy of the current database. Using the CurrentDb function enables you to create more than one variable of type Database that refers to the current database. Microsoft Access still supports the DBEngine(0)(0) syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser environment.


DBEngine Object (Microsoft Access)

Microsoft Access provides a means of manipulating data access objects from other applications through OLE Automation. If you are controlling Microsoft Access from another application, such as Visual Basic or Microsoft Excel, you can use the DBEngine property of the Microsoft Access Application object to return a reference to the DBEngine object. All data access objects and collections are then accessible through the DBEngine object.


Document Object, Documents Collection (Microsoft Access)

In addition to the Document objects defined by the Microsoft Jet database engine, the following Document objects are defined by Microsoft Access.

Document	Container	Contains information about
Form	Forms	Saved form
Macro	Scripts	Saved macro
Module	Modules	Saved module
Report	Reports	Saved report
SummaryInfo	Databases	Database document summary
UserDefined	Databases	User-defined properties
The Document objects defined by Microsoft Access are Microsoft Access database objects, not data access objects. These Document objects provide the Jet database engine with information about Microsoft Access objects. The Jet database engine uses this information to implement security on Microsoft Access database objects in the same way it does for data access objects.

The Documents collection of a Container object contains Document objects representing the individual objects of the type described by the Container object. For example, the Forms Container object has a Documents collection that might include a Document object corresponding to a form named Orders.

Microsoft Access defines two Document objects in the Databases Container object [image] SummaryInfo and UserDefined.

The SummaryInfo Document object provides programmatic access to document summary properties [image] including Title, Subject, Author, Keywords, Comments, Manager, Company, and Category. You can also set these properties on the Summary tab of the Database Properties dialog box, available by clicking Database Properties on the File menu.

To set these properties in Visual Basic, you must create them and append them to the Properties collection of the SummaryInfo Document object if they have not already been set in the Database Properties dialog box. Once a property has been created, you must explicitly refer to the Properties collection to set it. In the following example, doc is an object variable pointing to the SummaryInfo Document object.

doc.Properties!Title = "Northwind Traders"

For more information on creating and setting Microsoft Access-defined properties, search the online index for "Property object" and "CreateProperty method", or ask the Answer Wizard.

The UserDefined Document object provides programmatic access to user-defined properties defined on the Custom tab of the Database Properties dialog box. You can also create these properties in Visual Basic and append them to the Properties collection of the UserDefined Document object.

Note   Don't confuse properties defined in the Properties collection of a Database document with properties defined in the Properties collection of a Database object. You can create user-defined properties in either Properties collection, but only those defined on the SummaryInfo or UserDefined Document objects can be set from the Database Properties dialog box.

You can use Document objects to establish and enforce permissions for individual Microsoft Access database objects. To set permissions for a Document object, set the UserName property of the Document object to the name of an existing User or Group object. Then set the Permissions property for the Document object.

Note   Don't confuse the particular types of Container objects with the collection types of the same name. The Forms and Reports Container objects each contain Documents collections, which include individual Document objects representing each saved form or report. The Forms and Reports collections refer only to open forms or reports.


Property Object, Properties Collection (Microsoft Access)

Several types of properties exist in Microsoft Access. Each of these properties can be represented in Visual Basic code by a Property object variable, and each is a member of a Properties collection.

Properties that apply to data access objects

Built-in properties are defined by the Microsoft Jet database engine for each data access object.

User-defined properties can be added to some data access objects. These data access objects include Database, Index, QueryDef, and TableDef objects, and Field objects in the Fields collection of a QueryDef or TableDef object.

Some properties defined by Microsoft Access apply to data access objects. These properties can generally be set either in the Microsoft Access window or from Visual Basic. The Jet database engine cannot recognize these properties until corresponding Property objects are specifically created and appended to the Properties collection. The data access objects to which such properties may apply are QueryDef and TableDef objects, and Field objects in the Fields collection of a QueryDef or TableDef object. For lists of these Microsoft Access-defined properties, see the Microsoft Access Specifics topic for the TableDef, QueryDef, and Field objects.

Microsoft Access-defined properties that apply to data access objects differ from properties defined by the Jet database engine in several ways.

To refer to a user-defined property or a property defined by Microsoft Access, you must explicitly refer to the Properties collection. The fastest way to refer to a Microsoft Access property is by the following syntax.

object.Properties!name

You can also use the following, slightly slower syntax.

object.Properties("name")

In contrast, to refer to properties defined by the Jet database engine, you can simply use the object.name syntax.

If you are setting the value of a Microsoft Access-defined property for the first time, you first need to create it using the CreateProperty method. For example, the Caption property of a Field object is a Microsoft Access-defined property. If you have not previously set the Caption property in table Design view, but are setting the property for the first time from Visual Basic code, you must first create that property using the CreateProperty method and append it to the Properties collection before you can set its value.

A Microsoft Access-defined property is automatically appended to the Properties collection the first time it is set in the Microsoft Access window, so if you have already set a property in the user interface, you don't need to create and append the property in code. For example, you can set the Caption property for a table in Datasheet view by clicking Font on the Format menu. This property is then included in the Properties collection of a TableDef object that points to the table.

Until you have set a Microsoft Access-defined property either in table Design view or from Visual Basic code, that property will not appear in the Properties collection.  When you set these properties from Visual Basic, you should include error-handling code that checks to see if the property exists in the Properties collection, and creates and appends the property if it does not.

Properties that apply to Microsoft Access objects

Like data access objects, every Microsoft Access object contains a Properties collection, which has built-in Property objects. For example, Property objects that apply to a form are members of the Properties collection of the Form object.

You can also create user-defined properties for Microsoft Access objects. For example, you might create a property called TextType that applies to a text box control.

Property objects in the Properties collections of Form, Report, and Control objects differ from data access Property objects in that they do not have an Inherited property.

You can enumerate the Property objects in the Properties collections of Form, Report, and Control objects. However, the Microsoft Access Application object and the Screen object have Properties collections that can't be enumerated. Additionally, the properties of these objects are read-only.


QueryDef Object, QueryDefs Collection (Microsoft Access)

In addition to the properties defined by the Microsoft Jet database engine, a QueryDef object may also contain these Microsoft Access application-defined properties. For details on checking and setting these properties, search the online index for the individual properties and the Property object.

DatasheetFontItalic	FrozenColumns
DatasheetFontHeight	LogMessages
DatasheetFontName	RecordLocks
DatasheetFontUnderline	RowHeight
DatasheetFontWeight	ShowGrid
Description


Container Object, Containers Collection Example (Microsoft Access)

The following example grants programmers full access to all modules in a database, and grants all other users read-only permissions on modules.

Sub SetModulePermissions()

Dim dbs As Database, wrk As Workspace, ctr As Container

Dim grp As Group

Set wrk = DBEngine.Workspaces(0)

' Return Database object pointing to current database.

Set dbs = CurrentDb

Set ctr = dbs.Containers!Modules

wrk.Groups.Refresh

For Each grp In wrk.Groups

ctr.UserName = grp.Name

If ctr.UserName = "Programmers" Then

ctr.Permissions = ctr.Permissions Or dbSecFullAccess

Else

ctr.Permissions = ctr.Permissions Or acSecModReadDef

End If

Next grp

End Sub


Database Object, Databases Collection Example (Microsoft Access)

The following example show three ways to return a Database object in Microsoft Access. The procedure returns a Database object representing the current database, which is open in the Microsoft Access window. Next, the procedure creates another database called Newdb.mdb and saves it to disk. Then it opens an existing database called Another.mdb. Finally, it enumerates all Database objects in the Databases collection.

Sub ReferenceDatabases()

Dim wsp As Workspace

Dim dbsCurrent As Database, dbsNew As Database

Dim dbsAnother As Database, dbs As Database

' Return Database object pointing to current database.

Set dbsCurrent = CurrentDb

' Return Workspace object pointing to current workspace.

Set wsp = DBEngine.Workspaces(0)

' Create new Database object.

Set dbsNew = wsp.CreateDatabase("Newdb.mdb", dbLangGeneral)

' Open database other than current database.

Set dbsAnother = wsp.OpenDatabase("Another.mdb")

' Enumerate all open databases.

For Each dbs in wsp.Databases

Debug.Print dbs.Name

Next dbs

End Sub


Field Object, Fields Collection Example (Microsoft Access)

The following example creates a new Field object, sets some of its properties, and appends it to the Fields collection of a TableDef object. The procedure then enumerates all fields in the Fields collection of the TableDef object.

Sub NewField()

Dim dbs As Database, tdf As TableDef

Dim fld As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Create new Field object.

Set fld = tdf.CreateField("SSN#")

' Set Type and Size properties of Field object.

fld.Type = dbText

fld.Size = 11

' Append field.

tdf.Fields.Append fld

' Enumerate all fields in Fields collection of TableDef object.

For Each fld in tdf.Fields

Debug.Print fld.Name

Next fld

End Sub


Group Object, Groups Collection Example (Microsoft Access)

The following example creates a new User object and appends it to the Users collection of a Workspace object. It then creates a new Group object and appends it to the Groups collection of the Workspace object. The new Group object is also appended to the Groups collection of the User object. The new group is then given modify and delete permissions for modules.

Note that in order to assign users to groups, you must either append a User object to the Users collection of a Group object, or append a Group object to the Groups collection of a User object. It doesn't matter which option you choose; either will result in the specified user being included in the specified group.

Sub NewModulesGroup()

Dim wsp As Workspace, dbs As Database

Dim usr As User, grp As Group, grpMember As Group

Dim ctr As Container, doc As Document

' Get default Workspace object.

Set wsp = DBEngine.Workspaces(0)

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Create User object and append to Users collection of Workspace object.

Set usr = wsp.CreateUser("Pat Smith", "123abc789xyz", "Password")

wsp.Users.Append usr

' Create Group object and append to Groups collection of Workspace object.

Set grp = wsp.CreateGroup("Programmers", "321xyz987abc")

wsp.Groups.Append grp

' Append Group object to Groups collection of User object.

Set grpMember = usr.CreateGroup("Programmers")

usr.Groups.Append grpMember

' Refresh Groups collection of User object.

usr.Groups.Refresh

' Return Container object.

Set ctr = dbs.Containers!Modules

' Set UserName property of Container object.

ctr.UserName = grpMember.Name

'Add modify and delete permissions for new group on all modules.

ctr.Permissions = ctr.Permissions Or acSecModWriteDef

End Sub


DBEngine Object Example (Microsoft Access)

The following example prints all the properties of the DBEngine object.

Sub EngineProperties()

Dim prp As Property

For Each prp In DBEngine.Properties

Debug.Print prp.Name

Next prp

End Sub


Document Object, Documents Collection Example (Microsoft Access)

The following example prints the name of each Form Document object in the current database and the date it was last modified.

Sub DocumentModified()

Dim dbs As database, ctr As container, doc As Document

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Return Container variable pointing to Forms container.

Set ctr = dbs.Containers!Forms

' Enumerate through Documents collection of Forms container.

For Each doc In ctr.Documents

' Print Document object name and value of LastUpdated property.

Debug.Print doc.Name; "    "; doc.LastUpdated

Next doc

End Sub


Dynaset-Type Recordset Object Example (Microsoft Access)

The following example creates a dynaset-type Recordset object, then checks the Updatable property of the Recordset object.

Sub RecentHires()

Dim dbs As Database, rst As Recordset

Dim strSQL As String

' Return Database object pointing to current database.

Set dbs = CurrentDb

strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-95#;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Debug.Print rst.Updatable

End Sub


Error Object, Errors Collection Example (Microsoft Access)

The following example generates an error by attempting to open a Recordset object on a non-existent Students table. Information about the error is stored in both the data access Error object and the Visual Basic Err object. The procedure prints the value of the Description, Source, and Number properties of the Error object. Then it prints the values of the corresponding properties of the Err object.

Note that the first Error object in the Errors collection, Errors(0), should always refer to the same error as the Err object. If it doesn't, information in the Errors collection may be outdated. Use the Refresh method to ensure that the Errors collection includes the most recent error information.

Sub CheckError()

Dim dbs As Database, tdf As TableDef, rst As Recordset

Dim prp As Property

' Declare Error variable for enumeration of Errors collection.

Dim errX As Error

' Ignore errors.

On Error Resume Next

' Clear error in Err object.

Err.Clear

' Refresh Errors collection.

Errors.Refresh

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Attempt to open Recordset object on nonexistent table.

Set rst = dbs.OpenRecordset("Students")

Debug.Print "DAO Error Object:"

' Print number of errors in Errors collection.

Debug.Print ">>>Number of errors: "; Errors.Count

' Enumerate Errors collection and key properties.

For Each errX In DBEngine.Errors

Debug.Print errX.Description

Debug.Print errX.Source

Debug.Print errX.Number

Next errX

Debug.Print

Debug.Print "VBA Err Object:"

' Display corresponding properties of Err object.

Debug.Print Err.Description

Debug.Print Err.Source

Debug.Print Err.Number

End Sub


Index Object, Indexes Collection Example (Microsoft Access)

The following example creates a new index on an Employees table.

Sub NewIndex()

Dim dbs As Database, tdf As TableDef, idx As Index

Dim fld As Field

' Return Database object pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

Set idx = tdf.CreateIndex("LastNameIndex")

Set fld = idx.CreateField("LastName")

idx.Fields.Append fld

tdf.Indexes.Append idx

End Sub


Parameter Object, Parameters Collection Example (Microsoft Access)

The following example creates a new parameter query and supplies values for the parameters.

Sub NewParameterQuery()

Dim dbs As Database, qdf As QueryDef, rst As Recordset

Dim prm As Parameter, strSQL As String

' Return Database object pointing to current database.

Set dbs = CurrentDb

' Construct SQL string.

strSQL = "PARAMETERS [Beginning OrderDate] DateTime, " & _

"[Ending OrderDate] DateTime; SELECT * FROM Orders " & _

"WHERE ([OrderDate] Between[Beginning OrderDate] " & _

"And [Ending OrderDate]);"

' Create new QueryDef object.

Set qdf = dbs.CreateQueryDef("ParameterQuery", strSQL)

' Supply values for parameters.

qdf.Parameters![Beginning OrderDate] = #4-1-95#

qdf.Parameters![Ending OrderDate] = #4-30-95#

End Sub


Property Object, Properties Collection Example (Microsoft Access)

The following example creates a new user-defined property, sets its initial value, and appends it to the Properties collection of a TableDef object.

Sub CreateNewProperty()

Dim dbs As Database, tdf As TableDef

Dim prp As Property

' Return Database object pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Orders

' Create new property, denote type, and set initial value.

Set prp = tdf.CreateProperty("LastSaved", dbText, "New")

' Append to Properties collection of TableDef object.

tdf.Properties.Append prp

End Sub

The next example creates a property that is defined by Microsoft Access, but applies to data access objects. Because the Microsoft Jet database engine cannot recognize properties defined by Microsoft Access, you must create a new Property object and append it to the Properties collection if you are setting the property for the first time.

Note that you must specify the correct constant for the type argument when you create the property. If you're not certain which data type you should use, search the online index for the individual property.

Sub CreateAccessProperty()

Dim dbs As Database, tdf As TableDef

Dim prp As Property

' Return Database object pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Orders

' Create property, denote type, and set initial value.

Set prp = tdf.CreateProperty("DatasheetFontItalic", dbBoolean, True)

' Append Property object to Properties collection.

tdf.Properties.Append prp

End Sub


QueryDef Object, QueryDefs Collection Example (Microsoft Access)

The following example checks to see if there is a query called RecentHires in the current database, and deletes it from the QueryDefs collection if it exists. Then the procedure creates a new QueryDef object and opens it in Datasheet view.

Sub NewQuery()

Dim dbs As Database, qdf As QueryDef

Dim strSQL As String

' Return Database object pointing to current database.

Set dbs = CurrentDb

' Refresh QueryDefs collection.

dbs.QueryDefs.Refresh

' If RecentHires query exists, delete it.

For Each qdf in dbs.QueryDefs

If qdf.Name = "RecentHires" Then

dbs.QueryDefs.Delete qdf.Name

End If

Next qdf

' Create SQL string to select employees hired after 1-1-95.

strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-95#;"

' Create new QueryDef object.

Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)

' Open query in Datasheet view.

DoCmd.OpenQuery qdf.Name

End Sub


Recordset Object, Recordsets Collection Example (Microsoft Access)

The following example opens a table-type Recordset object, a dynaset-type Recordset object, and a snapshot-type Recordset object. Then it displays the value of the Updatable property for the Recordset objects.

Sub NewRecordsets()

Dim dbs As database, rst As Recordset

Dim rstEmployees As Recordset, rstOrders As Recordset

Dim rstProducts As Recordset, strSQL As String

' Return Database object pointing to current database.

Set dbs = CurrentDb

' Create table-type Recordset object.

Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenTable)

' Construct SQL string.

strSQL = "SELECT * FROM Orders WHERE OrderDate >= #1-1-95#;"

' Create dynaset-type Recordset object.

Set rstOrders = dbs.OpenRecordset(strSQL, dbOpenDynaset)

' Create snapshot-type Recordset object.

Set rstProducts = dbs.OpenRecordset("Products", dbOpenSnapshot)

' Print value of Updatable property for each Recordset object.

For Each rst In dbs.Recordsets

Debug.Print rst.Name; "   "; rst.Updatable

Next rst

End Sub


Relation Object, Relations Collection Example (Microsoft Access)

The following example creates a new Relation object representing the relationship between an Employees table and an Orders table.

To test the following example in Microsoft Access, open the Northwind sample database and choose Relationships from the Tools menu. Delete the relationship between the Employees table and the Orders table, and close the Relationships window. Then, run the following function from a standard module, and view the Relationships window again to see the new relationship.

Sub NewRelation()

Dim dbs As Database

Dim fld As Field, rel As Relation

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create new relationship and set its properties.

Set rel = dbs.CreateRelation("EmployeesRelation", "Employees", "Orders")

' Set Relation object attributes to enforce referential integrity.

rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade

' Create field in Fields collection of Relation.

Set fld = rel.CreateField("EmployeeID")

' Provide name of foreign key field.

fld.ForeignName = "EmployeeID"

' Append field to Relation and Relation to database.

rel.Fields.Append fld

dbs.Relations.Append rel

End Sub


Snapshot-Type Recordset Object Example (Microsoft Access)

The following example creates a snapshot-type Recordset object from an SQL statement, then prints the value of the Updatable property for the Recordset object. Since snapshot-type Recordset objects are never updatable, the value of this property will always be False (0).

Sub LongTermEmployees()

Dim dbs As Database, qdf As QueryDef, rst As Recordset

Dim strSQL As String

' Return Database object pointing to current database.

Set dbs = CurrentDb

strSQL = "SELECT * FROM Employees WHERE HireDate <= #1-1-95#;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Debug.Print rst.Updatable

End Sub


Table-Type Recordset Object Example (Microsoft Access)

The following example opens a table-type Recordset object, then prints the number of records in the Recordset object.

Sub CountEmployees()

Dim dbs As Database, tdf As TableDef, rst As Recordset

' Return Database object pointing to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Employees", dbOpenTable)

rst.MoveLast

Debug.Print rst.RecordCount

End Sub


TableDef Object, TableDefs Collection Example (Microsoft Access)

The following example creates a new TableDef object, defines a field within it, and appends it to the TableDefs collection of the current database.

Sub NewTable()

Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database object pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.CreateTableDef("Contacts")

Set fld = tdf.CreateField("ContactName", dbText, 30)

tdf.Fields.Append fld

dbs.TableDefs.Append tdf

End Sub


User Object, Users Collection Example (Microsoft Access)

The following example creates a new User object and appends it to the Users collection of a Workspace object. It then creates a new Group object and appends it to the Groups collection of the Workspace object. The new User object is also appended to the Users collection of the Group object. The new user is then given retrieval permissions on tables.

Note that in order to assign users to groups, you must either append a User object to the Users collection of a Group object, or append a Group object to the Groups collection of a User object. It doesn't matter which option you choose; either will result in the specified user being included in the specified group.

Sub NewTablesUser()

Dim wsp As Workspace, dbs As Database

Dim usr As User, grp As Group, usrMember As User

Dim ctr As Container, doc As Document

Set wsp = DBEngine.Workspaces(0)

' Return Database object pointing to current database.

Set dbs = CurrentDb

' Create User object and append to Users collection of Workspace object.

Set usr = wsp.CreateUser("Chris Jones", "123abc456DEF", "Password")

wsp.Users.Append usr

' Create Group object and append to Groups collection of Workspace object.

Set grp = wsp.CreateGroup("Marketing", "321xyz654EFD")

wsp.Groups.Append grp

' Append new User object to Users collection of new Group object.

Set usrMember = grp.CreateUser("Chris Jones")

grp.Users.Append usrMember

' Refresh Users collection of Group object.

grp.Users.Refresh

' Return Container object.

Set ctr = dbs.Containers!Tables

' Set UserName property of Container object.

ctr.UserName = usrMember.Name

' Add retrieve permissions for new user on all tables.

ctr.Permissions = ctr.Permissions Or dbSecRetrieveData

End Sub


AbsolutePosition Property Example (Microsoft Access)

The following example uses the AbsolutePosition property to set the ordinal position of the first record in a dynaset-type Recordset object to 1.

Sub FirstRecord()

Dim dbs As Database, rst As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT * FROM Orders WHERE " & _

"[ShippedDate] >= #1-1-95#;")

rst.MoveFirst

rst.AbsolutePosition = 1

End Sub


AllowZeroLength Property Example (Microsoft Access)

The following example creates a new Field object and sets its AllowZeroLength property to True.

Sub ZeroLengthField()

Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Create new field in Employees table.

Set fld = tdf.CreateField("SpouseName", dbText, 15)

' Allow zero-length strings in field.

fld.AllowZeroLength = True

' Append Field object.

tdf.fields.Append fld

End Sub


AllPermissions Property Example (Microsoft Access)

The following example checks the AllPermissions property for the Forms Container object and determines whether the user specified by the UserName property has full access to forms.

Sub CheckAllPermissions()

Dim dbs As Database, ctr As Container

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Return Container object that points to Forms container.

Set ctr = dbs.Containers!Forms

' Print current value of UserName for container.

Debug.Print ctr.UserName

' Check if AllPermissions property includes full access.

If (ctr.AllPermissions And dbSecFullAccess) Then

MsgBox "User " & ctr.UserName & " has full access to all forms."

End If

End Sub


Attributes Property Example (Microsoft Access)

The following example checks the Attributes property for each table in the current database, and prints the names of system and hidden tables provided by the Microsoft Jet database engine.

Note that the And operator performs a bitwise comparison to determine whether an attribute is currently set.

Sub CheckAttributes()

Dim dbs As Database, tdf As TableDef

' Return Database variable that points to current database.

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs

' Compare property setting and constant in question.

If (tdf.Attributes And dbSystemObject) Or _

(tdf.Attributes And dbHiddenObject) Then

Debug.Print tdf.Name

End If

Next tdf

End Sub


BOF, EOF Properties Example (Microsoft Access)

The following example uses the EOF property of a Recordset object to move the current record pointer past the last record in the Recordset object, then uses the BOF property to move the current record pointer to before the first record in the Recordset object.

Sub EndsOfFile()

Dim dbs As Database, rst As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Orders")

' Do until end of file.

Do Until rst.EOF

' Move to next record.

rst.MoveNext

.

.

.

Loop

' Move to last record to set a current record.

rst.MoveLast

' Do until beginning of file.

Do Until rst.BOF

rst.MovePrevious

.

.

.

Loop

rst.Close

dbs.Close

End Sub


Bookmark, Bookmarkable Properties Example (Microsoft Access)

The following example moves through the records of the Employees table from the beginning of the file to the end and stores the value of the Bookmark property for each record in an array.

' Include this statement in the Declarations section of the module.

Option Compare Binary

Sub RecordPositions()

Dim dbs As Database, rst As Recordset, fld As Field

Dim intI As Integer

' Declare array to hold bookmarks.

Dim varRecord() As Variant

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Open a table-type Recordset object.

Set rst = dbs.OpenRecordset("Employees")

Set fld = rst.Fields!LastName

' Populate the Recordset object.

rst.MoveLast

rst.MoveFirst

' Redimension array with value of RecordCount property as upper bound.

ReDim varRecord(0 To rst.RecordCount - 1)

intI = 0

' Check Bookmarkable property of Recordset object.

If rst.Bookmarkable Then

Do Until rst.EOF

' Populate array with bookmarks.

varRecord(intI) = rst.Bookmark

' Increment counter.

intI = intI + 1

rst.MoveNext

Loop

End If

End Sub


CacheSize, CacheStart Properties, FillCache Method Example (Microsoft Access)

The following example opens a Recordset object from an attached Orders table that is stored on an ODBC server. The procedure next sets the CacheSize and CacheStart properties, and then uses the FillCache method to cache data from the server to the database.

Sub CacheData()

Dim rst As Recordset, dbs As Database

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Open local dynaset-type Recordset object.

Set rst = dbs.OpenRecordset("Orders", DbOpenDynaset)

' Locate record with OrderID of 11000.

rst.FindFirst "OrderID = 11000"

' Start caching records beginning with OrderID 11000.

rst.CacheStart = rst.Bookmark

rst.CacheSize = 12	' Set cache size to 12 records.

rst.FillCache	' Fill cache.

' Display rows.

.

.

.

End Sub


CollatingOrder Property Example (Microsoft Access)

The following example checks the CollatingOrder property for the current database.

Sub SetSortOrder()

Dim dbs As Database

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Check CollatingOrder property for database.

Debug.Print dbs.CollatingOrder

End Sub


Connect, SourceTableName Properties Example (Microsoft Access)

The following example creates a TableDef object in the specified database. The procedure then sets its Connect, Name, and SourceTableName properties and appends the object to the TableDefs collection.

Sub ConnectSource()

Dim dbs As Database, tdf As TableDef

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.CreateTableDef("PDXAuthor")

' Attach Paradox table Author in database C:\PDX\Publish.

tdf.Connect = "Paradox 4.X;DATABASE=C:\PDX\Publish"

tdf.SourceTableName = "Author"

dbs.TableDefs.Append tdf

End Sub


Container, Permissions Properties Example (Microsoft Access)

The following example determines the name of the Container object to which a Document object belongs, and sets permissions accordingly for the specified group account.  Remember that you must denote a particular user or group account before you set permissions for an object.  The following example assumes that you have defined a Marketers group.

Note that Microsoft Access defines additional Container objects beyond those defined by the Microsoft Jet database engine. These include the Form, Report, Script, and Module Container objects.

Sub DocAnalyzer(docUnknown As Document)

Dim strContainerName As String

' Set UserName property to valid existing group account.

docUnknown.UserName = "Marketers"

' Get value of Container property.

strContainerName = docUnknown.Container

Select Case strContainerName

Case "Forms"

' Set permissions for Form Document object.

docUnknown.Permissions = docUnknown.Permissions Or _

acSecFrmRptWriteDef

Case "Reports"

' Set permissions for Report Document.

docUnknown.Permissions = docUnknown.Permissions Or _

acSecFrmRptExecute

Case "Scripts"

' Set permissions for Script Document.

docUnknown.Permissions = docUnknown.Permissions Or _

acSecMacWriteDef

Case "Modules"

' Set permissions for Module Document object.

docUnknown.Permissions = docUnknown.Permissions Or _

acSecModReadDef

Case Else

Exit Sub

End Select

End Sub


Count Property Example (Microsoft Access)

The following example prints the names and total number fields in an Orders table.

Sub CountFields()

Dim dbs As Database, tdf as TableDef

Dim intI As Integer

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Orders

' Count fields in Fields collection of TableDef object.

Debug.Print tdf.Fields.Count

' List names of all fields.

For Each fld In tdf.Fields

Debug.Print fld.Name

Next fld

End Sub


DataUpdatable Property Example (Microsoft Access)

The following example creates two Recordset objects, a dynaset-type Recordset object and a snapshot-type Recordset object, from the same table. The procedure then checks the DataUpdatable property for the LastName field in each Recordset object. The value of the DataUpdatable property is True (-1) for the dynaset-type Recordset object, and False (0) for the snapshot-type Recordset object.

Sub CheckUpdatable()

Dim dbs As Database

Dim rstDynaset As Recordset, rstSnapshot As Recordset

Dim fldDynaset As Field, fldSnapshot As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Open dynaset-type Recordset object.

Set rstDynaset = dbs.OpenRecordset("Employees", dbOpenDynaset)

' Open snapshot-type Recordset object.

Set rstSnapshot = dbs.OpenRecordset("Employees", dbOpenSnapshot)

' Get Field object variables pointing to field in each Recordset object.

Set fldDynaset = rstDynaset.Fields!LastName

Set fldSnapshot = rstSnapshot.Fields!LastName

' Get current record.

rstDynaset.MoveFirst

rstSnapshot.MoveFirst

' Display value of DataUpdatable property for each Recordset object.

Debug.Print "DataUpdatable (Dynaset-type Recordset): "; _

fldDynaset.DataUpdatable

Debug.Print "DataUpdatable (Snapshot-type Recordset): "; _

fldSnapshot.DataUpdatable

End Sub


DateCreated, LastUpdated Properties Example (Microsoft Access)

The following example enumerates through all QueryDef objects in the database and lists those which have been created or changed within the last week.

Sub TimeUpdated()

Dim dbs As Database, tdf As TableDef, qdf As QueryDef

Dim strList As String, intI As Integer

Dim dteWeek As Date

' Return Database variable that points to current database.

Set dbs = CurrentDb

dteWeek = Now - 7

 intI = 0

' Enumerate through QueryDefs collection.

For Each qdf In dbs.QueryDefs

' Evaluate LastUpdated and DateCreated properties.

If (qdf.LastUpdated > dteWeek) Or (qdf.DateCreated > dteWeek) Then

' Create list.

strList = strList & Chr(13) & Chr(10) & qdf.Name

End If

intI = intI + 1

Next qdf

MsgBox strList

End Sub


DefaultValue Property Example (Microsoft Access)

The following example creates a new Field object and sets its DefaultValue property. The procedure then appends the new object to the Fields collection of the Employees table in the TableDefs collection of the database.

Sub NewField()

Dim dbs As Database, tdf As TableDef

Dim fld As Field

' Return Database object that points to current database.

Set dbs = CurrentDb

' Return TableDef variable pointing to Employees table.

Set tdf = dbs.TableDefs!Employees

' Create Field object.

Set fld = tdf.CreateField("DaysOfVacation", dbText, 20)

' Set field properties.

fld.DefaultValue = "10"

' Append fld to Fields collection.

tdf.Fields.Append fld

End Sub


DistinctCount Property Example (Microsoft Access)

The following example prints the number of unique values in the indexes on the OrderID and OrderDate fields of an Orders table. Note that the DistinctCount property is guaranteed to return the number of unique values in an index only immediately after an index has been created with the CreateIndex method, or after a database has been compacted or converted using the CompactDatabase method.

Sub CountKeys()

Dim dbs As Database, tdf As TableDef

Dim idx As Index, fldOrderID As Field, fldOrderDate As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Return TableDef object pointing to Orders table.

Set tdf = dbs.TableDefs!Orders

' Create new index.

Set idx = tdf.CreateIndex("OrderIDDate")

' Create and append index fields.

Set fldOrderID = idx.CreateField("OrderId", dbLong)

Set fldOrderDate = idx.CreateField("OrderDate", dbDate)

idx.Fields.Append fldOrderID

idx.Fields.Append fldOrderDate

' Append new index.

tdf.Indexes.Append idx

' Refresh Indexes collection.

tdf.Indexes.Refresh

' Print value of DistinctCount property for new index.

Debug.Print idx.DistinctCount

End Sub


Filter Property Example (Microsoft Access)

The following example prompts the user to enter the name of a country on which to filter, and then sets the Filter property of a dynaset-type Recordset object. For example, if the user enters "Italy", the filtered recordset will contain only those records in which the ShipCountry field contains the value "Italy".

Note that you first create a dynaset-type Recordset object and set its Filter property, then open a second dynaset-type Recordset object based on the first. Setting the Filter property of the first Recordset object doesn't actually affect the records that it contains, so you need to create a second Recordset object in order to see the effects of the filter.

Sub SetRecordsetFilter()

    Dim dbs As Database, strInput As String

    Dim rstOrders As Recordset, rstFiltered As Recordset

    ' Return Database variable that points to current database.

    Set dbs = CurrentDb

    ' Prompt for value on which to filter.

    strInput = InputBox("Enter name of country on which to filter.")

    ' Create dynaset-type Recordset object.

    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)

    ' Set filter condition.

    rstOrders.Filter = "[ShipCountry] = '" & strInput & "'"

    ' Create filtered dynaset-type Recordset object.

    Set rstFiltered = rstOrders.OpenRecordset()

    ' Populate recordset.

    rstFiltered.MoveLast

    ' Second Recordset object is now subset of the first.

    Debug.Print rstFiltered.RecordCount

End Sub

Tip   In most situations, it is more efficient to create the second Recordset object with the desired conditions in one step. When you know what data you want to select, it's generally more efficient to create a recordset with an SQL statement. The following example shows how you can create just one recordset and obtain the same results as in the preceding example.

Dim dbs As Database, rstOrders As Recordset, strInput As String

Set dbs = CurrentDb

strInput = InputBox("Enter name of country on which to filter.")

Set rstOrders = dbs.OpenRecordset("SELECT * FROM Orders " & _

"WHERE [ShipCountry] = '" & strInput & "';")


Foreign Property Example (Microsoft Access)

The following example prints the value of the Foreign property for each index in an Orders table.

Sub CheckForeign()

Dim dbs As Database, tdf As TableDef, idx As Index

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Orders

' Enumerate through Indexes collection of Orders table.

For Each idx In tdf.Indexes

' Print value of Foreign property.

Debug.Print idx.Name; "      "; idx.Foreign

Next idx

End Sub


ForeignName, ForeignTable, Table Properties Example (Microsoft Access)

The following example shows how you can use the ForeignName, ForeignTable, and Table properties when you create a relationship between two existing tables [image] in this case, Employees (the primary table) and Orders (the foreign table) in the current database. EmployeeID is the primary key in the Employees table, and also a foreign key in the Orders table. The relationship is one-to-many and referential integrity is enforced.

To test the following example in Microsoft Access, open the Northwind database and click Relationships on the Tools menu. Delete the relationship between the Employees table and the Orders table. Close the Relationships window, saving the current configuration when prompted. Then run the following procedure from a standard module, and view the Relationships window again to see the new relationship.

Sub ForeignNameTable()

Dim dbs As Database

Dim fld As Field, rel As Relation

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create new relationship and set its properties.

Set rel = dbs.CreateRelation("EmployeesRelation")

' Denote primary table.

rel.Table = "Employees"

' Denote foreign table.

rel.ForeignTable = "Orders"

rel.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

' Create field in Fields collection of Relation.

Set fld = rel.CreateField("EmployeeID")

' Provide name of foreign key field.

fld.ForeignName = "EmployeeID"

' Append field to Relation and Relation to database.

rel.Fields.Append fld

dbs.Relations.Append rel

End Sub


IgnoreNulls, Unique Properties Example (Microsoft Access)

The following example adds a new index to a TableDef object, makes that index the current index, and invokes the Seek method based on that index. The names of the Field objects created and appended to the Fields collection of the Index object correspond to the names of current fields in the table.

Sub NewIndex()

Dim dbs As Database, tdf As TableDef

Dim idx As Index, fldID As Field, fldName As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Products

' Return Index object that points to new index.

Set idx = tdf.CreateIndex("IDName")

' Create and append index fields.

Set fldID = idx.CreateField("ProductID")

Set fldName = idx.CreateField("ProductName")

idx.Fields.Append fldID

idx.Fields.Append fldName

' Set index properties.

idx.IgnoreNulls = True

idx.Unique = True

' Append the new Index object to the Indexes collection.

tdf.Indexes.Append idx

End Sub


Index Property Example (Microsoft Access)

The following example sets the index for a table-type Recordset object to the primary key. "PrimaryKey" is the default name of an Index object if the index corresponds to the primary key set in table Design view. Next, the procedure asks the user for a value to search on and locates the record with a matching key field value. Note that the current index must be set before certain operations, such as the Seek method, can be performed on a table-type Recordset object.

Sub UsePrimaryKey()

Dim dbs As Database, rst As Recordset

Dim fld as Field, strInput as String

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Orders", dbOpenTable)

' Set current index.

rst.Index = "PrimaryKey"

strInput = InputBox("Enter the OrderID on which to search.")

' Locate record.

rst.Seek "=", strInput

If Not rst.NoMatch Then

For Each fld in rst.Fields

Debug.Print fld.Name; "   "; fld.Value

Next fld

End If

' Close Recordset object.

rst.Close

End Sub


Inherit Property Example (Microsoft Access)

The following example sets the Inherit property of the Forms Container object to True (-1), and then sets the Permissions property to give users full security access to all forms. All new Document objects of the Form type will inherit these permissions. The procedure then creates a new Form object and displays the permissions for that object.

Note that the Forms Container object is different from the Forms collection. The Forms Container object includes all Form Document objects in the database; the Forms collection contains only currently open forms.

Sub FormPermissions()

Dim dbs As Database, ctrForms As Container

Dim docForm As Document, frmNew As Form

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Return Container variable pointing to Forms Container object.

Set ctrForms = dbs.Containers!Forms

' Set Inherit property.

ctrForms.Inherit = True

' Set permissions to be inherited.

ctrForms.Permissions = ctrForms.Permissions Or acSecFrmRptWriteDef

' Create new form.

Set frmNew = CreateForm()

' Save form.

DoCmd.Save , "OrderForm"

' Return Document object associated with new form.

Set docForm = ctrForms.Documents!OrderForm

' Compare permissions for Container and Document objects.

If docForm.Permissions <> ctrForms.Permissions Then

MsgBox "Error!"

Else

MsgBox "Permissions successfully inherited."

End If

End Sub


Inherited Property Example (Microsoft Access)

The following example creates a Property object in the Properties collection of a TableDef object, and then creates a new QueryDef object based on the same table. The Property object automatically exists in the Properties collection of the new QueryDef object. Next, the procedure checks the Inherited property for the Property objects in the Properties collections of both the TableDef and the QueryDef objects.

In the following example, the Microsoft Access DatasheetFontItalic property is created and appended to the Properties collection of the TableDef object. The DatasheetFontItalic property is defined by Microsoft Access rather than by the Microsoft Jet database engine. However, the property applies to data access objects. Therefore, in order to set it from Visual Basic code, you must first create a Property object corresponding to that property and append it to the Properties collection of the data access object. It is necessary to create the property only the first time you set it.

Sub CheckInherited()

Dim dbs As Database, tdfOrders As TableDef, qdfOrders As QueryDef

Dim prpTableDef As Property, prpQueryDef As Property

Dim strSQL As String

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdfOrders = dbs.TableDefs!Orders

' Create Property object and append to Properties collection.

Set prpTableDef = tdfOrders.CreateProperty("DatasheetFontItalic", _

dbBoolean, True)

tdfOrders.Properties.Append prpTableDef

Debug.Print prpTableDef.Inherited

' Create QueryDef based on Orders table.

strSQL = "SELECT * FROM Orders WHERE ShipCountry = 'USA'"

Set qdfOrders = dbs.CreateQueryDef("USAOrders", strSQL)

' Return Property object pointing to property.

 Set prpQueryDef = qdfOrders.Properties!DatasheetFontItalic

 Debug.Print prpQueryDef.Inherited

End Sub


LastModified Property Example (Microsoft Access)

The following example prints the value of the OrderDate field for the most recently modified record in a recordset.

' Include this statement in the Declarations section of the module.

Option Compare Binary

Sub LastChangedRecord()

Dim dbs As Database, rst As Recordset, varBook As Variant

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create dynaset-type Recordset object.

Set rst = dbs.OpenRecordset("SELECT * FROM Orders " & "ORDER BY [ShipCountry]")

With rst

' Populate recordset.

.MoveLast

' Move to first record and search.

.MoveFirst

.FindFirst "[ShipCountry] = 'UK'"

' Alter record and save changes.

.Edit

.[ShipCountry] = "USA"

.Update

End With

' Check to see if recordset supports bookmarks.

If rst.Bookmarkable Then

' Set bookmark to most recently modified record.

varBook = rst.LastModified

' Set currency to last modified record.

rst.Bookmark = varBook

' Display changed value.

Debug.Print rst.Fields!ShipCountry

End If

End Sub


LockEdits Property Example (Microsoft Access)

The following example opens a dynaset-type Recordset object and sets its LockEdits property to False (0). This enables optimistic locking so other users can change the database records at any time. Microsoft Access triggers a trappable error if the data changes before you use the Update method.

Sub ShowLockStatus()

Dim dbs As Database, rst As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)

rst.LockEdits = False

On Error Goto ErrorHandler

' Enable editing.

rst.Edit

.	' Change records.

.

.

rst.Update	' Try to post changes.

rst.Close

Exit Sub

ErrorHandler:

If Err.Number = 3197 Then

MsgBox "Data changed by another user."

Resume Next

Else

MsgBox "Some other error."

End If

End Sub


LoginTimeout Property Example (Microsoft Access)

The following example sets the LoginTimeout property to 120 seconds, then creates a query, and runs it on a database on an ODBC server.

Sub Login()

Dim dbs As Database

Dim qdf As QueryDef, rst As Recordset

' Set timeout to 120 seconds.

DBEngine.LoginTimeout = 120

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create query.

Set qdf = dbs.CreateQueryDef("All Employees", "SELECT * FROM Employees;")

qdf.Connect = "ODBC;DSN=Human Resources; " & _

"DATABASE=HRSRVR; UID=Smith; PWD=Sesame"

' Log in to server and run query.

Set rst = qdf.OpenRecordset()

End Sub


Name Property Example (Microsoft Access)

The following example creates two new TableDef objects and names them. The name of the first TableDef object is included as an argument to the CreateTableDef method. The name of the second TableDef object is set using the Name property, after the TableDef object has been created.

Note that you must define fields in the table before the TableDef object can be appended to the TableDefs collection.

Sub NameNewTables()

Dim dbs As Database

Dim tdfDefinitions As TableDef, tdfSynonyms as TableDef

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create and name TableDef object.

Set tdfDefinitions = dbs.CreateTableDef("Definitions")

' Create second TableDef object.

Set tdfSynonyms = dbs.CreateTableDef("")

' Set Name property for second TableDef object.

tdfSynonyms.Name = "Synonyms"

.	' Create fields.

.

.

dbs.TableDefs.Append tdfDefinitions

dbs.TableDefs.Append tdfSynonyms

End Sub


NoMatch Property Example (Microsoft Access)

The following example uses the NoMatch property to determine whether a FindFirst method process has been successful.

Function FindCountry() As Integer

Dim intI As Integer, varRecord() As Variant

Dim dbs As Database, rstOrders As Recordset

Dim strCountry As String

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create dynaset-type Recordset object.

Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)

strCountry = InputBox("Please enter country name.")

intI = 0

rstOrders.FindFirst "[ShipCountry] = '" & strCountry & "'"

If rstOrders.NoMatch Then

FindCountry = False

Exit Function

Else

Debug.Print rstOrders!OrderID

End If

FindCountry = True

End Function


ODBCTimeout Property Example (Microsoft Access)

The following example sets the ODBCTimeout property to 120 seconds, creates a query, and runs it on a database on an ODBC server.

Sub SetTimeout()

Dim dbs As Database

Dim qdfCustomers As QueryDef, rstCustomers As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create QueryDef.

Set qdfCustomers = dbs.CreateQueryDef("All Cust", _

"SELECT * FROM Customers;")

qdfCustomers.Connect = "ODBC;DSN=HumanResources;SERVER=HRSRVR: " & _

"UID=Smith; PWD=Sesame"

' Log in to server and run query.

qdfCustomers.ODBCTimeout = 120

Set rstCustomers = qdfCustomers.OpenRecordset()

End Sub


OrdinalPosition Property Example (Microsoft Access)

The following example changes the setting of the OrdinalPosition property for the first field in a Products table.

Sub SetPosition()

Dim dbs As Database, tdf As TableDef

Dim fldFirst As Field, fld As Field

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Products

' Return Field object pointing to first field in table.

Set fldFirst = tdf.Fields(0)

' Set OrdinalPosition property to last position in collection.

fldFirst.OrdinalPosition = tdf.Fields.Count

' Refresh Fields collection.

tdf.Fields.Refresh

' Enumerate all fields and print ordinal position.

For Each fld In tdf.Fields

Debug.Print fld.Name, fld.OrdinalPosition

Next fld

End Sub


Owner Property Example (Microsoft Access)

The following example changes the Owner property setting of a Module Document object after determining whether the current user has the appropriate permissions to do so.

Note that the And operator performs a bitwise comparison to determine whether a certain permission is currently set.

Sub ChangeOwner()

Dim dbs As Database, ctrModules As Container, docModule As Document

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Return Container variable that points to Modules container.

Set ctrModules = dbs.Containers!Modules

' Return Document object that points to ValuableFunctions module.

Set docModule = ctrModules.Documents!ValuableFunctions

' Compare current permissions with owner permissions.

If (docModule.Permissions And dbSecWriteOwner) <> 0 Then

docModule.Owner = "SomebodyElse"

End If

End Sub


PercentPosition Property Example (Microsoft Access)

The following example expresses the current position of a record in a recordset as a percent, then displays it in a message box.

Sub PercentOfRecords()

Dim dbs As Database, rstProducts As Recordset

Dim strInput As String, strSQL As String

strSQL = "SELECT * FROM Products ORDER BY ProductName;"

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Open table.

Set rstProducts = dbs.OpenRecordset(strSQL, dbOpenDynaset)

' Populate recordset.

rstProducts.MoveLast

' Return to start of recordset.

rstProducts.MoveFirst

' Prompt user to input name of product.

strInput = InputBox("Please enter the full product name.")

' Find first occurrence.

rstProducts.FindFirst "[ProductName] = '" & strInput & "'"

' Display current position as a percent.

MsgBox("The current position in the Recordset is " & _

rstProducts.PercentPosition & "%.")

End Sub


Permissions, UserName Properties Example (Microsoft Access)

The following example adds a new user account by creating a User object and appending it to the Users collection of the default workspace. It then sets permissions for the new user for all tables in the database. The procedure first sets the UserName property of a Tables Container object to the name of the new user, then sets the Permissions property to the appropriate permissions. Note that the security constants used to set the Permissions property are Integer values, so the + (plus sign) operator is used to add them.

Sub SetPermissions()

Dim dbs As Database, ctrTables As Container

Dim wspDefault As Workspace, usrNew As User

' Return Workspace object pointing to default workspace.

Set wspDefault = DBEngine.Workspaces(0)

' Create User object, specifying Name, PID, and Password.

Set usrNew = wspDefault.CreateUser("Kevin Smith", "ABC123XYZ123", "Password")

' Append to Users collection of default workspace.

wspDefault.Users.Append usrNew

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Return Container variable pointing to Tables Container object.

Set ctrTables = dbs.Containers!Tables

ctrTables.UserName = usrNew.Name

' Set new user's permissions for tables.

ctrTables.Permissions = dbSecInsertData + dbSecReplaceData + dbSecDeleteData

End Sub


Primary Property Example (Microsoft Access)

The following example checks the Primary property of each index on a Products table, and prints the fields making up the primary key.

Sub SetIndex()

Dim dbs As Database, tdf As TableDef, fld As Field

Dim idx As Index

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Products

' Enumerate through Indexes collection of TableDef.

For Each idx In tdf.Indexes

' Check Primary property of Recordset object.

If idx.Primary Then

For Each fld In idx.Fields

Debug.Print fld.Name

Next fld

Exit Sub

End If

Next idx

MsgBox "No primary key defined."

End Sub


QueryTimeout Property Example (Microsoft Access)

The following example opens the specified database and sets the QueryTimeout property to 120 seconds.

Sub SetTimeout()

Dim dbs As Database

' Return Database variable that points to current database.

Set dbs = CurrentDb

dbs.QueryTimeout = 120

End Sub


RecordCount Property Example (Microsoft Access)

The following example creates a Recordset object based on the Orders table and then determines the number of records in the Recordset object.

Sub CountRecords()

Dim dbs As Database, rstOrders As Recordset

Dim lngTotal As Long

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Open table-type Recordset object.

Set rstOrders = dbs.OpenRecordset("Orders")

' Fully populate Recordset object.

rstOrders.MoveLast

Debug.Print rstOrders.RecordCount

End Sub


RecordsAffected Property Example (Microsoft Access)

The following example prints the number of records updated by an action query.

Sub RecordsUpdated()

Dim dbs As Database, qdf As QueryDef

Dim strSQL As String

' Return Database variable that points to current database.

Set dbs = CurrentDb

strSQL = "UPDATE Employees SET Title = " & _

"'Senior Sales Representative' " & "WHERE Title = 'Sales Representative';"

' Create new QueryDef.

Set qdf = dbs.CreateQueryDef("UpdateTitles", strSQL)

' Execute QueryDef object.

qdf.Execute

Debug.Print qdf.RecordsAffected

End Sub


Required Property Example (Microsoft Access)

The following example creates a new Index object on an Employees table and sets the Required property for the index. The new index consists of two fields, LastName and FirstName.

Sub NewIndex()

Dim dbs As Database, tdf As TableDef, idx As Index

Dim fldLastName As Field, fldFirstName As Field, rst As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Return Index object that points to new index.

Set idx = tdf.CreateIndex("FullName")

' Create and append index fields.

Set fldLastName = idx.CreateField("LastName", dbText)

Set fldFirstName = idx.CreateField("FirstName", dbText)

idx.Fields.Append fldLastName

idx.Fields.Append fldFirstName

' Ensure value is entered for each field in the index.

idx.Required = True

' Append Index object.

tdf.Indexes.Append idx

End Sub


Restartable Property, Requery Method Example (Microsoft Access)

The following example checks the Restartable property setting of a dynaset-type Recordset object and then uses the Requery method on the Recordset object.

Sub RequerySource()

Dim dbs As Database, rstOrders As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)

If rstOrders.Restartable = True Then

rstOrders.Requery

Else

rstOrders.Close

End If

End Sub


ReturnsRecords Property Example (Microsoft Access)

The following example creates an SQL pass-through query that returns records from an external database.

Sub CreatePassThrough()

Dim dbs As Database, qdfPassThrough As QueryDef

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set qdfPassThrough = dbs.CreateQueryDef("OctoberOrders", _

"SELECT * From Orders WHERE ShipCity = 'London'")

dbs.Connect = "ODBC; DSN=MySQLDBDSN;  " _

& " UID=Chrissy;PWD=FourOh;"

qdfPassThrough.ReturnsRecords = True

End Sub


Size, Type Properties Example (Microsoft Access)

The following example creates a new Field object and sets its Size and Type properties. The procedure then appends the new object to the Fields collection of the Employees table in the TableDefs collection of the database.

Sub NewField()

Dim dbs As Database, tdf As TableDef

Dim fld As Field

' Return Database object that points to current database.

Set dbs = CurrentDb

' Return TableDef variable pointing to Employees table.

Set tdf = dbs.TableDefs!Employees

' Create Field object.

Set fld = tdf.CreateField("DaysOfVacation")

' Set field properties.

fld.Type = dbText

fld.Size = 20

' Append fld to Fields collection.

tdf.Fields.Append fld

End Sub

You can also use the CreateField method to set the Name, Type, and Size properties if you provide name, type, and size as arguments.

Set fld = tdfEmployees.CreateField("DaysOfVacation", dbText, 20)


Sort Property Example (Microsoft Access)

The following example uses the Sort property to set the sort order of a dynaset-type Recordset object based on an Orders table. The records in the rstSorted recordset variable will be ordered alphabetically by ship country.

Note that you first create a dynaset-type Recordset object and set its Sort property, then open a second dynaset-type Recordset object based on the first. Setting the Sort property of the first Recordset object doesn't actually affect the order of the records that it contains, so you need to create a second Recordset object in order to see the effects of the sort.

Sub SortByCountry()

Dim dbs As Database

Dim rstOrders As Recordset, rstSorted As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Create dynaset-type Recordset object on Orders table.

Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)

' Set sort order.

rstOrders.Sort = "ShipCountry"

' Create second dynaset-type Recordset object.

Set rstSorted = rstOrders.OpenRecordset()

End Sub

Tip   In most situations, it is more efficient to create the second Recordset object with the desired conditions in one step. As a general rule, when you know the data you want to select, it's usually more efficient to create a Recordset object with an SQL statement. The following example shows how you can create just one Recordset object and obtain the same results as in the preceding example.

Dim dbs As Database, rstOrders As Recordset

Dim strSelect As String

Set dbs = CurrentDb

strSelect = "SELECT * FROM Orders ORDER BY [ShipCountry];"

Set rstOrders = dbs.OpenRecordset(strSelect)


SourceField, SourceTable Properties Example (Microsoft Access)

The following example creates a Recordset object using an SQL statement that creates aliases for fields in two different tables in the database. The example then prints the name of the field, the original table, and the original field.

Sub SourceInfo()

Dim dbs As Database, rst As Recordset, fld As Field

Dim strSQL As String

' Return Database object that points to current database.

Set dbs = CurrentDb

' Construct SQL statement.

strSQL = "SELECT ProductID As ProductCode, " & _

"CategoryName As TypeOfProduct FROM Products, Categories;"

Set rst = dbs.OpenRecordset(strSQL)

For Each fld in rst.Fields

Debug.Print fld.Name	' Print field name.

Debug.Print fld.SourceTable	' Print original table name.

Debug.Print fld.SourceField	' Print original field name.

Debug.Print

Next fld

End Sub


SQL Property Example (Microsoft Access)

The following example creates a parameter query based on an Orders table. The query selects all orders for which the order date falls between the dates entered by the user.

Sub RangeOfOrders()

    Dim dbs As Database, qdf As QueryDef, rst As Recordset

    ' Return Database variable that points to current database.

    Set dbs = CurrentDb

    Set qdf = dbs.CreateQueryDef("RangeOfOrders")

    ' Construct SQL statement including parameters.

    qdf.SQL = "PARAMETERS [Start] DATETIME, [End] DATETIME; " & _

    "SELECT * FROM Orders WHERE [OrderDate] BETWEEN " & _

    "[Start] AND [End];"

    qdf.Parameters("Start") = #1/1/95#

    qdf.Parameters("End") = #1/31/95#

    ' Create snapshot-type Recordset object from QueryDef object.

    Set rst = qdf.OpenRecordset(dbOpenSnapshot)

End Sub


Updatable Property Example (Microsoft Access)

The following example adds a record to a Recordset object if the object's Updatable property setting is True.

Sub UpdateData()

Dim dbs As Database, rstUnknown As Recordset

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set rstUnknown = dbs.OpenRecordset("Unfamiliar Table")

' Check Updatable property before adding new record.

If rstUnknown.Updatable = True Then

With rstUnknown

.AddNew

!SomeField = "Some new data"

.Update

End With

End If

dbs.Close

End Sub


ValidationRule, ValidationText Properties Example (Microsoft Access)

The following example sets the ValidationRule and ValidationText properties for two fields in an Order Details table. Once these properties have been set, you can view them in table Design view as well.

Sub SetValidation()

Dim dbs As Database, tdf As TableDef

Dim fldQuantity As Field, fldDiscount As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs![Order Details]

Set fldQuantity = tdf.Fields!Quantity

Set fldDiscount = tdf.Fields!Discount

' Set ValidationRule and ValidationText.

fldQuantity.ValidationRule = ">= 4"

fldQuantity.ValidationText = "Quantity must be four or more items."

fldDiscount.ValidationRule = "Between .05 and .30"

fldDiscount.ValidationText = "Discount must be between 5% and 30%."

End Sub


DELETE Statement (SQL) (Microsoft Access)

In Microsoft Access, no query output or datasheet is produced when you use the DELETE statement. If you want to know which records will be deleted, first view the datasheet of a select query that uses the same criteria, and then run a delete query.


INSERT INTO Statement (SQL) (Microsoft Access)

If you create an INSERT INTO...VALUES query in SQL view, save and close the query, and then reopen it, you'll see that Microsoft Access has converted the VALUES clause to a SELECT clause. This doesn't alter the results of the query.

Using the INSERT INTO statement is equivalent to setting the DestinationTable property in the query property sheet in query Design view.


PARAMETERS Declaration (SQL) (Microsoft Access)

With a parameter query, Microsoft Access prompts you for the criteria when the query is run. This eliminates the extra step of opening the query in Design view and changing the criteria.

The name argument that you supply with the PARAMETERS declaration provides the text that is displayed in a dialog box when you run the query. Microsoft Access automatically creates this dialog box for you.

Using the PARAMETERS declaration in SQL view is equivalent to defining parameters in the Criteria cell of the query design grid.


PROCEDURE Clause (SQL) (Microsoft Access)

When you copy a PROCEDURE clause into SQL view, Microsoft Access removes the clause from the SQL statement when you switch to another view. Removing the PROCEDURE clause doesn't affect the result of the query. If the PROCEDURE clause includes parameters, Microsoft Access replaces PROCEDURE and name with the PARAMETERS reserved word. The defined parameters aren't removed.


UNION Operation (SQL) (Microsoft Access)

In Microsoft Access, the arguments for the UNION operation (query1, query2,...queryn) can be a SELECT statement, the name of a stored Microsoft Access query, or the name of a stored Microsoft Access table preceded by the TABLE reserved word.

You can view a union query only in SQL view, not in the query design grid.


UPDATE Statement (SQL) (Microsoft Access)

Since the UPDATE statement doesn't generate a result set, there is no query datasheet for Microsoft Access to display a when you use this statement.

If you want to confirm each change, you can use the Replace command on the Edit menu of the form or datasheet rather than an update query.


WITH OWNERACCESS OPTION Declaration (SQL) (Microsoft Access)

You can include the WITH OWNERACCESS OPTION declaration in an SQL statement to allow users to run a query and view the results even if they don't have security permissions on the underlying tables. When this option is set, a user is granted the same permissions as the owner of the query.

For example, the owner of a query on an Employees table can include the WITH OWNERACCESS OPTION declaration in the definition of the query that returns all fields except those that give employees' addresses. A user who does not have read permissions on the Employees table can then run the query and view only those fields included in the query. Effectively, the user now has restricted read access to the table.

Using this declaration is equivalent to setting the RunPermissions property to Owner's in the query property sheet in query Design view. Omitting the declaration is equivalent to setting the RunPermissions property to User's, which is the default setting.


Between...And Operator (Microsoft Access)

In Microsoft Access, you can use the Between...And operator in a query expression or in a calculated control on a form or report.

You can use the Between...And operator in the Criteria field in the query design grid to create a parameter query. The Between...And operator enables you to prompt the user to enter a range of values by which the query is restricted. The expression supplied for the value1 argument serves as the prompt for the beginning of the range, and the expression supplied for the value2 argument serves as the prompt for the end of the range.

For example, suppose you have an Orders table that has an OrderDate field. Create a new query in the query design grid and drag the OrderDate field to the first Field cell in the grid. In the Criteria field, enter the following statement.

Between [Enter beginning date:] And [Enter ending date:]

When you run the query, you will first be prompted with a dialog box that says "Enter the beginning date for the range:". Once you enter a value in that dialog, you will be prompted by a second dialog box that says "Enter the ending date for the range:". Assuming you enter valid dates, your results will include all the values in the OrderDate field that either match or fall between the dates you specified as parameters.

You can also use the Between...And operator in a calculated control to determine whether the value of the control falls within a specified numeric range. The following example determines whether an order was shipped to a location within a range of zip codes. If the ZIP field is between 98101 and 98199, the IIf function returns "Local". Otherwise, it returns "Nonlocal".

= IIf([ZIP] Between "98101" And "98199", "Local", "Nonlocal")

If any of the arguments supplied for the Between...And expression is Null, Between...And returns a Null.


In Operator (Microsoft Access)

In Microsoft Access, you can use the In operator in a query expression or in a calculated control on a form or report.

You can use the In operator in a query expression when you need to set a number of criteria. For example, suppose you have an Orders table with both a ShipCountry field and an OrderID field, and you want to create a query to show all orders sent to the United States, Canada, or the United Kingdom. Create a new query in the Query window and add the Orders table. Drag the OrderID field and the ShipCountry field to the query design grid. Enter the following expression in the Criteria cell below the ShipCountry field.

In('USA', 'Canada', 'UK')

When you run the query, you will see all orders shipped to one of these three countries.

You can achieve the same result by entering the following expression in the Criteria cell.

"USA" Or "Canada" Or "UK"

With a long list of criteria, it may be more convenient to use the In operator than the Or operator. In addition, the SQL statement for the expression containing the In operator is shorter.

You can use the In operator in a calculated control to determine whether the value of a field in the current record is within a set of values. For example, you might use the In operator with the IIf function to determine whether the value of a control is among a set of specified values. In the following example, if the ShipRegion is WA, OR, or ID, the IIf function returns "Local". Otherwise, it returns "Nonlocal".

= IIf([ShipRegion] In ('WA','OR','ID'), "Local", "Nonlocal")


Like Operator (Microsoft Access)

In Microsoft Access, you can use the Like operator in a query expression, in a macro or module, or in a calculated control on a form or report.

The case sensitivity and character sort order of the Like operator depend on the setting of the New Database Sort Order box on the General tab of the Options dialog box, available by clicking Options on the Tools menu. By default, the New Database Sort Order is set to General, which specifies a case-insensitive sort order for the database.

You can alter the sort order for an individual Visual Basic module by changing the Option Compare statement in the Declarations section of a module. By default the Declarations section is set to Option Compare Database, which specifies the same sort order that currently applies to the rest of the database. If the module doesn't contain an Option Compare statement, the default string-comparison method is Binary, which is case-sensitive.

You can use the Like operator to specify inexact criteria in the query design grid. For example, if you type Like "C*" in the Criteria row of the query design grid, the query returns all field values beginning with the letter C.

In a parameter query, you can use the Like operator to prompt the user for a pattern to search for. For example, suppose you have an Employees table that includes a LastName field. In the Query window, create a new query by adding the Employees table and dragging the LastName field to the grid. Enter the following expression in the Criteria row.

Like [Enter first few letters of name:]&"*"

When the query is run, a dialog box prompts the user with "Enter first few letters of name:". If the user types Sm in the dialog box, the query looks for the pattern Sm* [image] that is, all names beginning with the letters Sm.

You can use Like in an expression as a setting for the ValidationRule property or as a macro condition. For example, you can restrict data entered in a text box control to an inexact specification. In the ValidationRule property of the text box, enter the following expression.

Like "P[A-F]###"

Data entered in this text box must now begin with the letter P, followed by any letter between A and F and three digits.


Avg Function Example (Microsoft Access)

The following example assumes that you have an Orders table that contains a field called Freight. You can use the Avg function to calculate the average freight charges for orders with freight charges over $100. Enter the following expression in SQL view in the Query window.

SELECT Avg([Freight]) AS [AverageFreight] FROM Orders WHERE [Freight] > 100;

The next example creates a calculated control that displays the average freight charges for all of the underlying records in a form based on the same Orders table. Open a new form and set its RecordSource property to Orders. Enter this expression in a the ControlSource property of a text box on the form. To apply a condition that limits the calculation to only some records, such as those for orders greater than $100, set the form's Filter property.

= Avg([Freight])


Count Function Example (Microsoft Access)

The following example assumes that you have an Orders table that contains a field called ShipCountry. You can use the Count function to calculate the number of orders shipped to the United Kingdom. Enter the following expression in SQL view in the Query window.

SELECT Count([ShipCountry]) AS [UKOrders] FROM Orders WHERE [ShipCountry] = 'UK';

The next example creates a calculated control that displays the number of orders in the same Orders table. Open a new form and set its RecordSource property to Orders. Enter the following expression in the ControlSource property of a text box on the form. To apply a condition that limits the count to only some records, such as those for orders shipped to the United Kingdom, set the form's Filter property.

= Count([ShipCountry])


Min, Max Functions Example (Microsoft Access)

The following example assumes that you have an Orders table that contains fields called Freight and ShipCountry. You can use the Min and Max functions to return the lowest and highest freight charges for orders shipped to the United Kingdom. Enter the following expressions in SQL view in the Query window.

SELECT Min([Freight]) AS [LowFreight] FROM Orders WHERE [ShipCountry] = 'UK';

SELECT Max([Freight]) AS [HighFreight] FROM Orders WHERE [ShipCountry] = 'UK';

The next example creates a calculated control that displays the lowest freight charge for all records in the same Orders table. Open a new form and set its RecordSource property to Orders. Enter the following expression in the ControlSource property of a text box on the form. To apply a condition that limits the search to only some records, such as those for orders shipped to the United Kingdom, set the form's Filter property.

= Min([Freight])


StDev, StDevP Functions Example (Microsoft Access)

The following example assumes that you have an Orders table that contains fields called Freight and ShipCountry. You can use the StDev and StDevP functions to return the standard deviation of freight charges for orders shipped to the United Kingdom. Enter the following expressions in SQL view in the Query window.

SELECT StDev([Freight]) AS [FreightDev] FROM Orders WHERE [ShipCountry] = 'UK';

SELECT StDevP([Freight]) As [FreightDevP] FROM Orders WHERE [ShipCountry] = 'UK';

The next example creates a calculated control that displays the standard deviation of freight charges for all records in the same Orders table. Open a new form and set its RecordSource property to Orders. Enter the following expression in the ControlSource property of a text box on the form. To apply a condition that limits the search to only some records, such as those for orders shipped to the United Kingdom, set the form's Filter property.

= StDev([Freight])


Sum Function Example (Microsoft Access)

The following example assumes that you have an Orders table that contains fields called OrderID and ShipCountry, and an OrderDetails table that contain fields called UnitPrice and Quantity. You can use the Sum function to calculate the total sales for orders shipped to the United Kingdom. Enter the following expression in SQL view in the Query window.

SELECT Sum([UnitPrice]*[Quantity]) AS [TotalUKSales]

FROM Orders INNER JOIN [OrderDetails] ON Orders.[OrderID] = [OrderDetails].[OrderID]

WHERE ([ShipCountry] = 'UK');

The next example creates a calculated control that displays the total sales for records in the OrderDetails table. Open a new form and set its RecordSource property to Order Details. Enter the following expression in the ControlSource property of a text box on the form. To apply a condition that limits the search to only some records, such as those for orders shipped to the United Kingdom, set the form's Filter property.

= Sum([UnitPrice]*[Quantity])


Var, VarP Functions Example (Microsoft Access)
Var, VarP Functions Examples (Microsoft Access)

The following example assumes that you have an Orders table that contains a field called Freight. You can use the Var and VarP functions to estimate the variance of freight costs for orders shipped to the United Kingdom. Enter the following expressions in SQL view in the Query window.

SELECT Var([Freight]) AS [UKFreightVariance] FROM Orders WHERE [ShipCountry] = 'UK';

SELECT VarP([Freight]) AS [UKFreightVarianceP] FROM Orders WHERE [ShipCountry] = 'UK';

The next example creates a calculated control that displays the estimated variance for freight costs for records in the same Orders table. Open a new form and set its RecordSource property to Orders. Enter the following expression in the ControlSource property of a text box on the form. To apply a condition that limits the search to only some records, such as those for orders shipped to the United Kingdom, set the form's Filter property.

= Var([Freight])


#Const Directive (Microsoft Access)

In Microsoft Access, you can define a public conditional compiler constant in the Declarations section of a module. A public compiler constant is applicable to all modules in the current database, but not in any other database.

You can also declare a public compiler constant by clicking Options on the Tools menu, then clicking the Module tab. Enter the constant in the Conditional Compilation Arguments box.

For example, you might enter the following expression into the Conditional Compilation Arguments box.

conDebug = 1

You can use a public compiler constant in code in any module. For example, you can create an #If...Then...#Else construct to optionally run debug code.

#If conDebug = 1 Then

.	' Run debug code.

.

.

#Else

.	' Run streamlined code.

.

.

#End If

To create multiple public conditional compilation constants, declare them on separate lines in the Declaration section. In the Conditional Compilation Arguments box, separate them with colons. For example, you can enter the following conditional compilation constants in the Conditional Compilation Arguments box.

conActiveLanguage = 0 : conDebug = 1

Using a logical operator, you can include both constants in an #If...Then...#Else construct. In the following example, you can run the first segment of code only if an expression containing both constants is true.

#If (conActiveLanguage = 0 And conDebug = 1) Then

.	' Run debug code for the active language version.

.

.

#Else

.	' Run another code segment.

.

.

#End If

Note   A conditional compilation constant is always evaluated with a text-based string comparison method. This evaluation is equivalent to having an Option Compare Text statement in the module and occurs even if the module contains an Option Compare Database statement.

When writing code for conditional compilation, it may be less confusing to view one procedure at a time rather than all procedures in the module. To change how you view your code, click Options on the Tools menu, then click the Module tab. In the Code View section, clear the Full Module View check box.


Choose Function Example (Microsoft Access)

You can use the Choose function to set the value of one field based on the value of another field. For example, suppose you have an Orders table that contains a field called OrderDate. You could create a calculated control on a form to display the day of the week on which an order was placed. The following example uses the Weekday function to return the numeric representation of the day of the week corresponding to the order date. The Choose function returns a string containing the name of the day of the week based on that numeric value.

= Choose(Weekday([OrderDate]) - 1, "Monday", "Tuesday", "Wednesday", "Thursday", "Friday")


DateAdd Function Example (Microsoft Access)

The following example uses DateAdd to find the number of days in a month. The function DaysInMonth takes an argument of type Date.

Function DaysInMonth(dteAny As Date) As Integer

Dim dteNextMonth, dteEndOfMonth

dteNextMonth = DateAdd("m", 1, dteAny)

dteEndOfMonth = dteNextMonth - DatePart("d", dteNextMonth)

DaysInMonth = DatePart("d", dteEndOfMonth)

End Function

The next example shows how you can create a calculated control using the DateAdd function to display the date by which a particular order must be shipped [image] in this case, thirty days after the OrderDate. Suppose you have a form based on an Orders table, with a field called OrderDate. You can create another text box on the form to display the shipping date by setting the ControlSource property of this text box as in the following example.

= DateAdd("d", 30, [OrderDate])


DateDiff Function Example (Microsoft Access)

The following examples use the DateDiff function to calculate the number of calendar weeks between the first of the year and today, and the number of days since February 1, 1995 respectively.

Debug.Print DateDiff("ww", "1-1", Now())

Debug.Print DateDiff("y", #1-Feb-1995#, Now())

The next example shows how to use the DateDiff function in a query expression. Suppose you have an Orders table that contains an OrderDate field and a ShippedDate field. You can create a calculated field in a query to display the time elapsed between an order date and a shipped date for each order. In the Query window, create a new query by adding the Orders table and dragging the OrderID field to the query design grid. In an empty Field cell, enter the following to create a calculated field.

DaysElapsed: DateDiff("y", [OrderDate], [ShippedDate])


DatePart Function Example (Microsoft Access)

The following example uses the DatePart function to determine the day of the week on which an order was placed.

Function DayOfWeek (OrderDate As Date) As Integer

DayOfWeek = DatePart("w", OrderDate)

End Function

The next example uses the DatePart function to specify criteria for a select query. For example, suppose you want to create a query based on an Orders table to list all orders placed in the first quarter of 1995. Assuming your Orders table has a OrderID field and an OrderDate field, you can drag the OrderID field to the first cell in the query design grid, and enter the following in the Criteria cell beneath it.

(DatePart("q", [OrderDate]) = 1) and (DatePart("yyyy", [OrderDate]) = 1995)


IIf Function Example (Microsoft Access)

This example uses the IIf function to evaluate an OrderAmount field and returns the word "Large" if the amount is greater than 1000; otherwise, it returns the word "Small". You can enter the following expression in the ControlSource property of a calculated control.

= IIf([OrderAmount] > 1000, "Large", "Small")

In the next example, the IIf function returns a zero-length string if the ShipCountry field is Null; otherwise, it returns the field's contents.

= IIf(IsNull([ShipCountry]), "", [ShipCountry])

The following example shows how you can use the IIf function to return a string containing an employee's first name, middle initial, and last name. If there is no middle initial, the expression returns the first and last names separated by a single space. If there is a middle initial, however, it includes the middle initial and a period.

= [FirstName] & IIf(IsNull([Initial]), "", " " & [Initial] & ".") & " " & [LastName]


Partition Function Example (Microsoft Access)

You can use the Partition function in the query design grid as well as in the SQL view of the Query window. In the query design grid, you can use the Partition function in a calculated field or to specify criteria for a select query.

The following example shows how you can use the Partition function to create a calculated field that lists how many records fall into each specified range. Suppose you have an Orders table that contains a Freight field. In the Query window, create a new Totals query by adding the Orders table and clicking on the Totals button in the Query toolbar. Drag the Freight field to the first Field cell on the query design grid, and set the value of the Total cell to Count. In another field cell, enter the following expression.

Range: Partition([Freight], 0, 1000, 50)

Set the Total cell below this field to Group By, and run the query. The Partition function returns eleven ranges (0:99, 100:199, 200:299, and so on). The query shows the number of orders with freight charges falling into each range.

The next example shows how you can use the Partition function in the SQL view of the Query window. It creates a crosstab query that evaluates a Freight field in an Orders table. It calculates the number of orders for each customer for which freight cost falls within one of several ranges. The ranges are defined by the arguments to the Partition function: start = 0, stop = 1000, interval = 50.

Enter the following expression in SQL view. When you run this query, each range will appear as a column heading.

TRANSFORM Count(Orders.[OrderID]) AS [CountOfOrderID]

SELECT Orders.[CustomerID]

FROM Orders

GROUP BY Orders.[CustomerID]

PIVOT Partition(Int([Freight]), 0, 1000, 50);


Switch Function Example (Microsoft Access)

The following example uses the Switch function to determine the appropriate language for a specified city by evaluating the ShipCountry and ShipCity fields against a table named Language that contains country names and languages. You can enter the following expression in a calculated control on a form or report. The expression is shown on multiple lines for clarity; you can also enter it on a single line.

= Switch([ShipCity] = "Madrid", "Spanish", _

[ShipCity] = "Berlin", "German", _

[ShipCity] = "Torino", "Italian", _

[ShipCountry] = "France", "French", _

True, "English")

If the city is Madrid, the Switch function returns "Spanish"; if it is Berlin, it returns "German"; and so on. If the city is not one of those listed, but the country is France, it returns "French". Otherwise, the Switch function returns "English".


Add Method Example (Microsoft Access)

The following example creates a user-defined collection and uses the Add method to add new objects to it. To test this example, create a new form and save it as Form1. In the form's module, declare a public variable varInstName. Your new user-defined objects will be derived from this form and its module.

You can manipulate individual objects within the collection by calling the methods or setting the properties that you have defined for them. In the module of Form1, enter the following code.

' Declare public variable in Declarations section of form module.

Public varInstName As Variant

Public Sub ChangeCaption (strCaption As String)

Me.Visible = True

Me.Caption = strCaption

End Sub

This Sub procedure becomes a method that you can perform on your user-defined object, once you have created a new instance of the object.

With these steps completed, enter the following procedure in a standard module. When you run this procedure, it will create new instances of your user-defined object and add them to the collection defined by the Collection object. Then it will display the objects (forms in this case) with their changed captions.

Function MakeCollection()

' Create new Collection object using New keyword.

Dim colFormObjects As New Collection

' Declare other variables.

Dim strObjName As String, strList As String

Dim strMsg As String, strCRLF As String

Dim intI As Integer

strCRLF = Chr(13) & Chr(10)

' Create objects and add them to collection.

Do

' Create new instance of user-defined object.

Dim frmObject As New Form_Form1

strMsg = ("Please enter a name for this new object." & strCRLF _

& "Press Cancel to see the items in the collection.")

' Get name for this instance of object.

strObjName = InputBox(strMsg, "Name the collection item:")

' Store name in public variable you declared in form module.

frmObject.varInstName = strObjName

' If user entered name, add object to collection.

If frmObject.varInstName <> "" Then

colFormObjects.Add Item := frmObject

End If

' Clear object variable so it can be set to next instance.

Set frmObject = Nothing

Loop Until strObjName = ""

' Enumerate and display items in collection.

' Note that items in Collection object are indexed beginning with 1.

intI = 1

For Each frmObject In colFormObjects

strList = strList & frmObject.varInstName & strCRLF

frmObject.ChangeCaption ("New Form #" & intI)

intI = intI + 1

Next frmObject

' Display list of object names.

MsgBox strList, , "Objects in colFormObjects collection"

End Function


Count Property Example (Microsoft Access)

The following example uses the Collection object's Count property to specify how many iterations are required to remove all elements of the Collection object colThings. User-defined collections are indexed numerically. For a user-defined collection, the default base index is 1. Since collections are automatically reindexed when an item is removed, the following code removes the first member on each iteration.

Note that you can also use the For Each...Next statement to manipulate items in a collection.

Dim intI

For intI = 1 to colThings.Count

colThings.Remove 1

Next


Remove Method Example (Microsoft Access)

The following example shows how to use the Remove method to delete items from a Collection object colThings. User-defined collections are indexed numerically. For a user-defined collection, the default base index is 1. Since collections are automatically reindexed when an item is removed, the following code removes the first member on each iteration.

Note that you can use the For Each...Next syntax to manipulate items in a collection.

For Each objMine in colThings

objThings.Remove 1

Next objMine


Collection Object Example (Microsoft Access)

The following example creates a Collection object, and then creates a dialog box in which users can add user-defined objects to the collection. To test this example, create a new form and save it as Form1. In the form's module, declare a public variable varInstName. Your new user-defined objects will be derived from this form and its module.

You can manipulate individual objects within the collection by calling the methods or setting the properties that you have defined for them. In the module of Form1, enter the following code.

' Declare public variable in Declarations section of form module.

Public varInstName As Variant

Public Sub ChangeCaption (strCaption As String)

Me.Visible = True

Me.Caption = strCaption

End Sub

This Sub procedure becomes a method which you can perform on your user-defined object, once you have created a new instance of the object.

Once you have completed these steps, enter the following procedure in a standard module. When you run this procedure, it will create new instances of your user-defined object and add them to the collection defined by the Collection object. Then it will display the objects (forms in this case) with their changed captions.

Function MakeCollection()

' Create new Collection object using New keyword.

Dim colFormObjects As New Collection

' Declare other variables.

Dim strObjName As String, strList As String

Dim strMsg As String, strCRLF As String

Dim intI As Integer

strCRLF = Chr(13) & Chr(10)

' Create objects and add them to collection.

Do

' Create new instance of user-defined object.

Dim frmObject As New Form_Form1

strMsg = ("Please enter a name for this new object." & strCRLF _

& "Press Cancel to see the items in the collection.")

' Get name for this instance of object.

strObjName = InputBox(strMsg, "Name the collection item:")

' Store name in public variable you declared in form module.

frmObject.varInstName = strObjName

' If user entered name, add object to collection.

If frmObject.varInstName <> "" Then

colFormObjects.Add Item := frmObject

End If

' Clear object variable so it can be set to next instance.

Set frmObject = Nothing

Loop Until strObjName = ""

' Enumerate and display items in collection.

' Note that items in Collection object are indexed beginning with 1.

intI = 1

For Each frmObject In colFormObjects

strList = strList & frmObject.varInstName & strCRLF

frmObject.ChangeCaption ("New Form #" & intI)

intI = intI + 1

Next frmObject

' Display list of object names.

MsgBox strList, , "Objects in colFormObjects collection"

End Function


Date Function (Microsoft Access)

You can insert the current date on a form or report by clicking Date And Time on the Insert menu. This command creates a text box on a form or report and sets the ControlSource property of the text box to the expression =Date(). The command is available only in form Design view and report Design view.

You can also use the Date function in a query expression or in a macro. However, you must include the parentheses after the function when you use it anywhere other than a Visual Basic module, as in the expression Date().


Time Function (Microsoft Access)

You can insert the current time on a form or report by clicking Date And Time on the Insert menu. This command creates a text box on a form or report and sets the ControlSource property of the text box to the expression =Time(). It is available only in form Design view and report Design view.

You can also use the Time function in a query expression or in a macro. However, you must include the parentheses after the function when you use it anywhere other than in a Visual Basic module, as in the expression Time().


Const Statement (Microsoft Access)

A constant in a Microsoft Access standard module is private by default. If you precede a constant with the keyword Public, however, it is then available to other modules in the current database. It is also available to modules that reference the current database. A constant defined in a form module or report module is always private to that module, and can't be made public using the Public keyword.


Declare Statement (Microsoft Access)

In Microsoft Access, Declare statements are public by default. In a standard module, a public Declare statement is available to both the current database and any referencing databases. You can preface a Declare statement with the Private keyword to ensure that it is not available outside of the current database. To use a Declare statement in a form module or report module, you must precede the statement with the Private keyword.


Dim Statement (Microsoft Access)

You can use the New keyword to declare an object variable of a specific type. If you include the New keyword in your variable declaration, you automatically create a new instance of the object and point the object variable to it. Therefore, if you declare an object variable using the New keyword, you don't need to use the Set statement.

Using the New keyword, you can create an object variable to point to any type of object [image] a Microsoft Access object, data access object, user-defined object, or Visual Basic Collection object. For example, you can use the following statements to create a new TableDef object.

' Create object variable and point it to new object.

Dim tdfOrders as New TableDef

' Set Name property of object.

tdfOrders.Name = "Orders"

These statements are equivalent to the following two statements.

' Create an empty object variable.

Dim tdfOrders As TableDef

' Create the new TableDef object and point the object variable to it.

' "dbs" is an object variable pointing to the current database.

Set tdfOrders = dbs.CreateTableDef("Orders")

You should use the New keyword to create a Collection object. For example, the following statement creates a new Collection object.

Dim colCustomers As New Collection

You should also use the New keyword to create a new instance of a user-defined object. For example, a form whose module contains public procedures can be a user-defined object. If a form called CustomerObject has a module in which you have defined procedures as public, you can create a new instance of this form object from another module, and its procedures become methods and properties of the object. You can then manipulate the form object by calling the methods and setting the properties you defined in the form module.

' Create new instance of your object.

Dim frmCustomer As New Form_CustomerObject

' Call the method Show from the form object.

frmCustomer.Show

You can use the New keyword to create a new instance of a Microsoft Access object from some applications that are OLE Automation controllers. Check the other application's documentation to determine whether or not the application supports this syntax.

For example, from another OLE Automation controller, assuming you have established a reference to the Microsoft Access type library, you can create a new Microsoft Access object with the following code.

Dim appAccess as New Access.Application


Function Statement (Microsoft Access)

In Microsoft Access, a public Function procedure in a standard module is available to all other procedures in the database and in all other Microsoft Access databases. However, it is not available to any other applications.

If a Function procedure is declared as public within a private module, then the procedure is available to all other procedures in that database, but is not available to other Microsoft Access databases. Form modules and report modules are always private, as are standard modules that contain an Option Private Module statement.

If you declare a Function procedure as private in any module, that procedure is available only to other procedures in the same module.

Note   If a Function procedure is available to other Microsoft Access databases, it will be visible from those databases in the Object Browser.


Option Compare Statement (Microsoft Access)

In Microsoft Access, all modules by default contain the Option Compare Database statement in the Declarations section. This statement sets the string comparison method for the module to the one specified for the entire database.

You can change the string comparison method for all new databases by clicking Options on the Tools menu and changing the value in the New Database Sort Order box on the General tab of the Options dialog box. By default this value is set to General, which specifies a case-insensitive sort order based on the English alphabet.

Changing the New Database Sort Order option doesn't affect the string comparison method for the current database.  It only affects databases that are created after the option has been set.

To specify a string comparison method for an individual module that is different from the one specified for the database, change Option Compare Database to Option Compare Binary or Option Compare Text. The Option Compare Binary statement compares strings by evaluating the ASCII values corresponding to the characters they contain, so the statement is case-sensitive. Option Compare Text is case-insensitive.

Note   If you are using the Bookmark property of a Recordset object, you must include an Option Compare Binary statement in the Declarations section of the module. The setting and return value for the Bookmark property is a Variant array of Byte data. If you use a case-insensitive string comparison method, the Bookmark property may point you to the wrong record.


Option Explicit Statement (Microsoft Access)

In Microsoft Access, you can ensure that all new form modules, report modules, and standard modules will automatically include the Option Explicit statement. On the Tool menu, click Options, then click the Module tab, and select the Require Variable Declaration option.

Once you select this option, it will automatically be set for all other databases which you create or open with Microsoft Access.

Note   When you select this option, only new modules will have the Option Explicit setting. You must enter the Option Explicit statement into the Declarations section of any existing modules; or when the module is open, on the Tools menu click Options, click the Module tab, and select the Require Variable Declaration option.


Option Private Statement (Microsoft Access)

When you include the Option Private statement in a standard module in Microsoft Access, any public identifiers in the module are still available to all other procedures in the current database. However, they are not available to other databases.

Note   If a standard module includes the Option Private statement, it will not be visible in the Object Browser from any other referencing Microsoft Access database.

You cannot have an Option Private statement in form modules or report modules because they are always private.


Property Get Statement (Microsoft Access)

You can use the Property Get statement to define a property procedure that gets the value of a property for a user-defined object. You will probably want to use the Property Let and Property Get statements together to create a property for an object. For example, you could define a Property Let procedure in a form module to set a new property for an object, and a Property Get procedure to return the value of that setting.

Usually you will define property procedures within a form or report module. Property procedures in a form or report module are public by default, and will be available to procedures in other modules in the current database. However, they will not be available to other databases.

You can use the property procedures to create new properties on a user-defined object. You can also use them to combine a set of properties for a custom form or report object. For example, you could create a custom property called CustomFormType. Using the Property Get statement, you could construct the CustomFormType property procedures so that one setting would display a white form with scroll bars, navigation buttons, and caption "Type1", while another setting would display a blue form with neither scroll bars nor navigation buttons, and caption "Type2". Then you could set the CustomFormType property for new form objects that you create for this form. Using the Property Get statement, you could create a property procedure to return the value of this property once it has been set.


Property Let Statement (Microsoft Access)

You can use the Property Let statement to define a property procedure that assigns the value of a property for a user-defined object. You will probably want to use the Property Let and Property Get statements together to create a property for an object. For example, you could define a Property Let procedure in a form module to set a new property for an object, and a Property Get procedure to return the value of that setting.

Usually you will define property procedures within a form module or report module. Property procedures in a form or report module are public by default, and will be available to procedures in other modules in the current database. However, they will not be available to other databases.

You can use the property procedures to create new properties on a user-defined object. You can also use them to combine a set of properties for a custom form or report object. For example, you could create a custom property called CustomFormType. Using the Property Get statement, you could construct the CustomFormType property procedures so that one setting would display a white form with scroll bars, navigation buttons, and caption "Type1", while another setting would display a blue form with neither scroll bars nor navigation buttons, and caption "Type2". Then you could set the CustomFormType property for new form objects that you create for this form. Using the Property Get statement, you could create a property procedure to return the value of this property once it has been set.


Public Statement (Microsoft Access)

By default, module-level variables are private to that module. You must explicitly declare a variable as public with the Public statement.

In a standard module, module-level variables declared with the Public statement are available to all other procedures in all modules in all Microsoft Access databases. However, they are not visible in the Object Browser, and they are not available to any other applications other than Microsoft Access.

If a public variable is declared in a standard module that contains the Option Private statement, the variable is available to all procedures in the current database, but not to procedures in other databases.

A public variable declared in a form module or report module is visible to all procedures in the current database, but not to procedures in other databases. To refer to a public variable declared in a form or report module from another module, you must qualify the variable with the class name of the form or report.

For example, if you declare the public variable varPublic in the module of a form called OrderForm, you can refer to that variable from a standard module as follows.

Form_OrderForm.varPublic

Note   The Public statement can't be used in a form or report module to declare a fixed-length string or an array.


Sub Statement (Microsoft Access)

In Microsoft Access, a public Sub procedure in a standard module is available to all other procedures in the database and in all other Microsoft Access databases. However, it is not available to any other applications.

If a Sub procedure is declared as public within a private module, then the procedure is available to all other procedures in that database, but is not available to other Microsoft Access databases. Form modules and report modules are always private, as are standard modules that contain an Option Private Module statement.

If you declare a Sub procedure as private in any module, that procedure is available only to other procedures within the same module.

Note   If a Sub procedure is available to other Microsoft Access databases, it will be visible from those databases in the Object Browser.

When you create an event procedure for a form or report, Microsoft Access automatically inserts a code stub for a Sub procedure and precedes it with the Private keyword. For example, draw a command button on a new form, set its OnClick property to [Event Procedure], and click on the Build button to view the form's module. Microsoft Access inserts the following code for you in the module.

Private Sub Command0_Click

End Sub

You can then enter the code that you want to execute when that button's Click event occurs.


Type Statement (Microsoft Access)

In Microsoft Access, user-defined types in a standard module are public by default. To make a user-defined type in a standard module private, precede the Type statement with the Private keyword.

In a form module or report module, user-defined types are private and cannot be made public.


Property Let, Property Get Statement Example (Microsoft Access)

The following example shows how to use the Property Let and Property Get statements to create a custom property, CustomFormType, for a user-defined object. By setting the CustomFormType property, you apply a set of predefined characteristics to a user-defined form object. You can also read the property to determine its current setting.

To test this example, create a new form and save it as CustomFormTemplate. In the Declarations section of the form's module, enter the following code.

Public intCurrent As Integer

Const conType1 = 1

Const conType2 = 2

Enter the following procedures in the form module. The Property Let procedure defines the characteristics that will be applied to the form when the CustomFormType property is set. The Property Get procedure returns the current setting of the CustomFormType property.

Static Property Let CustomFormType(intCustomType As Integer)

Select Case intCustomType

Case conType1

With Me

.Section(0).BackColor = RGB(255, 255, 255)

.ScrollBars = 3

.NavigationButtons = True

.Caption = "Type1"

intCurrent = conType1

End With

Case conType2

With Me

.Section(0).BackColor = RGB(127, 127, 127)

.ScrollBars = 0

.NavigationButtons = False

.Caption = "Type2"

intCurrent = conType2

End With

Case Else

MsgBox "Error!"

End Select

End Property

Static Property Get CustomFormType() As Integer

Select Case intCurrent

Case conType1

CustomFormType = conType1

Case conType2

CustomFormType = conType2

Case Else

MsgBox "Error!"

End Select

End Property

' Define custom method to display form.

Sub Show()

Me.Visible = True

End Sub

Create a new instance of the CustomFormTemplate object in a standard module. Now you can set and read the value of the CustomFormType property just as you would any built-in property.

Sub SetFormProperty()

Dim frmObject as New Form_CustomFormTemplate

' Call Show method to display form.

frmObject.Show

' Set value of property.

frmObject.CustomFormType = 1

' Return value of property.

Debug.Print frmObject.CustomFormType

End Sub


User-defined type not defined (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.

In Microsoft Access, only form modules and report modules are class modules. Form and report class modules are private; that is, neither they nor any of their members are available from outside the current database.


Invalid use of New keyword (Microsoft Access)

In Microsoft Access, only form modules and report modules are class modules. You can use the New keyword to create a new instance of a class and assign it to an object variable.

To create a new instance of a form module called Form_Orders, you can use the New keyword as in the following example.

Dim frmOrdersObject As New Form_Orders


Constants, fixed-length strings, arrays, and Declare statements not allowed as Public members of class module (Microsoft Access)

In Microsoft Access, only form modules and report modules are class modules. You can't declare a public constant, fixed-length string, or Declare statement in a form or report module. They must be prefaced by the Private keyword.


Member already exists in this form (Microsoft Access)

In Microsoft Access, you cannot use an identifier in a form module and report module if that identifier has the same name as a method or property of the form or report. For example, since a form has a RecordSource property, a procedure such as the following in the form's module would cause an error.

Sub RecordSource()	' Generates an error.

.	' Some code.

.

.

End Sub


A module is not a valid type (Microsoft Access)

In Microsoft Access, only form modules and report modules are class modules. Make sure that you are not trying to create an instance of a standard module.


IsDate Function Example (Microsoft Access)

The following example prompts the user for a string value, uses the IsDate function to determine whether the string can be converted to a date, and then displays an appropriate message.

Sub CheckDate()

strDate = InputBox("Enter string to display as a date.")

' Test variable.

If IsDate(strDate) Then

' If string is date, format and display in dialog.

MsgBox "The date is: " & Format(DateValue(strDate), "Long Date")

Else

MsgBox "The value you entered is not a date."

End If

End Sub


IsEmpty Function Example (Microsoft Access)

The following example uses the IsEmpty function to determine whether a variable has been initialized. If it hasn't been initialized, a message prompts the user to enter the initial value.

Sub CheckInitialized()

' Declare variables.

Dim varTest As Variant, varInit As Variant

' Test variable.

If IsEmpty(varTest) Then

varInit = MsgBox("Variable uninitialized; initialize now?", _ 	vbOKCancel)

If varInit = vbOK Then

' If user clicked OK, initialize varTest.

varTest = InputBox("Please enter the value for the variable.")

' Display confirmation.

MsgBox "Variable 'varTest' initialized to " & varTest

End If

End If

End Sub


IsMissing Function Example (Microsoft Access)

The following example uses the IsMissing function to determine whether an argument has been passed to the function. If not, a message prompts the user to supply a value.

Sub RunQuery(Optional varQueryName As Variant)

Dim strGetQuery As String

' Determine if optional argument was passed.

If IsMissing(varQueryName) Then

' If argument was not passed, prompt for query to run.

varGetQuery = InputBox("Enter query name.")

DoCmd.OpenQuery varGetQuery

Else

' If argument was passed, run that query.

DoCmd.OpenQuery varQueryName

End If

End Sub


IsNull Function Example (Microsoft Access)

The following example uses the IsNull function to determine whether the value of a control object is Null. If it is, a message prompts the user to enter data. If the value is not Null, a message displays the value of the control object.

Sub ControlValue(ctlText As Control)

Dim strMsg As String, strCRLF As String

strCRLF = Chr(13) & Chr(10)

' Check that control is text box.

If ctlText.ControlType = acTextBox Then

' If value of control is Null, prompt for data.

If IsNull(ctlText.Value) Then

strMsg = "No data in the field '" & ctlText.Name & "'." _

& strCRLF & "Please enter data for this field now."

If MsgBox(strMsg, vbQuestion) = vbOK Then

Exit Function

End If

' If value is not Null, display value.

Else

MsgBox (ctlText.Value)

End If

End If

End Sub


IsObject Function Example (Microsoft Access)

The following example uses the IsObject function to check whether an argument passed to the function is an object.

Sub CheckObject(varAny As Variant)

Dim varX as Variant

If IsObject(varAny) Then

Set varX = varAny

Else

varX = varAny

End If

End Sub


TypeName Function Example (Microsoft Access)

The following example creates several object variables and passes them to the TypeName function.

Sub ObjectTypes()

Dim dbs As Database, tdf As TableDef

Dim fld As Field

' Get current database.

Set dbs = CurrentDb

' Return TableDef object pointing to Orders table.

Set tdf = dbs.TableDefs("Orders")

' Return Field object pointing to OrderDate field.

Set fld = tdf.Fields("OrderDate")

' Print value returned by TypeName for each object.

Debug.Print TypeName(dbs)

Debug.Print TypeName(tdf)

Debug.Print TypeName(fld)

End Sub


MsgBox Function Example (Microsoft Access)

The following example uses the MsgBox function to create a formatted error message in Microsoft Access. Note the use of the at sign (@) to denote the different sections of the string.

Sub CustomMessage()

Dim strMsg As String, strInput As String

' Initialize string.

strMsg = "Number outside range.@You entered a number that is " _

& "less than 1 or greater than 10.@Press OK to enter " _

& "the number again."

' Prompt user for input.

strInput = InputBox("Enter a number between 1 and 10.")

' Test value of user input.

Do While strInput < 0 or strInput > 10

If MsgBox (strMsg, vbOkCancel, "Error!") = vbOk Then

strInput = InputBox("Enter a number between 1 and 10.")

End If

Loop

' Display user's correct input.

MsgBox "You entered the number " & strInput

End Sub


InputBox Function Example (Microsoft Access)

The following example uses the InputBox function to return the user's name. Note that you can't open a Help file from a dialog box created by the InputBox function in Microsoft Access.

Sub Greeting()

Dim strInput As String, strMsg As String

strMsg = "Enter your name."

strInput = InputBox(strMsg, "User Info", , 2000, 2000)

MsgBox "Hello, " & strInput

End Sub


AddNew Method (Microsoft Access)

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.


Clone Method (Microsoft Access)

If you use the Bookmark property of a Recordset object in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.


Close Method (Microsoft Access)

If a Visual Basic procedure contains an object variable that represents the database currently open in Microsoft Access, using the Close method on that object will cause the variable to go out of scope. The Close method will not affect the database that is open in the Microsoft Access Database window.


CompactDatabase Method (Microsoft Access)

The CompactDatabase method won't completely convert a Microsoft Access database from one version to another. Only the data format is converted. Microsoft Access–defined objects, such as forms and reports, aren't converted.

Use the Convert Database command on the File menu to convert Microsoft Access databases from an earlier version to the current version.


CreateDatabase Method (Microsoft Access)

You can use the CreateDatabase method to create a new database from Visual Basic. The new database is immediately opened, appended to the Databases collection, and saved to disk. However, only the current database is open in the Microsoft Access Database window. To open a database that you have created with CreateDatabase in the Database window, you must click Open Database on the File menu.


CreateGroup Method (Microsoft Access)

Once you have created a new Group object and appended it to the Groups collection of a Workspace or User object, you can verify that the new group exists by examining the Name drop-down list box on the Groups tab of the User And Group Accounts dialog box. This dialog box is available by pointing to Security on the Tools menu and then clicking User And Group Accounts.


CreateProperty Method (Microsoft Access)

Microsoft Access defines a number of properties on data access objects. These properties are not automatically recognized by the Microsoft Jet database engine. In order to set or return values for a Microsoft Access–defined property in Visual Basic, you must specifically add the property to the Properties collection of the object to which it applies. You can do this by using the CreateProperty method to create the property and then appending it to the Properties collection.

A Microsoft Access–defined property is automatically added to the Properties collection when it is first set from the Microsoft Access window. If the property has already been set in this way, then you don't need to add it to the Properties collection.

When you write code to set a Microsoft Access–defined property, you should include an error-handling routine that creates a Property object representing that property and appends it to the Properties collection if it does not already exist in the collection.

When you refer to a Microsoft Access–defined property in Visual Basic, you must explicitly refer to the Properties collection. For example, you would refer to the AppTitle property in the following manner, once it exists within the Properties collection of a Database object representing the current database.

Dim dbs As Database

Set dbs = CurrentDb

dbs.Properties!AppTitle = "Northwind Traders"

Note   You need to create and append only the Microsoft Access properties that apply to data access objects. You can set other Microsoft Access properties in Visual Basic using the standard object.property syntax.


CreateUser Method (Microsoft Access)

Once you have created a new User object and appended it to the Users collection of a Workspace or Group object, you can verify that the new user exists by examining the Name drop-down list box on the Users tab of the User And Group Accounts dialog box. This dialog box is available by pointing to Security on the Tools menu and then clicking User And Group Accounts.


FieldSize Method (Microsoft Access)

The data access FieldSize method is different from the Microsoft Access FieldSize property, which is set in table Design view. The data access FieldSize method returns the number of bytes in a Field object of type Memo data type or OLE Object data type.

You set the Microsoft Access FieldSize property in order to limit the size of a field in a table. The FieldSize property is only available in table Design view. From Visual Basic, use the Size property to set the size of a field in a table.


FillCache Method (Microsoft Access)

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, the current record may be set to an incorrect record. The Option Compare Text statement denotes a text-based comparison method, as does the default setting for the Option Compare Database statement.


FindFirst, FindLast, FindNext, FindPrevious Methods (Microsoft Access)

When specifying criteria for the Find methods, you must be careful to reference fields and controls properly, and to construct the criteria string correctly. For more information, see the topics Specifying Criteria in Domain Functions and Using Quotes in Strings.

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

You should use the the month-day-year format when you search for fields containing dates, even if the Regional Settings of the Windows Control Panel is set to a different format; otherwise, the data may not be found. You can use the Visual Basic Format function to make this easier. The following code requests a date from the user and constructs a string on which to search.

dim dteAny As Date

dteAny = InputBox ("Please enter a date in month-day-year format.")

rstOrders.FindFirst "[OrderDate] > #" & Format(dteAny, "m/d/yy") & "#"


GetChunk Method (Microsoft Access)

After assigning the results of the GetChunk method to a string variable, you can use the Visual Basic Len function to determine the number of characters in the string. Or you can use the LenB function to determine the number of bytes used to represent that string. For more information, search the online index for "Len function".


Move Method (Microsoft Access)

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.


AddNew Method Example (Microsoft Access)

The following example creates a new record in an Employees table and saves the changes.

Sub AddNewRecord()

Dim dbs As Database, rst As Recordset

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Employees")

With rst

' Add new record to end of Recordset object.

.AddNew

!LastName = "Russell"	' Add data.

!FirstName = "Peter"

.Update	' Save changes.

End With

dbs.Close

End Sub


Append Method Example (Microsoft Access)

The following example defines a new Field object and appends it to the Fields collection of a TableDef object.

Sub NewField()

Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database variable that points to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Create new field in Employees table.

Set fld = tdf.CreateField("SocialSecurity#", dbText, 15)

' Append field to collection.

dbs.TableDefs!Employees.Fields.Append fld

End Sub


AppendChunk, GetChunk Methods Example (Microsoft Access)

The following example appends data to a Notes field in an Employees table. The Notes field is Memo data type. The procedure then returns the contents of the field using the GetChunk method, adds to the data, and appends the altered data back to the Notes field using the AppendChunk method.

Sub AddToMemo()

Dim dbs As Database, rst As Recordset

Dim fldNotes As Field, fldFirstName As Field

Dim lngSize As Long, strChunk As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Create table-type Recordset object.

Set rst = dbs.OpenRecordset("Employees")

Set fldNotes = rst.Fields!Notes

Set fldFirstName = rst.Fields!FirstName

rst.MoveLast	' Populate Recordset object.

rst.MoveFirst	' Move to first record.

Do Until rst.EOF

' Determine size of field in current record.

lngSize = fldNotes.FieldSize

If lngSize = 0 Then

MsgBox "No text in " & fldNotes.Name & " field!"

Exit Do

Else

' Return contents of Notes field.

strChunk = fldNotes.GetChunk(0, lngSize)

' Alter data.

strChunk = strChunk & "  " & fldFirstName.Value & _

" is a terrific employee!"

With rst

.Edit	' Enable editing.

.[Notes] = ""	' Initialize field.

.[Notes].AppendChunk strChunk	' Append altered data.

.Update	' Save changes.

.MoveNext	' Move to next record.

End With

End If

Loop

End Sub


BeginTrans, CommitTrans, Rollback Methods Example (Microsoft Access)

The following example changes the job title of all sales representatives in an Employees table. After the BeginTrans method starts a transaction that isolates all the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved using the Update method.

One or more table pages remain locked while the user decides whether or not to accept the changes. Consequently, this technique isn't recommended but only shown as an example.

Sub ChangeTitle()

Dim wsp As Workspace, dbs As Database, rst As Recordset

Dim strName As String, strMessage As String, strPrompt As String

strPrompt = "Change title to Account Executive?"

' Get default Workspace.

Set wsp = DBEngine.Workspaces(0)

' Get current database.

Set dbs = CurrentDb

' Create table-type Recordset object.

Set rst = dbs.OpenRecordset("Employees", dbOpenTable)

' Start of transaction.

wsp.BeginTrans

rst.MoveFirst

Do Until rst.EOF

If rst![Title] = "Sales Representative" Then

strName = rst![LastName] & ", " & rst![FirstName]

strMessage = "Employee: " & strName & vbCrLf & vbCrLf

If MsgBox(strMessage & strPrompt, vbQuestion + vbYesNo, _

"Change Job Title") = vbYes Then

' Enable editing.

rst.Edit

rst![Title] = "Account Executive"

' Save changes.

rst.Update

End If

End If

' Move to next record.

rst.MoveNext

Loop

If MsgBox("Save all changes?", vbQuestion + vbYesNo, _

" Save Changes") = vbYes Then

wsp.CommitTrans	' Commit changes.

Else

wsp.Rollback	' Undo changes.

End If

End Sub


CancelUpdate, Update Methods Example (Microsoft Access)

The following example creates a new record in an Employees table, enters values, and prompts the user to save the changes. If the user chooses not to save the changes, the Update method is canceled.

Sub NewRecord()

Dim dbs As Database, rst As Recordset

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Employees")

With rst

' Add new record to end of Recordset object.

.AddNew

.[LastName] = "Christopher"	' Add data.

.[FirstName] = "Kevin"

End With

' Prompt user to save changes.

If MsgBox("Save these changes?", vbYesNo) = vbNo Then

' If user chooses No, cancel changes.

rst.CancelUpdate

Else

' If user chooses Yes, save changes.

rst.Update

End If

dbs.Close

End Sub


Clone Method Example (Microsoft Access)

The following example creates a Recordset object using an SQL statement on an Employees table and then uses the Clone method to create a clone of the Recordset object so that bookmarks can be shared between the two objects. This technique is especially useful when you have to compare the results of a query from more than one point at the same time.

The following function creates a dynaset-type Recordset object on an Employees table, then creates a clone of that Recordset object. The function reads and stores in a string variable the value of the Bookmark property for the current record, in this case the second record in the original Recordset object. The Bookmark property of the duplicate Recordset object is set to this string, making the second record the current record in this Recordset object as well. The current values of the LastName field for both recordsets are identical, as you can see once they are printed in the Debug window.

Sub CreateClone()

Dim dbs As Database

Dim rstEmployees As Recordset, rstDuplicate As Recordset

Dim fldName As Field, strBook As String

' Return Database variable that points to current database.

Set dbs = CurrentDb

' Open dynaset-type Recordset object.

Set rstEmployees = dbs.OpenRecordset("SELECT * FROM Employees " & _

" ORDER by [LastName]")

' Clone Recordset object.

Set rstDuplicate = rstEmployees.Clone

Set fldName = rstEmployees.Fields!LastName

' Set current record.

rstEmployees.MoveFirst

' Move to second record.

rstEmployees.MoveNext

' Get Bookmark value and print current field value.

If rstEmployees.Bookmarkable Then

strBook = rstEmployees.Bookmark

Debug.Print fldName.value

Else

' If Recordset object does not support bookmarks, exit procedure.

Exit Sub

End If

' Set Bookmark property of clone to obtained value.

rstDuplicate.Bookmark = strBook

Debug.Print fldName.value

End Sub


Close Method Example (Microsoft Access)

The following example creates a Database object that points to the current database and opens a table-type Recordset object based on a Customers table in the database. It then uses the Close method on these object variables, which causes them to go out of scope and frees the memory resources they have been using.

Sub UseClose()

Dim dbs As Database, rst As Recordset

' Return Database object that represents current database.

Set dbs = CurrentDb

' Create table-type Recordset.

Set rst = dbs.OpenRecordset("Customers")

.

.

.

rst.Close	' Close recordset.

dbs.Close	' Close database.

End Sub


CopyQueryDef Method Example (Microsoft Access)

The following example uses the CopyQueryDef method to return a copy of a QueryDef object representing an Invoices query, and prints the SQL property of that QueryDef object.

Sub GetQueryDefCopy()

Dim dbs As Database, rst As Recordset

Dim qdfOriginal As QueryDef, qdfCopy As QueryDef

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Return QueryDef variable pointing to Invoices query.

Set qdfOriginal = dbs.QueryDefs!Invoices

' Open dynaset-type Recordset object.

Set rst = qdfOriginal.OpenRecordset

' Get copy of original QueryDef object.

Set qdfCopy = rst.CopyQueryDef

' Print value of SQL property for copy.

Debug.Print qdfCopy.SQL

End Sub


CreateDatabase Method Example (Microsoft Access)

The following example uses CreateDatabase to create a new, encrypted database named Newdb.mdb.

Sub NewDatabase()

Dim wspDefault As Workspace, dbsNew As Database

Set wspDefault = DBEngine.Workspaces(0)

' Create new, encrypted database.

Set dbsNew = wspDefault.CreateDatabase ("Newdb.mdb", _

dbLangGeneral, dbEncrypt)

' Create and append tables to new database.

.

.

.

dbsNew.Close

End Sub


CreateField Method Example (Microsoft Access)

The following example creates a new Field object and appends it to an Employees table.

Sub NewField()

Dim dbs As Database, tdf As TableDef

Dim fld As Field

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Create new Field object.

Set fld = tdf.CreateField("Phone", dbText)

' Set another property of field.

fld.Size = 25

' Append new field to Fields collection.

tdf.Fields.Append fld

End Sub


CreateIndex Method Example (Microsoft Access)

The following example creates a new Index object on an Employees table. The new index consists of two fields, LastName and FirstName.

Sub NewIndex()

Dim dbs As Database, tdf As TableDef

Dim idx As Index, rst As Recordset

Dim fldLastName As Field, fldFirstName As Field

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Return Index object that points to new index.

Set idx = tdf.CreateIndex("FullName")

' Create and append index fields.

Set fldLastName = idx.CreateField("LastName", dbText)

Set fldFirstName = idx.CreateField("FirstName", dbText)

idx.Fields.Append fldLastName

idx.Fields.Append fldFirstName

' Append Index object.

tdf.Indexes.Append idx

End Sub


CreateProperty Method Example (Microsoft Access)

The following example sets the Subject property. The Subject property is a database property that can also be set on the Summary tab of the Database Properties dialog box, available by clicking Database Properties on the File menu.

The Subject property applies to a data access object [image] the SummaryInfo Document object. However, it is defined by Microsoft Access, so the Microsoft Jet database engine does not automatically recognize it. Therefore, you must specifically add this property to the Properties collection of the Document object before you can set it. If the property does not exist in the Properties collection, Microsoft Access will generate an error when you attempt to set it.

In the following example, if Microsoft Access generates an error upon trying to set the Subject property, the error-handling routine creates the property and appends it to the Properties collection.

Sub SetAccessProperty()

Dim dbs As Database, ctr As Container, doc As Document

Dim prp As Property

' Enable error handling.

On Error GoTo ErrorHandler

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Return Container variable pointing to Databases container.

Set ctr = dbs.Containers!Databases

' Return Document variable pointing to SummaryInfo document.

Set doc = ctr.Documents!SummaryInfo

' Attempt to set Subject property.

doc.Properties!Subject = "Business Contacts"

Exit Sub

ErrorHandler:

' Check number of error that has occurred.

If Err.Number = 3270 Then

' Create Subject property and set its value.

Set prp = doc.CreateProperty("Subject", dbText, _

"Business Contacts")

' Append property.

doc.Properties.Append prp

' Resume main procedure.

Resume Next

Else

' If different error has occurred, display message.

MsgBox "Unknown error!", vbCritical

End If

End Sub


CreateQueryDef Method Example (Microsoft Access)

The following example creates a new QueryDef object, then opens the query in Datasheet view.

Sub NewQuery()

Dim dbs As Database, qdf As QueryDef, strSQL As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

strSQL = "SELECT * FROM Employees WHERE [HireDate] >= #1-1-95#"

Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)

DoCmd.OpenQuery qdf.Name

End Sub


CreateRelation Method Example (Microsoft Access)

The following example creates a new Relation object that defines a relationship between a Categories table and a Products table. The Categories table is the primary table in the relationship, and the Products table is the foreign table. The CategoryID field is the primary key in the Categories table, and a foreign key in the Products table.

To test this example in the Northwind database, click Relationships on the Tools menu, and delete the relationship between the Categories table and the Products table. Close the Relationships window, saving the current configuration at the prompt. Run the following procedure. Then view the Relationships window again to see the new relationship.

Sub NewRelation()

Dim dbs As Database, rel As Relation, fld As Field

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Create new Relation object and specify foreign table.

Set rel = dbs.CreateRelation("CategoryID", "Categories", "Products")

' Set attributes to enforce referential integrity.

rel.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

' Create field in relation.

Set fld = rel.CreateField("CategoryID")

' Specify field name in foreign table.

fld.ForeignName = "CategoryID"

' Append Field object to Fields collection of Relation object.

rel.Fields.Append fld

' Append Relation object to Relations collection.

dbs.Relations.Append rel

End Sub


CreateTableDef Method Example (Microsoft Access)

The following example creates a new TableDef object and appends it to the TableDefs collection of the current database.

Sub NewTable()

Dim dbs As Database, tdf As TableDef, fld As Field

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Return TableDef object variable that points to new table.

Set tdf = dbs.CreateTableDef("Contacts")

' Define new field in table.

Set fld = tdf.CreateField("ContactName", dbText, 40)

' Append Field object to Fields collection of TableDef object.

tdf.Fields.Append fld

' Append TableDef object to TableDefs collection of database.

dbs.TableDefs.Append tdf

End Sub


Delete Method Example (Microsoft Access)

The following example creates a field in a table and then deletes the field.

Sub DeleteField()

Dim dbs As Database, tdf As TableDef

Dim fldInitial As Field, fld As Field

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Orders

' Create new Field object, specifying name, type, and size.

Set fldInitial = tdf.CreateField("MiddleInitial", dbText, 2)

' Append new Field object.

tdf.Fields.Append fldInitial

' Enumerate through Fields collection to find new Field object.

For Each fld In tdf.Fields

If fld.Name = "MiddleInitial" Then

' Delete new Field object.

tdf.Fields.Delete fld.Name

MsgBox "Field deleted."

End If

Next fld

End Sub


Edit Method Example (Microsoft Access)

The following example opens a Recordset object and locates each record satisfying the search criteria for the Title field. The procedure then uses the Edit method to prepare the record for subsequent editing, changes the job title, and saves the change.

Sub ChangeTitle()

Dim dbs As Database, rst As Recordset

Dim strCriteria As String, strNewTitle As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Set search criteria.

strCriteria = "Title = 'Sales Representative'"

strNewTitle = "Account Executive"

' Create dynaset-type Recordset object.

Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)

' Find first occurrence.

rst.FindFirst strCriteria

' Loop until no matching records.

Do Until rst.NoMatch

With rst

.Edit	' Enable editing.

!Title = strNewTitle	' Change title.

.Update	' Save changes.

.FindNext strCriteria	' Find next occurrence.

End With

Loop

End Sub

Tip   Using an update query to alter data is more efficient. For example, you can use the following code to achieve the same results.

strSQL = "Update Employees Set Title = 'Account Executive' " & _

"WHERE Title = 'Sales Representative' "

dbs.Execute strSQL


Execute Method Example (Microsoft Access)

The following example executes an action query and prints the number of records affected.

Sub RecordsUpdated()

Dim dbs As Database, qdf As QueryDef

Dim strSQL As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

strSQL = "UPDATE Employees SET Title = " & _

"'Senior Sales Representative' " & _

"WHERE Title = 'Sales Representative';"

' Create new QueryDef.

Set qdf = dbs.CreateQueryDef("UpdateTitles", strSQL)

' Execute QueryDef.

qdf.Execute

Debug.Print qdf.RecordsAffected

End Sub


FieldSize Method Example (Microsoft Access)

The following example uses the FieldSize method to return the size in bytes of two fields in an Employees table. The Notes field is Memo data type and the Photo field is Binary data type.

Sub GetFieldSize()

Dim dbs As Database, rst As Recordset

Dim fldNotes As Field, fldPhoto As Field

Dim strSQL As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Construct SQL statement to return Notes and Photo fields.

strSQL = "SELECT Notes, Photo FROM Employees;"

' Create dynaset-type Recordset object.

Set rst = dbs.OpenRecordset(strSQL)

Set fldNotes = rst.Fields!Notes

Set fldPhoto = rst.Fields!Photo

' Move to first record.

rst.MoveFirst

Debug.Print "Size of Notes:"; "    "; "Size of Photo:"

' Print sizes of fields for each record in Recordset object.

Do Until rst.EOF

Debug.Print fldNotes.FieldSize; "        "; fldPhoto.FieldSize

rst.MoveNext

Loop

End Sub


FindFirst, FindLast, FindNext, FindPrevious Methods Example (Microsoft Access)

The following example creates a dynaset-type Recordset object and then uses the FindFirst method to locate the first record satisfying the specified criteria. The procedure then finds the remaining records that satisfy the criteria.

Sub FindRecord()

Dim dbs As Database, rst As Recordset

Dim strCriteria As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Define search criteria.

strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"

' Create a dynaset-type Recordset object based on Orders table.

Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)

' Find first matching record.

rst.FindFirst strCriteria

' Check if record is found.

If rst.NoMatch Then

MsgBox "No record found."

Else

' Find other matching records.

Do Until rst.NoMatch

Debug.Print rst!ShipCountry; "   "; rst!OrderDate

rst.FindNext strCriteria

Loop

End If

End Sub


GetRows Method Example (Microsoft Access)

The following example uses the GetRows method to return a two-dimensional array containing the first ten rows of data in a Recordset object.

Sub RowsArray()

Dim dbs As Database, rst As Recordset, strSQL As String

Dim varRecords As Variant, intI As Integer, intJ As Integer

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Build SQL statement that returns specified fields.

strSQL = "SELECT [FirstName], [LastName], [HireDate] " & _

"FROM [Employees]"

' Open dynaset-type Recordset object.

Set rst = dbs.OpenRecordset(strSQL)

' Return first ten rows into array.

varRecords = rst.GetRows(10)

' Find upper bound of second dimension.

For intI = 0 To UBound(varRecords, 2)

Debug.Print

' Find upper bound of first dimension.

For intJ = 0 To UBound(varRecords, 1)

' Print data from each row in array.

Debug.Print varRecords(intJ, intI)

Next intJ

Next intI

End Sub


Move Method Example (Microsoft Access)

The following example uses the Move method to move forward two rows in a Recordset object.

Sub MoveForward()

Dim dbs As Database, rst As Recordset

Dim strCriteria As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

' Open dynaset-type Recordset object.

Set rst = dbs.OpenRecordset("SELECT * FROM Orders " & _

"ORDER BY [ShipCountry];")

rst.MoveFirst

' Check number of records in Recordset object.

If rst.RecordCount > 2 Then

' Move forward two rows.

rst.Move 2

Debug.Print rst!ShipCountry

End If

End Sub


MoveFirst, MoveLast, MoveNext, MovePrevious Methods Example (Microsoft Access)

The following example uses the MoveLast method to populate the Recordset object so the number of records can be counted. The MoveFirst method then moves the current record pointer to the first record in the Recordset object. The procedure prompts the user to enter a record number, then sets a bookmark for that record.

Sub MoveToRecord()

Dim dbs As Database, rst As Recordset, intI As Integer

Dim strNumber As String, strBookmark As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Orders")

' Populate Recordset object.

rst.MoveLast

' Return to first record.

rst.MoveFirst

strNumber = InputBox("Please enter record number.")

' Check that number is within Recordset object.

If strNumber <= rst.RecordCount And rst.Bookmarkable Then

For intI = 1 To strNumber

rst.MoveNext

Next intI

strBookmark = rst.Bookmark

End If

End Sub


OpenDatabase Method Example (Microsoft Access)

The following example returns a Database variable that points to the current database. Then it opens a different database called Another.mdb, using the OpenDatabase method. The procedure then enumerates all TableDef objects in both databases.

Sub OpenAnother()

Dim dbs as Database, dbsAnother as Database

Dim tdf as TableDef

' Return Database variable representing current database.

Set dbs = CurrentDb

' Return Database variable representing Another.mdb.

Set dbsAnother = DBEngine.Workspaces(0).OpenDatabase("Another.mdb")

' Enumerate all TableDef objects in each database.

For Each tdf in dbs.TableDefs

Debug.Print tdf.Name

Next tdf

For Each tdf in dbsAnother.TableDefs

Debug.Print tdf.Name

Next tdf

End Sub


OpenRecordset Method Example (Microsoft Access)

The following example opens a dynaset-type Recordset object and prints the number of records in the Recordset object.

Sub UKOrders()

Dim dbs As Database, rst As Recordset

Dim strSQL As String

' Return Database variable pointing to current database.

Set dbs = CurrentDb

strSQL = "SELECT * FROM Orders WHERE [ShipCountry] = 'UK'"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast

Debug.Print rst.RecordCount

End Sub


Refresh Method Example (Microsoft Access)

The following example refreshes the Indexes collection of a TableDef object. In a database in a multiuser environment, you can use the Refresh method to show changes made by other users.

Sub RefreshIndex()

Dim dbs As Database, tdf As TableDef

Dim idx As Index, fld As Field

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

tdf.Indexes.Refresh

For Each idx In tdf.Indexes

Debug.Print idx.Name; ":"

For Each fld In idx.Fields

Debug.Print "   "; fld.Name

Next fld

Next idx

End Sub


Seek Method Example (Microsoft Access)

The following example creates a new Index object on an Employees table. The new index consists of two fields, LastName and FirstName. The procedure then uses the Seek method to find a specified record.

Sub NewIndex()

Dim dbs As Database, tdf As TableDef, idx As Index

Dim fldLastName As Field, fldFirstName As Field, rst As Recordset

' Return Database variable pointing to current database.

Set dbs = CurrentDb

Set tdf = dbs.TableDefs!Employees

' Return Index object that points to new index.

Set idx = tdf.CreateIndex("FullName")

' Create and append index fields.

Set fldLastName = idx.CreateField("LastName", dbText)

Set fldFirstName = idx.CreateField("FirstName", dbText)

idx.Fields.Append fldLastName

idx.Fields.Append fldFirstName

' Append Index object.

tdf.Indexes.Append idx

' Open table-type Recordset object.

Set rst = dbs.OpenRecordset("Employees")

' Set current index to new index.

rst.Index = idx.Name

' Specify record to find.

rst.Seek "=", "Fuller", "Andrew"

If rst.NoMatch Then

Debug.Print "Seek unsuccessful!"

Else

Debug.Print "Seek successful."

End If

End Sub


Field Object, Fields Collection (Microsoft Access)

In addition to the properties defined by the Microsoft Jet database engine, a Field object in the Fields collection of a QueryDef object or a TableDef object may also contain the following Microsoft Access application-defined properties.

For details on reading and setting these properties, search the online index for the individual properties and the Property object.

Properties:

Caption	DecimalPlaces

ColumnHidden	Description

ColumnOrder	Format

ColumnWidth	InputMask


Group Object, Groups Collection (Microsoft Access)

You can create Group objects to establish and enforce permissions for Microsoft Access database objects as well as for data access objects. For example, you can set security for forms, reports, macros, and modules.

A Group object has a Name property that you can use in setting permissions for a Container or Document object. For example, you can assign the value of a Group object's Name property to the UserName property of a Container or Document object. You can then set the Permissions property of the Container or Document object to establish permissions for the group of users defined by the UserName property. Or you can read the Permissions property to determine existing permissions for that group.


TableDef Object, TableDefs Collection (Microsoft Access)

In addition to the properties defined by the Microsoft Jet database engine, a TableDef object may also contain the following Microsoft Access application-defined properties. For details on reading and setting these properties, search the online index for the individual properties and the Property object.

DatasheetFontHeight

DatasheetFontItalic

DatasheetFontName

DatasheetFontUnderline

DatasheetFontWeight

Description

FrozenColumns

RowHeight

ShowGrid


User Object, Users Collection (Microsoft Access)

You can create User objects to establish and enforce permissions for Microsoft Access database objects as well as for data access objects. For example, you can set security for forms, reports, macros, and modules.

A User object has a Name property that you can use in setting permissions for a Container or Document object. For example, you can assign the value of a User object's Name property to the UserName property of a Container or Document object. You can then set the Permissions property of the Container or Document object to establish permissions for the user defined by the UserName property. Or you can read the Permissions property to determine existing permissions for that user.


AllPermissions Property (Microsoft Access)

Microsoft Access defines four types of Container objects in addition to those defined by the Microsoft Jet database engine. These include the Forms, Reports, Scripts, or Modules Container objects. Individual Form, Report, Script and Module Document objects belong to the Documents collections of these Container objects. For these Container and Document objects, you can also set the AllPermissions property to the following constants.

Constant	User or group can

acSecFrmRptReadDef	Open the form or report in Design view but not make any changes.

acSecFrmRptWriteDef	Modify or delete the form or report in Design view.

acSecFrmRptExecute	Open the form in Form view or Datasheet view; print or open the report in Sample Preview or Print Preview.

acSecMacReadDef	Open the Macro window and view a macro without making changes.

acSecMacWriteDef	Modify or delete the macro in the Macro window.

acSecModReadDef	Open the module but not make any changes.

acSecModWriteDef	Modify or delete the contents of a module.

acSecMacExecute	Run the macro.


Bookmark Property (Microsoft Access)

When you write Visual Basic code that uses the Bookmark property, you must include an Option Compare Binary statement in the Declarations section of the module. The Bookmark property sets and returns a bookmark, which is a Variant array of Byte data. The string comparison method for the module must therefore be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

Note   Don't confuse this property with the Microsoft Access Bookmark property, which applies to a Form object and stores a bookmark for a particular record in the table or query underlying the form. These two properties do not interfere with each other; you can have separate bookmarks on a form and on a Recordset object at the same time.


CacheSize, CacheStart Properties (Microsoft Access)

When you use the CacheStart property in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. The bookmark set or returned by the CacheStart property is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.


DefaultValue Property (Microsoft Access)

In Microsoft Access, if you set the data access DefaultValue property to GenUniqueID( ) to create an AutoNumber field, you must also set the Attributes property to dbAutoIncrement. If you don't set the Attributes property in this way, Microsoft Access won't assign the AutoNumber data type to the field, and you'll get an error when you try to view the table in Datasheet view.

The following code shows how you can create a field with the AutoNumber data type, given a TableDef object variable tdf and a Field object variable fld.

Set fld = tdf.CreateField("EmployeeID", dbLong)

fld.DefaultValue = "GenUniqueID()"

fld.Attributes = dbAutoIncrField

tdf.Fields.Append fld

If the DefaultValue property setting is an expression, it can't contain user-defined functions, Microsoft Access domain aggregate functions, SQL aggregate functions, the CurrentUser function, the Eval function, or references to queries, forms, or other Field objects.


Description Property (Microsoft Access)

The data access Description property applies to the Error object and returns a string associated with an error. You can set it only from Visual Basic.

Note   Don't confuse this property with the Microsoft Access Description property, which describes a table or query and its fields. This property applies to a TableDef object, a QueryDef object, or a Field object in the Fields collection of a TableDef or QueryDef object.


Filter Property (Microsoft Access)

The data access Filter property applies a filter to a Recordset object. You can set it only from Visual Basic.

Note   The Microsoft Access Filter property applies a filter to a form. You can set it in a form's property sheet in form Design view, in a macro, or from Visual Basic.


HelpContext, HelpFile Properties (Microsoft Access)

The data access HelpContext and HelpFile properties apply to the Error object. You can set it only from Visual Basic.

Note   Don't confuse these properties with the Microsoft Access HelpContextID and HelpFile properties. These properties are similar, but they apply only to forms, reports, and controls. You can set them from Visual Basic, in a macro, or in the property sheet within form Design view or report Design view.


Inherited Property (Microsoft Access)

Microsoft Access defines a number of properties which apply to data access objects. Because these properties are defined by Microsoft Access, the Microsoft Jet database engine doesn't recognize them automatically. To set one of these properties in Visual Basic, you must first create the property using the CreateProperty method and then append it to the Properties collection of the object. For more information, search the online index for the Property object topic.

When you create an object based on another object, the derived object inherits the properties of the original object. You can use the Inherited property to determine whether one of these properties was created for the object it is applied to, or if the property was inherited from another object.

For example, suppose you want to set the Microsoft Access DatasheetFontName property for a TableDef object. If you are setting this property for the first time, you will need to create a corresponding Property object and append it to the Properties collection of the TableDef object. The Inherited property of the new Property object returns False.

If you then create a new QueryDef object based on the table corresponding to the TableDef object, the DatasheetFontName property will be included in the Properties collection of the QueryDef object. The Inherited property of a Property object corresponding to this property returns True.


LastModified Property (Microsoft Access)

When you use the LastModified property in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. The bookmark returned by the LastModified property is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.


Permissions Property (Microsoft Access)

Microsoft Access defines four types of Container objects in addition to those defined by the Microsoft Jet database engine. These include the Forms, Reports, Scripts, or Modules Container objects. Individual Form, Report, Script and Module Document objects belong to the Documents collections of these Container objects. For these Container and Document objects, you can also set the Permissions property to the following constants.

Constant	User or group can

acSecFrmRptReadDef	Open the form or report in Design view but not make any changes.

acSecFrmRptWriteDef	Modify or delete the form or report in Design view.

acSecFrmRptExecute	Open the form in Form view or Datasheet view; print or open the report in Sample Preview or Print Preview.

acSecMacReadDef	Open the Macro window and view a macro without making changes.

acSecMacWriteDef	Modify or delete the macro in the Macro window.

acSecModReadDef	Open the module but not make any changes.

acSecModWriteDef	Modify or delete the contents of a module.

acSecMacExecute	Run the macro.


Primary Property (Microsoft Access)

In Microsoft Access, when you set a primary key for a table in table Design view, the primary key is automatically defined as the primary index.


Size Property (Microsoft Access)

The Size property is equivalent to the FieldSize property in table Design view. You can't set the FieldSize property in Visual Basic; you must use the Size property instead.

In Microsoft Access, the Memo data type field can contain up to 1.2 gigabytes of information. However, Microsoft Access only displays the first 32 kilobytes of data in a control on a form or report.


Source Property (Microsoft Access)

The data access Source property applies to the Error object and can be read only from Visual Basic.

Note   Don't confuse this property with the Microsoft Access Source property, which applies to a query and specifies the source connection string and source database for the query's input table or query.


Type Property (Microsoft Access)

In Microsoft Access, you can set the data type for a field in table Design view, and you can set the data type for a parameter in the Query Parameters dialog box. These actions are equivalent to setting the data access Type property for a Field object or a Parameter object from Visual Basic.

The following table lists the Type property constants and the corresponding Microsoft Access settings for field and parameter data types if you are setting them from table Design view or the Query Parameters dialog box.

When creating a table field with data type Number, set the FieldSize property to specify which of the six numeric data types the field will be. By default this property is set to Long Integer. The other settings are Byte, Integer, Single, Double, and ReplicationID.

Constant	Table field setting	Query parameter setting
dbBoolean	Yes/No	Yes/No
dbByte	Number (FieldSize = Byte)	Byte
dbCurrency	Currency	Currency
dbDate	Date/Time	Date/Time
dbDouble	Number (FieldSize = Double)	Double
dbGUID	Number or AutoNumber (FieldSize = ReplicationID)	Not supported
dbInteger	Number (FieldSize = Integer)	Integer
dbLong	Number (FieldSize = Long Integer)	Long Integer
	AutoNumber (FieldSize = Long Integer)	Not supported
dbLongBinary	OLE Object	OLE Object
	Not supported	Binary
dbMemo	Memo	Memo
dbSingle	Number (FieldSize = Single)	Single
dbText	Text	Text
Not supported	Not supported	Value
Note   The parameter data type Value doesn't correspond to a data type defined by the Microsoft Jet database engine. It corresponds to the SQL reserved word VALUE, which can be used to create a parameter query. In Microsoft Access or SQL queries, VALUE can be considered a valid synonym for the Visual Basic Variant data type.


ValidationRule Property (Microsoft Access)

Use the data access ValidationRule property to set validation rules for a Field, Recordset, or TableDef object from Visual Basic.

You can also set validation rules for a field or control in the Microsoft Access user interface. Set the Microsoft Access ValidationRule property for a field or a table in table Design view. Set the ValidationRule property of a control in the property sheet in form Design view.

In Microsoft Access, the string expression specified by the ValidationRule property of a Field object can't refer to user-defined functions, domain aggregate functions, SQL aggregate functions, the CurrentUser function, the Eval function, or queries.


SELECT Statement (SQL) (Microsoft Access)

In Microsoft Access, if you are working in SQL view in the Query window, field names (field1, field2) are used as column headings in Datasheet view. To display a different column heading for a column in the Datasheet, use the AS reserved word. If you use the AS reserved word, then the alias1, alias2 arguments provide the column names to use in displaying the retrieved data in Datasheet view. Using the AS clause is equivalent to setting the Alias property in the field list property sheet in query Design view.

The following example uses the title "Birthday" to head the column in the resulting datasheet.

SELECT [BirthDate] AS Birthday FROM Employees;

When using aggregate functions or queries that return ambiguous or duplicate field names, you must use the AS clause to provide an alternate name for the field. The following example creates the column heading "Head Count" in Datasheet view.

SELECT COUNT([EmployeeID])

AS [HeadCount] FROM Employees;

If you are working with data access objects in Visual Basic code, the field1, field2 arguments are used to name the Field objects in the Recordset object returned by the query. If you include the AS keyword, then the alias1, alias2 arguments provide the column headings to return as Field object names in the resulting Recordset object.


FROM Clause (SQL) (Microsoft Access)

If you include a query or table name in the FROM clause of a SQL statement, that query or table will be automatically added to the Query window.

Conversely, any table or query that you add to the Query window will be included in the FROM clause of the corresponding SQL statement.


GROUP BY Clause (SQL) (Microsoft Access)

Including a GROUP BY clause in an SQL statement is equivalent to creating a totals query in the Query window and setting the Total cell to Group By for the appropriate field.


WHERE Clause (SQL) (Microsoft Access)

In Microsoft Access, the conditions that you establish in a WHERE clause in SQL view are the same as those you might enter in the Criteria field in the query design grid. If you enter criteria in the query design grid, you can change to SQL view to see the WHERE clause.

Conversely, if you enter a WHERE clause in an SQL statement in SQL view, you can change to Design view to see the criteria in the query design grid, unless you are creating a union query. Union queries can only be viewed in SQL view.


ORDER BY Clause (SQL) (Microsoft Access)

In Microsoft Access, an ORDER BY clause sorts the displayed data on a specified field or fields in ascending or descending order. Using an ORDER BY clause is equivalent to selecting Ascending or Descending in a Sort cell in the query design grid.


ALL, DISTINCT, DISTINCTROW, TOP Predicates (SQL) (Microsoft Access)

In Microsoft Access, using DISTINCT is equivalent to setting the UniqueValues property to Yes on the query property sheet in query Design view.

Using DISTINCTROW is equivalent to setting the UniqueRecords property to Yes (the default value) in the query property sheet in query Design view.

Using TOP is equivalent to setting the TopValues property on the query property sheet in query Design view, or entering a value in the Top Values list box on the toolbar in the Query window.

Using the PERCENT reserved word is equivalent to using the percent sign (%) with the TopValues property or in the Top Values list box.


SQL Aggregate Functions (Microsoft Access)

The SQL aggregate functions are similar to the domain aggregate functions, but they are used in different contexts. In Microsoft Access, you can use the SQL aggregate functions in an SQL statement that you create in the SQL view of the Query window, or in an SQL statement within Visual Basic code. Conversely, domain aggregate functions can be called directly from Visual Basic code.

You can use both SQL aggregate and domain aggregate functions in query expressions in the query design grid. SQL aggregate functions are most useful in totals queries and crosstab queries.

You can also use both types of aggregate functions in a calculated control on a form or report.


Avg Function (SQL) (Microsoft Access)

In Microsoft Access, you can use the Avg function in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the Avg function in a calculated control on a form or report.

The Avg function is most useful in totals queries and crosstab queries. It functions the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

For example, suppose you have an Orders table that has both a Freight field and a ShipCity field. You can create a query that displays the average freight cost for orders sent to each city to which your company ships. Create a new totals query, and drag the ShipCity field to the query design grid. The Total cell beneath the ShipCity field should be set to Group By. Drag the Freight field to the query design grid, and set the Total cell beneath it to Avg. When you run the query, it will display the average freight cost by city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement.

SELECT DISTINCTROW Avg(Orders.Freight) AS AvgOfFreight, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.

Dim dbs As DatabAse, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW Avg(Orders.Freight) AS AvgOfFreight, _

Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"

Set rst = dbs.OpenRecordset(strSQL)

To use the Avg function in a calculated control, set the control's ControlSource property to an expression containing the Avg function. For example, to display the average freight cost for a set of orders in a text box, enter the following expression in the ControlSource property of the text box.

= Avg([Freight])

If you use the Avg function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.


Count Function (SQL) (Microsoft Access)

In Microsoft Access, you can use the Count function in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the Count function in a calculated control on a form or report.

The Count function is most useful in totals queries and crosstab queries. It functions the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

The fastest way to count all the records in a query is to use the Count(*) function. You can use the Count(*) function in a calculated field in a query.

For example, suppose you have an Orders table that has both an OrderID field and a ShipCity field. You can create a query that displays the number of orders sent to each city. Create a new totals query, and drag the ShipCity field to the query design grid. Set the Total cell beneath the ShipCity field to Group By.

Next, create a calculated field by typing the following expression into a new Field cell.

CountOfOrders: Count(*)

Then, set the Total cell beneath this field to Expression. When you run the query, it will display the number of orders sent to each city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement.

SELECT DISTINCTROW Count(*) AS CountOfOrders, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can achieve the same results by dragging the OrderID field to the query design grid, and choosing Count in the Total cell beneath it. This query will be slightly slower than the one that uses the Count(*) function. Note the differences between the following SQL statements.

SELECT DISTINCTROW Count(Orders.OrderID) AS CountOfOrderID, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.

Dim dbs As DatabAse, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW Count(*) AS CountOfOrders, Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"

Set rst = dbs.OpenRecordset(strSQL)

To use the Count function in a calculated control, set the control's ControlSource property to an expression containing the Count function. For example, to display the number of orders in a set of orders in a text box, enter the following expression in the ControlSource property of the text box.

= Count([OrderID])

If you use the Count function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.


First, Last Functions (SQL) (Microsoft Access)

In Microsoft Access, you can use the First and Last functions in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the First and Last functions in a calculated control on a form or report.

The First and Last functions are most useful in calculated controls on a report. For example, if you have an Order report that is grouped on a ShipCountry field and sorted on an OrderDate field, you can use the First and Last functions in calculated controls to show the range of earliest to latest order dates for each grouping. To group on the ShipCountry field, click on the Sorting And Grouping button on the Reports toolbar. Choose ShipCountry in the Field/Expression column, and set the GroupHeader and GroupFooter properties to Yes. In Design view, create two new text boxes in the ShipCountry footer, and set the ControlSource property for both to the following expressions.

= First([OrderDate])

= Last([OrderDate])

When you switch to Print Preview, you will see the first and last order dates for each group of orders.

Note   In previous versions of Microsoft Access, the First and Last functions returned the first and last record created, respectively. If you want to return the first or last record in a set of records in Microsoft Access for Windows 95, you should create a query sorted as either Ascending or Descending and set the TopValues property to 1. For more information, see the TopValues property topic. From Visual Basic, you can also create a sorted results set of the query, and use the MoveFirst or MoveLast method to return the first or last record in a set of records.


Min, Max Functions (SQL) (Microsoft Access)

In Microsoft Access, you can use the Min and Max functions in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the Min and Max functions in a calculated control on a form or report.

The Min and Max functions are most useful in totals queries and crosstab queries. They function the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

For example, suppose you have an Orders table that has both a Freight field and a ShipCity field. You can create a query that displays the minimum freight cost for orders sent to each city. Create a new totals query, and drag the ShipCity field to the query design grid. The Total cell beneath the ShipCity field should be set to Group By. Drag the Freight field to the query design grid, and set the Total cell beneath it to Min. When you run the query, it will display the minimum freight cost for orders sent to each city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement.

SELECT DISTINCTROW Min(Orders.Freight) AS MinOfFreight, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.

Dim dbs As DatabAse, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW Min(Orders.Freight) AS MinOfFreight, _

Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"

Set rst = dbs.OpenRecordset(strSQL)

To use the Min and Max functions in a calculated control, set the control's ControlSource property to an expression containing either of these functions. For example, to display the minimum freight cost for a set of orders in a text box, enter the following expression in the ControlSource property of the text box.

= Min([Freight])

If you use the Min or Max function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.


StDev, StDevP Functions (SQL) (Microsoft Access)

In Microsoft Access, you can use the StDev and StDevP functions in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the StDev and StDevP functions in a calculated control on a form or report.

The StDev and StDevP functions are most useful in totals queries and crosstab queries. They function the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

For example, suppose you have an Orders table that has both a Freight field and a ShipCity field. You can create a query that displays the standard deviation of freight cost for orders sent to each city. Create a new totals query, and drag the ShipCity field to the query design grid. The Total cell beneath the ShipCity field should be set to Group By. Drag the Freight field to the query design grid, and set the Total cell beneath it to StDev. When you run the query, it will display the standard deviation of freight cost for orders sent to each city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement.

SELECT DISTINCTROW StDev(Orders.Freight) AS StDevOfFreight, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.

Dim dbs As DatabAse, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW StDev(Orders.Freight) AS StDevOfFreight, _

Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"

Set rst = dbs.OpenRecordset(strSQL)

To use the StDev and StDevP functions in a calculated control, set the control's ControlSource property to an expression containing either of these functions. For example, to display the standard deviation of freight costs for a set of orders in a text box, enter the following expression in the ControlSource property of the text box.

= StDev([Freight])

If you use the StDev or StDevP function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.


Sum Function (SQL) (Microsoft Access)

In Microsoft Access, you can use the Sum function in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the Sum function in a calculated control on a form or report.

The Sum function is most useful in totals queries and crosstab queries. It functions the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

For example, suppose you have an Orders table that has both a Freight field and a ShipCity field. You can create a query that displays the sum of freight costs for orders sent to each city. Create a new totals query, and drag the ShipCity field to the query design grid. The Total cell beneath the ShipCity field should be set to Group By. Drag the Freight field to the query design grid, and set the Total cell beneath it to Sum. When you run the query, it will display the sum of freight costs by city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement.

SELECT DISTINCTROW Sum(Orders.Freight) AS SumOfFreight, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.

Dim dbs As DatabAse, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW Sum(Orders.Freight) AS SumOfFreight, _

Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"

Set rst = dbs.OpenRecordset(strSQL)

To use the Sum function in a calculated control, set the control's ControlSource property to an expression containing the Sum function. For example, to display the sum of freight costs for a set of orders in a text box, enter the following expression in the ControlSource property of the text box.

= Sum([Freight])

If you use the Sum function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.


Var, VarP Functions (SQL) (Microsoft Access)

In Microsoft Access, you can use the Var and VarP functions in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the Var and VarP functions in a calculated control on a form or report.

The Var and VarP functions are most useful in totals queries and crosstab queries. They function the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

For example, suppose you have an Orders table that has both a Freight field and a ShipCity field. You can create a query that displays the variance of freight cost for orders sent to each city. Create a new totals query, and drag the ShipCity field to the query design grid. The Total cell beneath the ShipCity field should be set to Group By. Drag the Freight field to the query design grid, and set the Total cell beneath it to Var. When you run the query, it will display the variance of freight cost for orders sent to each city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement.

SELECT DISTINCTROW Var(Orders.Freight) AS VarOfFreight, Orders.ShipCity

FROM Orders

GROUP BY Orders.ShipCity;

You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.

Dim dbs As DatabAse, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW Var(Orders.Freight) AS VarOfFreight, _

Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"

Set rst = dbs.OpenRecordset(strSQL)

To use the Var and VarP functions in a calculated control, set the control's ControlSource property to an expression containing either of these functions. For example, to display the variance across freight costs in a text box, enter the following expression in the ControlSource property of the text box.

= Var([Freight])

If you use the Var or VarP function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.


SQL Expressions (Microsoft Access)

You can use many Visual Basic functions in SQL strings while in Visual Basic code, in SQL view of the Query window, or in the query design grid.

For example, you can include Visual Basic functions in an SQL statement that you use to define a QueryDef object or a dynaset-type or snapshot-type Recordset object. In the Microsoft Access query design grid, you can include Visual Basic functions in criteria expressions, or in a calculated field expression.


SQL Expressions Example (Microsoft Access)

The following example creates a dynaset-type Recordset object from an SQL statement. The SQL statement includes the Year function in a WHERE clause to return only records for orders placed in 1995.

Sub Orders95()

Dim dbs As Database, rst As Recordset, strSQL As String

Dim fld As Field

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW OrderID, OrderDate " & _

"FROM Orders WHERE ((Year([OrderDate])=1995));"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveLast

Debug.Print rst.RecordCount

End Sub

The next example shows how you can include a Visual Basic function in an SQL statement in SQL view in the Query window. The following SQL statement defines a query that displays the ShipName field from an Orders table, calculates the number of characters in that field for each record using the Len function, and displays that calculation in another column.

SELECT DISTINCTROW ShipName, Len([ShipName]) AS LengthOfShipName

FROM Orders;

You can create the same query in the query design grid. Create a new query and add the Orders table. Drag the ShipName field onto the Field cell in the first column in the grid. In another Field cell, create a calculated field expression by entering the following expression.

LengthOfShipName: Len([ShipName])


Project contains too many procedure, variable, and constant names (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Option Private Module not permitted in Class module (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.

Microsoft Access form modules and report modules are class modules. You can't include an Option Private Module statement within a form or report module.


Choose Function (Microsoft Access)

The Choose function is most useful in a calculated control on a form or report. For example, you can use the Choose function to set the value of a control based on the value of another field. Set the ControlSource property of the control to an expression containing the Choose function.

= Choose([ShipVia], "Speedy", "United", "Federal")

In the preceding example, if ShipVia contains 1, the Choose function returns the first string, "Speedy". If it contains 2, the function returns the second string, "United", and so on. If ShipVia contains 0, the Choose function returns a Null.

The next example shows how you can ensure that a string is returned even if the field contains 0.

= Choose([ShipVia] + 1, "none", "Speedy", "United", "Federal")

Note   In a Visual Basic module, you may want to use the more full-featured Select Case statement to return a value from a set of several choices.


IIf Function (Microsoft Access)

The IIf function (immediate if) is most useful in a calculated control on a form or report.

You can use the IIf function to evaluate an expression and return either of two other values, depending on whether the expression evaluates to True (-1) or False (0). For example, you can use the IIf function to inspect a field on a form and determine whether its value is Null. If it is, you can have the function return an empty string. If the field has a value that is not Null, the function could return the field's contents. The following example checks the ShipCountry field in an Orders table.

= IIf(IsNull(Forms!Orders![ShipCountry]), "", Forms!Orders![ShipCountry])

In Visual Basic, the IIf function evaluates both truepart and falsepart, even though it returns only one of them. On a Microsoft Access form or report, however, the IIf function evaluates either truepart or falsepart, whichever is appropriate. Therefore, you need not be concerned about the undesirable side effects of evaluating both arguments if you use the IIf function in a calculated control, query expression, or macro.

Note   The Microsoft Access Nz function converts Null values to zero, a zero-length string, or another value that you specify. If your expression handles Null values, you may be able use the Nz function as an alternative to the IIf function.

The IIf function is most useful within expressions that are in a form or report, rather than in Visual Basic code in a module. In Visual Basic, the more full-featured If...Then...Else statement offers greater versatility.


Switch Function (Microsoft Access)

The Switch function is most useful in a calculated control on a form or report. For example, you can use the Switch function to set the value of a control based on the value of another field. Set the ControlSource property of the control to an expression containing the Switch function.

In the following example, the Switch function returns a string that is the name of a shipper based on the value of the Freight field. If the value of the Freight field is less than $25, the Switch function returns "Speedy"; if it is greater than or equal to $25 but less than or equal to $50, the Switch function returns "United"; if it is greater than $50, the Switch function returns "Federal".

= Switch([Freight] < 25, "Speedy", ([Freight] >= 25 and [Freight] <= 50), "United", [Freight] > 50, "Federal")

Note   In a Visual Basic module, you may want to use the more full-featured Select Case statement to return a value from a set of several choices.


Add Method (Microsoft Access)

Items added to a user-defined collection are automatically indexed. You can refer to an individual item by this index. For example, if you have a collection colThings that contains four objects, you can refer to the third item in the collection with the expression colThings(3).

Note   When you add items to a Collection object, they are automatically indexed beginning with the number 1. Therefore, when you enumerate a Collection object, keep in mind that the index begins at 1. This may be different from built-in collections, which usually are indexed beginning with 0.

When you add an object to a user-defined collection, you can specify a custom key for that object in addition to the automatic index. In subsequent references to the object, you can refer to the custom key. For example, you can add an object objMine with the following key.

colThings.Add Item := objMine, key := ("A")

Subsequently you can refer to this particular object in the collection either as colThings(A), or by its numeric index.


Collection Object (Microsoft Access)

In Microsoft Access, a Collection object can consist of a set of objects of any type. You can create a Collection object that consists of several objects of the same type or objects of different types.

The collection defined by a Collection object can contain any combination of Microsoft Access objects, data access objects, and objects from any other application that exposes its objects to Microsoft Access. You can also add a Collection object to a collection defined by a Collection object.

The collection can also contain user-defined objects that you create in Microsoft Access. For example, you can create user-defined objects with custom methods and properties from a form module or report module. Then you can add these objects to a collection defined by a Collection object, and manipulate them as a set.


Creating an Object Variable (Microsoft Access)

In Microsoft Access, you can declare an object variable as a Variant, as an Object, or as a specific object type. Specific object types include Microsoft Access objects, data access objects, Visual Basic Collection objects, user-defined objects, or OLE Automation objects. The following examples show different specific object types you might use.

Dim frmOrderForm As Form	  ' Declares Form object.

Dim tdfOrders As TableDef	  ' Declares TableDef object.

Dim colThings As New Collection	  ' New Collection object.

Dim frmObject As New Form_Customers  ' New user-defined object.

Dim appXL As Excel.Application	  ' OLE Automation object.

Note that if you use the New keyword in an object variable declaration, a new object is created and the variable is automatically pointed to it, so you don't need to use the Set statement.

You can use the Me keyword in a form or report module to refer to the form or report that is associated with that module. The Me keyword allows you to refer to the current form without giving its full reference.

For example, you might create the following procedure in a standard module to change the color of the Detail section of a form.

Sub ChangeFormColor(frmCurrent as Form)

frmCurrent.Section("Detail0").BackColor = RGB(Rnd * 256, _

Rnd * 256, Rnd * 256)

End Sub

You can call this procedure from a form module and pass the Me keyword to as an argument to the procedure. The Me keyword represents the form from which the procedure is called and to which it is applied.

For example, you might call the following procedure from the Open event of a form. Set the form's OnOpen property to [Event Procedure], and create the following Sub procedure in the form's module.

Private Sub Form_Open()

ChangeFormColor Me

End Sub

If the name of the form were OrderForm, then you could also call this procedure with the following statement.

ChangeFormColor Forms!OrderForm

One advantage to using the Me keyword is that you don't need to know the name of the form to reference it.

If the form is a user-defined object, then you can also use the Me keyword in the procedures that define that object's properties and methods. For example, suppose you wish to define a method called Hide to hide an instance of a user-defined object called OrdersObject. You could include the following procedure in the form's module.

Public Sub Hide()

Me.Visible = False

End Sub

To perform this method on an instance of the OrdersObject form object, you can call it from another module as shown in the following example.

Dim frmObject as New Form_OrdersObject

frmObject.Hide


Module Scope (Microsoft Access)

In Microsoft Access, if you declare a module-level identifier that has the same name as a procedure-level identifier in a procedure in that module, you must qualify the module-level variable with the name of that module. This applies to standard modules, form modules, and report modules.

For example, you might declare a variable varScope at the module level and at the procedure level in a standard module called Important Functions. You can then refer to the module-level variable by qualifying it as [Important Functions].varScope.

Form and report modules in Microsoft Access are class modules. Whenever you reference a form or report module, you must supply the full class name of the module. It is the name of the saved form or report, preceded by Form_ or Report_. For example, if your form is called Order Form, then the class name of the form module is Form_Order Form.

To refer to a module-level variable or procedure in a form or report module, preface it with the class name of the form or report. For example, if you have declared a module-level variable varOrders in the module of a form called Order Form, you would refer to the module-level variable as [Form_Order Form].varOrders.

In a standard module, once you initialize a module-level variable, the variable retains its value, whether or not code is running, until the variable is either assigned another value or reset. You can test this by declaring a module-level variable in a standard module, running a procedure to assign it a value, and observing its value in the Debug window once the procedure has finished running.

All module-level variables in a form or report module exist whenever the form is open in Form view, or the report is open in Print Preview. When you close the form or report or switch to a different view, these variables go out of scope.

If you create procedures with identical names in a form or report module and in a standard module, the procedure in the standard module is always called unless it is prefaced with the class name of the form or report.

Tip   Place procedures that you call frequently in a standard module rather than in a form or report module so they will only be loaded when you need them, rather than with the form or report. Also, they'll be easier to find in a standard module, and you won't have to preface the procedure with the class name of a form or report to call it.

Procedures are public by default, except for event procedures. Event procedures in a form or report module are automatically prefaced with the Private keyword.

Public identifiers in a standard module are available to a referencing database. However, only public procedures are visible in the Object Browser. You can access other identifiers in code in a referencing database, provided you know their names.

Form and report modules are private and are never available outside the current database.


OLE Automation (Microsoft Access)

In Microsoft Access, you can create an OLE Automation object either by using the CreateObject or GetObject functions, or by using the New keyword.

Microsoft Access supports OLE Automation in two different ways. Microsoft Access is an OLE Automation controller, meaning that you can access objects exposed by other applications and manipulate them from Microsoft Access. Microsoft Access is also an OLE Automation server. From another application that supports OLE Automation, you can manipulate Microsoft Access objects.

For example, if you are using Microsoft Access as an OLE Automation controller, you can use objects exposed by Microsoft Excel. From Microsoft Access, you can open a Microsoft Excel worksheet and perform calculations on your data. You do this by creating a new Microsoft Excel object from Microsoft Access and pointing an object variable to it. You can then set its properties and call its methods as you would for any object. You can create a new instance of the Microsoft Excel Application object in the following way.

Dim appXL as Object

Set appXL = CreateObject("Excel.Application")

If Microsoft Excel is already running, you can use the GetObject function to point an object variable to the current instance of the Microsoft Excel Application object.

Dim appXL As Object

Set appXL = GetObject( , "Excel.Application")

If you are using Microsoft Access as an OLE Automation Server, you can manipulate Microsoft Access objects from another application. For example, you can open a Microsoft Access form from Microsoft Excel and use it to view data in a Microsoft Excel workbook. To create a new Microsoft Access object from Microsoft Excel, use the CreateObject function.

Dim appAccess As Object

Set appAccess = CreateObject("Access.Application.7")

If Microsoft Access is already running, use the GetObject function to point an object variable to the current instance of the Microsoft Access Application object.

From some applications, you can also use the New keyword to create Microsoft Access objects directly, without using the CreateObject and GetObject functions. Once you have made a reference to Microsoft Access from the other application, you can create a new Microsoft Access object in the following manner.

Dim appAccess as New Access.Application

Check the other application's documentation to determine whether the application supports this syntax.


Resolving Naming Conflicts (Microsoft Access)

In Microsoft Access, if two standard modules contain public procedures with the same name, you can call the procedure from within the module without qualifying it. However, if you call the procedure from a third module, you must qualify it with the name of the module containing the procedure that you want to run. The same holds true for public variables with identical names.

If both a standard module and either a form module or a report module contain a public procedure with the same name, an unqualified reference to the procedure will always call the one residing in the standard module. To call the procedure in the form or report module, you must qualify it with the class name of the form or report module. For example, if you have a function named CountOrders in the module of a form called OrderForm, you can call the function as follows.

Form_OrderForm.CountOrders

If you have established a reference to another Microsoft Access database in the References dialog box available by clicking References on the Tools menu, you can call a procedure in that referenced database without qualification, as long it has a unique name. If there is more than one referenced database containing a procedure with the same name, and you call the procedure without qualifying it, the match is made with the first declaration encountered by searching:

Directly referenced databases, in the order they appear in the References dialog box available by clicking References on the Tools menu.

The modules of each database.

If you reference a database that in turn references another database, you can call a procedure that resides in the indirectly referenced database. However, you must qualify the call with the name of the indirectly referenced database.

You cannot give an identifier in a form or report module the same name as a member of the form or report. For example, if you create a Sub procedure called Name, Microsoft Access will generate a compile-time error, because the form already has a Name property.


Scope and Visibility (Microsoft Access)

Microsoft Access has the following levels of scope and visibility.

Module Type	Scoping Level	Private	Public
Form/Report	Procedure level	All constants and variables are private.	Not applicable.
	Module level	All identifiers are private to the module in which they appear.	Variables, user-defined types, and Sub, Function, Property Let, Property Set, and Property Get procedures are all available to other procedures in the current database only. Public constants, fixed-length strings, and arrays are not allowed in form and report modules. You must preface Declare statements with the Private keyword.
Standard	Procedure level	All constants and variables are private.	Not applicable.
	Module level	All identifiers are private to the module in which they appear.	All identifiers are available to procedures in a referencing Microsoft Access database. None is available from any other application.
In Microsoft Access, public identifiers declared in a standard module are available to all other referencing databases. However, they are not available to other applications.

For example, suppose you create a procedure called NewProc in a new standard module called PublicProc in the Northwind sample database. From another database, you can create a reference to the Northwind database by clicking References on the Tools menu and selecting the Northwind.mdb from the list of available references. After you have established this reference, open the Object Browser and select the Northwind database in the Libraries/Databases box. You should see PublicProc in the Modules/Classes list, and NewProc in the Methods/Properties list.

You can call the NewProc procedure from the other database in one of several ways. You can simply call the procedure, if there are no other identifiers with the same name. You can qualify the procedure with the name of the module in which it resides. Or you can qualify the procedure with both the name of the module and the name of the database.

NewProc	 ' Call the procedure.

PublicProc.NewProc	 ' Qualify with the module name.

[Northwind.mdb].PublicProc.NewProc	 ' Qualify with both the module and database name.

If you declare a public variable, constant, Declare statement, or user-defined type in a standard module, you can also use that identifier in code in another database, once you have created a reference to the database in which the identifier is declared. However, Sub, Function, Property Let, Property Set, and Property Get procedures are the only identifiers that are visible in the Object Browser. In order to use any other identifier in code in a referencing database, you must already know its name. You can qualify a reference to an identifier in the same ways that you qualify a procedure call.

If you include the Option Private Module statement in a standard module, any public identifier will be available to procedures in the current database, but not in any other database.

Form and report modules are always private modules. If you declare a public identifier or procedure in a form or report module, it is available to all other modules in the database, but it is never available outside the current database.

Sub and Function procedures that you create in a form or report module are public by default, except for event procedures. When Microsoft Access creates a code stub for an event procedure, the Private keyword is automatically inserted before the event procedure.


Date Function Example (Microsoft Access)

You can use the Date function in a calculated control by setting the ControlSource property of the control as in the following example.

= Date()

You can also use the Date function to set criteria for a query. For example, if you have an Orders table that contains an OrderDate field, you can create a query on the Orders table to return all records that have an OrderDate between 4-1-95 and today's date. Enter the following in the Criteria cell under the OrderDate field.

Between #4-1-95# and Date()


DateSerial Function Example (Microsoft Access)

The following example uses the DateSerial function to return the number of days in a month. The function DaysInMonth requires a Date argument, since February will have an extra day in a leap year. If you pass a date literal to the DaysInMonth function, make sure it is delimited by number signs (#). For example, #2-1-95# or #February 1, 1995# are valid values for the Date argument. You can also pass in a string, such as "2-1-95".

This example uses the Year, Month, and Day functions to return the year, month, and day parts of the date passed in.

Sub DaysInMonth(dteAny As Date)

Dim intDays As Integer

' Add one month, subtract dates to find difference.

intDays = DateSerial(Year(dteAny), Month(dteAny) + 1, Day(dteAny)) _

- DateSerial(Year(dteAny), Month(dteAny), Day(dteAny))

Debug.Print intDays

End Sub

The next example uses named arguments to provide values to the DateSerial function. Since the arguments are named, their relative position is not important. Note the difference between using named arguments in the following example, and using the Year, Month, and Day functions in the preceding example.

Sub ConstructDate()

Debug.Print DateSerial(Day:=1, Month:=2, Year:=1995)

End Sub


Month Function Example (Microsoft Access)

The following example uses the Month function to set criteria in a query showing which orders were placed in December. Suppose you have an Orders table that includes an OrderID field and an OrderDate field. For example, you can create a new query on the Orders in the Query window, drag the OrderID field onto the query design grid from the Orders table, and enter the following expression in the Criteria cell under the OrderID field.

(Month([OrderDate]) = 12)


Now Function Example (Microsoft Access)

You can use the Now function in a calculated control to return the current system date and time. For example, you can create a text box and set its ControlSource property to the following expression.

= Now()


Time Function Example (Microsoft Access)

You can use the Time function in a calculated control to return the current system time. For example, you can create a text box and set its ControlSource property to the following expression.

= Time()


Timer Function Example (Microsoft Access)

You can use the Timer function to time operations and events in your Microsoft Access application by placing the Timer function immediately before and after the operation in your code that you wish to time. The following example uses the name of a query as its argument, then calculates how long it takes the query to run.

Sub QueryTimer (strQueryName As String)

Dim sngStart As Single, sngEnd As Single

Dim sngElapsed As Single

sngStart = Timer	' Get start time.

DoCmd.OpenQuery strQueryName, acNormal	' Run query.

sngEnd = Timer	' Get end time.

sngElapsed = Format(sngEnd - sngStart, "Fixed")	' Elapsed time.

MsgBox ("The query " & strQueryName & " took " & sngElapsed _

& " seconds to run.")

End Sub


Const Statement Example (Microsoft Access)

In Microsoft Access, you can declare only private constants in the Declarations section of a form module, report module, or standard module. However, you can declare both public and private constants in the General section of a standard module.

' In form or report module.

Const conCommission = .08

' In standard module.

Const conInterest = .054

Public Const conAddress = "8421 58th Avenue, College Park, MD"


Declare Statement Example (Microsoft Access)

In Microsoft Access, you can use the Declare statement at the module level of a standard module to declare a reference to an external procedure in a dynamic-link library (DLL). A Declare statement is public by default. In order to include a Declare statement in a form or report module, precede it with the Private keyword.

The following example references a procedure in a DLL, then calls that procedure from Visual Basic.

' In standard module.

Declare Sub MessageBeep Lib "User32" (ByVal intN As Integer)

' In form or report module.

Private Declare Sub MessageBeep Lib "User32" (ByVal intN As Integer)

' Once you have referenced procedure in DLL with Declare

' statement, you can call that procedure normally from code.

Sub SystemBeep()

Dim intB As Integer, intI As Integer

Dim sngStart, sngPause

sngPause = 2

intB = InputBox("How many beeps would you like to hear?")

For intI = 1 to intB

' Call system function.

MessageBeep(intB)

' Get start time.

sngStart = Timer

Do While Timer < sngStart + sngPause

' Pause for two seconds.

Loop

Next intI

End Sub


Option Private Statement Example (Microsoft Access)

The following example demonstrates the Option Private statement, which is used at the module level in a standard module to indicate that the entire module is private. If a module contains an Option Private Module statement, any public procedures can be called from other modules in the database, but not from another database.

Option Private Module	' Indicates that module is private.


Public Statement Example (Microsoft Access)

The following example uses the Public statement at the module level to explicitly declare variables as public. Public variables are available to all procedures in all modules within the current database. If they are declared in a standard module, then they are also available to a referencing database.

Public intNum As Integer

Public objApplication As Object


Ambiguous name detected (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.

You can create a reference to another Microsoft Access database by clicking References on the Tools menu, then selecting the appropriate type library from the list in the References dialog box. Once you have created a reference to another database, you can call procedures in the referenced database from the current database. If two public procedures in the current database and the referenced database have the same name, you should qualify the procedure in the referenced database with the name of the database and the module in which it resides.


Can't find project or library (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.

You can create a reference to another Microsoft Access database by clicking References on the Tools menu, then selecting the appropriate type library from the list in the References dialog box. Once you have created a reference to another database, you can call procedures in the referenced database from the current database.


Can't perform requested operation (Error 17) (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.

You can create a reference to another Microsoft Access database by clicking References on the Tools menu, then selecting the appropriate type library from the list in the References dialog box. Once you have created a reference to another database, you can call procedures in the referenced database from the current database.


Cyclic reference of projects not allowed (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.

You can create a reference to another Microsoft Access database by clicking References on the Tools menu, then selecting the appropriate type library from the list in the References dialog box. Once you have created a reference to another database, you can call procedures in the referenced database from the current database. If one database refers to another database, the second database cannot in turn refer to the first.


Duplicate declaration in current scope (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Expected procedure, not project or library (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Expected user-defined type, not project (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Expected variable or procedure, not project (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


File already exists (Error 58) (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


File not found (Error 53) (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Invalid data format (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Invalid qualifier (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


Invalid watch expression (Microsoft Access)

In Microsoft Access, a project is equivalent to a Microsoft Access database and its modules.


DoEvents Function (Microsoft Access)

In Microsoft Access, the DoEvents function is ignored if you use it in:

A user-defined function or a procedure that calculates a field in a query, form, or report.

A user-defined function that creates a list to fill a combo box, a list box, or an OLE object.

If you include the DoEvents function in any of these, Microsoft Access will not relinquish control to the operating system.


Compiler Constants (Microsoft Access)

Microsoft Access is a 32-bit development environment. The compiler constant Win32 will always evaluate to True (-1), while the constant Win16 will always evaluate to False (0).


MsgBox Function (Microsoft Access)

You can use the MsgBox function in Microsoft Access to create a formatted error message similar to built-in error messages displayed by Microsoft Access. This function permits you to supply a string in three sections for the prompt argument. You separate the sections with the at sign (@).

The following example displays a formatted dialog box with a sectioned string. The first section of text in the string is displayed as a bold heading. The second section is displayed as plain text beneath that heading. The third section is displayed as plain text beneath the heading "Solution", which the function provides for you.

MsgBox "Wrong button!@This button doesn't work.@Try another.", _

vbOKOnly + vbExclamation

Note   To display a message box without an icon in Microsoft Access, simply omit any constant that would denote an icon, or supply a value of zero for the buttons argument, as in the following example.

MsgBox "No icons included.", 0


InputBox Function (Microsoft Access)

You can't open a Help file from a dialog box created by the InputBox function in Microsoft Access. If you specify values for the helpfile and context arguments, they will be ignored. However, Microsoft Access won't generate an error.