Example
TestFunction()

Unlike user-defined function names used for event property settings, the function name in 
the Function Name argument doesn't begin with an equal sign (=).

Microsoft Access ignores the return value of the function.

Tip
To run a Sub procedure or event procedure written in Visual Basic, create a Function 
procedure that calls the Sub procedure or event procedure. Then use the RunCode action 
to run the Function procedure.

If you use the RunCode action to call a function, Microsoft Access looks for the function 
with the name specified by the Function Name argument in the standard modules for the 
database. However, when this action runs in response to clicking a menu command on a form 
or report or in response to an event on a form or report, Microsoft Access looks for the 
function first in the form's or report's class module and then in the standard modules. 
Microsoft Access doesn't search the class modules that appear on the Modules tab in the 
Database window for the function specified by the Function Name argument.

This action isn't available in Visual Basic. Run the desired Function procedure directly 
in Visual Basic.

Example
Dim varNum As Variant
If VarType(varNum) = vbCurrency Then
	Debug.Print "varNum contains Currency data."
Else
	Debug.Print "varNum doesn't contain Currency data."
End If

There are several categories of intrinsic constants. For a list of the intrinsic constants in 
a particular category, click the category below:

·	The action constants
·	The DAO constants
·	The event procedure constants
·	The Keycode constants
·	The miscellaneous constants
·	The RunCommand method constants (these are a subset of the action constants)
·	The security constants
·	The Visual Basic constants

·	The VarType function constants

System-Defined Constants

You can use the system-defined constants True, False, and Null anywhere in Microsoft Access. 
For example, you can use True in the following macro condition expression. The condition is 
met if the Visible property setting for the Employees form equals True.

Forms!Employees.Visible = True

You can use the constant Null anywhere in Microsoft Access. For example, you can use Null to 
set the DefaultValue property for a form control by using the following expression:
=Null

Example
The following example uses the CurrentDb function to return a Database object variable pointing to the current database. It then enumerates all the fields in the Employees table in that database.

Sub ListFields()
	Dim dbs As Database, tdf As TableDef, fld As Field

	' Return Database object variable pointing to current database.
	Set dbs = CurrentDb
	' Return TableDef object variable pointing to Employees table.
	Set tdf = dbs.TableDefs!Employees
	' Enumerate fields in Employees table.
	For Each fld In tdf.Fields
		Debug.Print fld.Name
	Next fld
End Sub

Examples
This example shows various uses of the Format function to format values using both 
named formats and user-defined formats. For the date separator (/), time separator (:), 
and AM/ PM literal, the actual formatted output displayed by your system depends on 
the locale settings on which the code is running. When times and dates are displayed 
in the development environment, the short time format and short date format of the 
code locale are used. When displayed by running code, the short time format and short 
date format of the system locale are used, which may differ from the code locale. For 
this example, English/U.S. is assumed.

MyTime and MyDate are displayed in the development environment using current system 
short time setting and short date setting.

Dim MyTime, MyDate, MyStr
MyTime = #17:04:23#
MyDate = #January 27, 1993#

' Returns current system time in the system-defined long time format.
MyStr = Format(Time, "Long Time")

' Returns current system date in the system-defined long date format.
MyStr = Format(Date, "Long Date")

MyStr = Format(MyTime, "h:m:s")			' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM")		' Returns "05:04:23 PM".

MyStr = Format(MyDate, "dddd, mmm d yyyy") 	' Returns "Wednesday, Jan 27 1993".

' If format is not supplied, a string is returned.
MyStr = Format(23)	' Returns "23".

' User-defined formats.
MyStr = Format(5459.4, "##,##0.00")	' Returns "5,459.40".
MyStr = Format(334.9, "###0.00")	' Returns "334.90".
MyStr = Format(5, "0.00%")		' Returns "500.00%".

MyStr = Format("HELLO", "<")		' Returns "hello".
MyStr = Format("This is it", ">")	' Returns "THIS IS IT"

Example (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")

Example (Function)
This example uses the IIf function to evaluate the TestMe parameter of the CheckIt 
procedure and returns the word "Large" if the amount is greater than 1000; otherwise, 
it returns the word "Small".

Function CheckIt (TestMe As Integer)
	CheckIt = IIf(TestMe > 1000, "Large", "Small")
End Function

This statement writes a sentence that leaves out any null entries:

IIF (IsNull([Age]), "", "The age is " & [Age] & " years old,") & _
IIF (IsNull([DistillDate]), "",  " distilled in " &  [DistillDate])

Example
The following example evaluates a control on a form and returns one of two strings based 
on the control's value. If the value of the control is Null, the procedure uses the Nz 
function to convert a Null value to a zero-length string.

Sub CheckValue()
	Dim frm As Form, ctl As Control
	Dim varResult As Variant

	' Return Form object variable pointing to Orders form.
	Set frm = Forms!Orders
	' Return Control object variable pointing to ShipRegion.
	Set ctl = frm!ShipRegion
	' Choose result based on value of control.
	varResult = IIf(Nz(ctl.Value) = "", "No value", "Value is " & ctl.Value)
	' Display result.
	MsgBox varResult
End Sub

.

Examples
The following example uses the Function statement to declare the name, arguments, and 
code that form the body of a Function procedure. The second example uses the ParamArray 
keyword.  The last example uses hard-typed, initialized Optional arguments.

' The following user-defined function returns the square root of the
' argument passed to it.

Function CalculateSquareRoot(NumberArg As Double) As Double
	If NumberArg < 0 Then	' Evaluate argument.
		Exit Function	' Exit to calling procedure.
	Else
		CalculateSquareRoot = Sqr(NumberArg)	' Return square root.
	End If
End Function

Using the ParamArray keyword enables a function to accept a variable number of arguments. 
In the following definition, FirstArg is passed by value.

Function CalcSum(ByVal FirstArg As Integer, ParamArray OtherArgs())
Dim ReturnValue 
' If the function is invoked as follows:
ReturnValue = CalcSum(4, 3 ,2 ,1)
' Local variables are assigned the following values: FirstArg = 4,
' OtherArgs(1) = 3, OtherArgs(2) = 2, and so on, assuming default
' lowerbound for arrays = 1.

Optional arguments can now have default values and types other than Variant. 

' If a function's arguments are defined as follows:
Function MyFunc(MyStr As String, Optional MyArg1 As _ Integer = 5, Optional MyArg2 = "Dolly") 
Dim RetVal
' The function can be invoked as follows:
RetVal = MyFunc("Hello", 2, "World")	' All 3 arguments supplied.
RetVal = MyFunc("Test", , 5)	' Second argument omitted.
' Arguments one and three using named-arguments.
RetVal = MyFunc(MyStr:="Hello ", MyArg1:=7)

Example (CInt)
This example uses the CInt function to convert a value to an Integer.

Dim MyDouble, MyInt
MyDouble = 2345.5678	' MyDouble is a Double.
MyInt = CInt(MyDouble)	' MyInt contains 2346.

Example (CLng)
This example uses the CLng function to convert a value to a Long.

Dim MyVal1, MyVal2, MyLong1, MyLong2
MyVal1 = 25427.45: MyVal2 = 25427.55	' MyVal1, MyVal2 are Doubles.
MyLong1 = CLng(MyVal1)	' MyLong1 contains 25427.
MyLong2 = CLng(MyVal2)	' MyLong2 contains 25428.

Example (CSng)

This example uses the CSng function to convert a value to a Single.

Dim MyDouble1, MyDouble2, MySingle1, MySingle2
' MyDouble1, MyDouble2 are Doubles.
MyDouble1 = 75.3421115: MyDouble2 = 75.3421555
MySingle1 = CSng(MyDouble1)	' MySingle1 contains 75.34211.
MySingle2 = CSng(MyDouble2)	' MySingle2 contains 75.34216.

Example (CStr)
This example uses the CStr function to convert a numeric value to a String. 

Dim MyDouble, MyString
MyDouble = 437.324		' MyDouble is a Double.
MyString = CStr(MyDouble)	' MyString contains "437.324".

Example (CVar)
This example uses the CVar function to convert an expression to a Variant.

Dim MyInt, MyVar
 MyInt = 4534			' MyInt is an Integer.
 MyVar = CVar(MyInt & "000")	' MyVar contains the string
 				' 4534000.

Example
The following example uses the Add method to add one standard module to the VBComponents 
collection.

Application.VBE.VBProjects(1).VBComponents.Add(vbext_ct_StdModule)

Example
This example uses the Add method to add Inst objects (instances of a class called Class1 
containing a Public variable InstanceName) to a collection called MyClasses. To see how 
this works, insert a class module and declare a public variable called InstanceName at 
module level of Class1 (type Public InstanceName) to hold the names of each instance. 
Leave the default name as Class1. Copy and paste the following code into the Form_Load event 
procedure of a form module. 

Dim MyClasses As New Collection	' Create a Collection object.
Dim Num As Integer	' Counter for individualizing keys.
Dim Msg
Dim TheName	' Holder for names user enters.
Do
' Create a new instance of Class1.
	Dim Inst As New Class1
' Increment Num, then get a name.
	Num = Num + 1
	Msg = "Please enter a name for this object." & Chr(13) _
	 & "Press Cancel to see names in collection."
	TheName = InputBox(Msg, "Name the Collection Items")
' Put name in object instance.
	Inst.InstanceName = TheName

' If user entered name, add it to the collection.
	If Inst.InstanceName <> "" Then
' Add the named object to the collection.
		MyClasses.Add item := Inst, key := CStr(Num)
	End If
' Clear the current reference in preparation for next one.
	Set Inst = Nothing
Loop Until TheName = ""
For Each x In MyClasses
	MsgBox x.instancename, , "Instance Name"
Next

Example
The following function uses the AddFromString and AddFromFile methods to add a string 
and the contents of a text file to a standard module:

Function AddTextToModule(strModuleName As String,strFileName As String,strText As String) As Boolean
	Dim mdl As Module

	On Error GoTo Error_AddTextToModule
	DoCmd.OpenModule strModuleName
	Set mdl = Modules(strModuleName)
	mdl.AddFromFile strFileName
	mdl.AddFromString strText
	
	AddTextToModule = True

Exit_AddTextToModule:
	Exit Function

Error_AddTextToModule:
	MsgBox Err & ": " & Err.Description
	AddTextToModule = False
	Resume Exit_AddTextToModule

End Function

You could call the preceding function from a procedure such as the following. Create a 
text file called Functions.txt, add some Visual Basic procedures, and save it in the 
My Documents directory. Then paste both the preceding procedure and the following procedure 
into a new standard module in the Northwind sample database. Run the following procedure:

Sub AddFunctionsFromText()
	Dim strModuleName As String, strFileName As String
	Dim strText As String

	strModuleName = "Utility Functions"
	strFileName = "C:\My Documents\Functions.txt"
	strText = "Public intX As Integer" & vbCrLf _
		& "Const conPathName As String = " _
		& """C:\Program Files\Microsoft Office\Office\Samples\"""
	If AddTextToModule(strModuleName, strFileName, strText) = True Then
		Debug.Print "String and file contents added successfully."

Else
		Debug.Print "String and file contents: Unable to Add."
	End If
End Sub

The next example creates a new form and adds a string and the contents of the Functions.txt 
file to its module. Run the following procedure from a standard module:

Sub AddTextToFormModule()
	Dim frm As Form, mdl As Module

	Set frm = CreateForm
	Set mdl = frm.Module
	mdl.AddFromString "Public intY As Integer"
	mdl.AddFromFile "C:\My Documents\Functions.txt"
End Sub

Example (DAO)
This example uses either the Append method or the Delete method to modify the Fields 
collection of a TableDef. The AppendDeleteField procedure is required for this procedure 
to run.

Sub AppendX()

	Dim dbsNorthwind As Database
	Dim tdfEmployees As TableDef
	Dim fldLoop As Field

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set tdfEmployees = dbsNorthwind.TableDefs!Employees

' Add three new fields.
	AppendDeleteField tdfEmployees, "APPEND", _
					"E-mail", dbText, 50
	AppendDeleteField tdfEmployees, "APPEND", _
					"Http", dbText, 80
	AppendDeleteField tdfEmployees, "APPEND", _
					"Quota", dbInteger, 5

	Debug.Print "Fields after Append"

Debug.Print , "Type", "Size", "Name"

' Enumerate the Fields collection to show the new fields.
	For Each fldLoop In tdfEmployees.Fields
		Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
	Next fldLoop

' Delete the newly added fields.
	AppendDeleteField tdfEmployees, "DELETE", "E-mail"
	AppendDeleteField tdfEmployees, "DELETE", "Http"
	AppendDeleteField tdfEmployees, "DELETE", "Quota"

	Debug.Print "Fields after Delete"
	Debug.Print , "Type", "Size", "Name"

' Enumerate the Fields collection to show that the new 
' fields have been deleted.
	For Each fldLoop In tdfEmployees.Fields
		Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
	Next fldLoop

	dbsNorthwind.Close

End Sub

Sub AppendDeleteField(tdfTemp As TableDef, _
		      strCommand As String, strName As String, _
		      Optional varType, Optional varSize)

	With tdfTemp

' Check first to see if the TableDef object is updatable. 
' If it isn't, control is passed back to the calling procedure.
		If .Updatable = False Then
			MsgBox "TableDef not Updatable! " & _
				"Unable to complete task."
			Exit Sub
		End If

' Depending on the passed data, append or delete a field to the 
' Fields collection of the specified TableDef object.
		If strCommand = "APPEND" Then
			.Fields.Append .CreateField(strName, varType, varSize)
		Else
			If strCommand = "DELETE" Then .Fields.Delete strName
		End If

	End With

End Sub

Example (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 reference to current database.
	Set dbs = CurrentDb
	Set tdf = dbs.TableDefs!Employees
	
' Create new field in Employees table.
	Set fld = tdf.CreateField("SocialSecurity#", dbText, 11)
	
' Append field and refresh collection.
	tdf.Fields.Append fld
	tdf.Fields.Refresh
	Set dbs = Nothing
End Sub

Example (Excel)
This example creates a new database, Nwindex.mdb. The example attaches two tables from 
the C:\Program Files\Common Files\Microsoft Shared\MSquery folder to the database. (In 
Windows NT, the two tables are located in the C:\Windows\Msapps\Msquery folder.)

Dim nWindEx As Database, customerTable As TableDef, supplierTable As TableDef
Dim dataSource As String
dataSource = _
	  "dBASE IV;DATABASE=C:\Program Files\Common Files\Microsoft Shared\MSquery"
appPath = Application.Path
Set nWindEx = Workspaces(0).CreateDatabase(Application.Path _
	    & "\NWINDEX.MDB", dbLangGeneral)
Set customerTable = nWindEx.CreateTableDef("Customer")
customerTable.Connect = dataSource
customerTable.SourceTableName = "Customer"
nWindEx.TableDefs.Append customerTable

Set supplierTable = nWindEx.CreateTableDef("Supplier")
supplierTable.Connect = dataSource
supplierTable.SourceTableName = "Supplier"
nWindEx.TableDefs.Append supplierTable
MsgBox "The database " & nWindEx.Name & " has been created."
nWindEx.Close

Example
The following example uses the ApplyFilter method to display only records that contain 
the name King in the LastName field:

DoCmd.ApplyFilter , "LastName = 'King'"

.

Example (DAO)
This example uses the Close method on both Recordset and Database objects that have been 
opened. It also demonstrates how closing a Recordset will cause unsaved changes to be lost.

Sub CloseX()

	Dim dbsNorthwind As Database
	Dim rstEmployees As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

' Make changes to a record but close the recordset before saving the changes.
	With rstEmployees
		Debug.Print "Original data"
		Debug.Print "    Name - Extension"
		Debug.Print "    " & !FirstName & " " & _
				 !LastName & " - " & !Extension
		.Edit
		!Extension = "9999"
		.Close
	End With

' Reopen Recordset to show that the data hasn't changed.
	Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

	With rstEmployees
		Debug.Print "Data after Close"
		Debug.Print "    Name - Extension"
		Debug.Print "    " & !FirstName & " " & _
				 !LastName & " - " & !Extension
		.Close
	End With

	dbsNorthwind.Close

End Sub

Example (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. The 
procedure uses the Close method on the Recordset object variable to free the memory 
resources it has been using.  It uses the Set statement with the Nothing keyword to free 
resources used by the Database object variable.

You can also use the Close method of the Database object to close it and free memory.  
The Close method of the Database object doesn't actually close the database that's open 
in Microsoft Access; it only frees the resources used by the Database object variable.

Using an object's Close method and setting the object variable to Nothing are equivalent 
ways to free memory.

Sub UseClose()
	Dim dbs As Database, rst As Recordset

' Return reference to current database.
	Set dbs = CurrentDb
	
' Create table-type recordset.
	Set rst = dbs.OpenRecordset("Customers")
	.
	.
	.
	
' Close recordset to free memory.
	rst.Close
					
' Free memory used by object variable.
	Set dbs = Nothing			
End Sub

Example (Excel)
This example opens the Customer recordset of the Nwindex.mdb database, counts how many 
records are available, and enters this number on Sheet1.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase 
method.

Dim db As Database, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer")
Set resultsSheet = Sheets("Sheet1")
resultsSheet.Activate
With resultsSheet.Cells(1, 1)
	.Value = "Records in " & rs.Name & " table:"
	.Font.Bold = True
	.EntireColumn.AutoFit
End With
rs.MoveLast
resultsSheet.Cells(1, 2).Value = rs.RecordCount
rs.Close
db.Close

Example
The following example creates a new table with two new fields. One of the fields is an 
AutoNumber field. The procedure also makes this field the primary key in the table.

Sub NewTable()
	Dim dbs As Database
	Dim tdf As TableDef, fld1 As Field, fld2 As Field
	Dim idx As Index, fldIndex As Field

	' Return reference to current database.
	Set dbs = CurrentDb
	' Create new table with two fields.
	Set tdf = dbs.CreateTableDef("Contacts")
	Set fld1 = tdf.CreateField("ContactID", dbLong)
	fld1.Attributes = fld1.Attributes + dbAutoIncrField
	Set fld2 = tdf.CreateField("ContactName", dbText, 50)
	' Append fields.

tdf.Fields.Append fld1
	tdf.Fields.Append fld2
	' Create primary key index.
	Set idx = tdf.CreateIndex("PrimaryKey")
	Set fldIndex = idx.CreateField("ContactID", dbLong)
	' Append index fields.
	idx.Fields.Append fldIndex
	' Set Primary property.
	idx.Primary = True
	' Append index.
	tdf.Indexes.Append idx
	' Append TableDef object.
	dbs.TableDefs.Append tdf
	dbs.TableDefs.Refresh
	Set dbs = Nothing
End Sub

Example (DAO)
This example uses the CreateField method to create three Fields for a new TableDef. It then 
displays the properties of those Field objects that are automatically set by the CreateField 
method. (Properties whose values are empty at the time of Field creation are not shown.)

Sub CreateFieldX()

	Dim dbsNorthwind As Database
	Dim tdfNew As TableDef
	Dim fldLoop As Field
	Dim prpLoop As Property

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")

	' Create and append new Field objects for the new 
	' TableDef object.
	With tdfNew
		' The CreateField method will set a default Size
		' for a new Field object if one is not specified.
		.Fields.Append.CreateField("TextField", dbText)
		.Fields.Append.CreateField("IntegerField", dbInteger)
		.Fields.Append.CreateField("DateField", dbDate)
	End With

	dbsNorthwind.TableDefs.Append tdfNew

	Debug.Print "Properties of new Fields in " & tdfNew.Name

	' Enumerate Fields collection to show the properties of 
	' the new Field objects.
	For Each fldLoop In tdfNew.Fields
		Debug.Print "    " & fldLoop.Name

		For Each prpLoop In fldLoop.Properties
			' Properties that are invalid in the context of

' TableDefs will trigger an error if an attempt
			' is made to read their values.
			On Error Resume Next
			Debug.Print "        " & prpLoop.Name & " - " & _
				IIf(prpLoop = "", "[empty]", prpLoop)
			On Error GoTo 0
		Next prpLoop

	Next fldLoop

	' Delete new TableDef because this is a demonstration.
	dbsNorthwind.TableDefs.Delete tdfNew.Name
	dbsNorthwind.Close

End Sub

Example (DAO)
This example uses the CreateQueryDef method to create and execute both a temporary and 
a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()

	Dim dbsNorthwind As Database
	Dim qdfTemp As QueryDef
	Dim qdfNew As QueryDef

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	With dbsNorthwind
' Create temporary QueryDef.
		Set qdfTemp = .CreateQueryDef("", _
			"SELECT * FROM Employees")
' Open Recordset and print report.
		GetrstTemp qdfTemp
' Create permanent QueryDef.
		Set qdfNew = .CreateQueryDef("NewQueryDef", _
			"SELECT * FROM Categories")

' Open Recordset and print report.
		GetrstTemp qdfNew
' Delete new QueryDef because this is a demonstration.
		.QueryDefs.Delete qdfNew.Name
		.Close
	End With

End Sub

Function GetrstTemp(qdfTemp As QueryDef)

	Dim rstTemp As Recordset

	With qdfTemp
		Debug.Print .Name
		Debug.Print "    " & .SQL
' Open Recordset from QueryDef.
		Set rstTemp = .OpenRecordset(dbOpenSnapshot)

		With rstTemp
' Populate Recordset and print number of records.

			.MoveLast
			Debug.Print "    Number of records = " & _
				.RecordCount
			Debug.Print
			.Close
		End With

	End With

End Function

Example (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 reference to current database.
	Set dbs = CurrentDb
	strSQL = "SELECT * FROM Employees WHERE [HireDate] >= #1-1-93#"
	' Create new query.
	Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
	DoCmd.OpenQuery qdf.Name
	Set dbs = Nothing
End Sub

Example (Excel)
This example creates a new query based on the Customer recordset in the Nwindex.mdb 
database. The query selects a snapshot of all customers in the state of Washington 
and then copies it to Sheet1.

To create the Nwindex.mdb database, run the Microsoft Excel example for the 
CreateDatabase method.

Dim db As Database, qDef As QueryDef, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set qDef = db.CreateQueryDef("WA Region")
qDef.SQL = "SELECT * FROM Customer WHERE [Region] = 'WA';"
Set rs = db.OpenRecordset("WA Region")
numberOfRows = Sheets("Sheet1").Cells(1, 1).CopyFromRecordset(rs)
Sheets("Sheet1").Activate
MsgBox numberOfRows & " records have been copied."
rs.Close
db.Close

Example
This example uses the CreateWorkspace method to create both a Microsoft Jet workspace 
and an ODBCDirect workspace. It then lists the properties of both types of workspace.

Sub CreateWorkspaceX()

	Dim wrkODBC As Workspace
	Dim wrkJet As Workspace
	Dim wrkLoop As Workspace
	Dim prpLoop As Property

' Create an ODBCDirect workspace. 
' Until you create the Microsoft Jet workspace, the Microsoft 
' Jet database engine will not be loaded into memory.

	Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", _
				      "", dbUseODBC)
	Workspaces.Append wrkODBC

	DefaultType = dbUseJet
' Create an unnamed Workspace object of the type 

' specified by the DefaultType property of DBEngine 
' (dbUseJet).
	Set wrkJet = CreateWorkspace("", "admin", "")

' Enumerate Workspaces collection.
	Debug.Print "Workspace objects in Workspaces collection:"
	For Each wrkLoop In Workspaces
		Debug.Print "    " & wrkLoop.Name
	Next wrkLoop

	With wrkODBC
' Enumerate Properties collection of ODBCDirect 
' workspace.
		Debug.Print "Properties of " & .Name
		On Error Resume Next

		For Each prpLoop In .Properties
			Debug.Print "    " & prpLoop.Name & " = " & prpLoop
		Next prpLoop
		On Error GoTo 0
	End With

	With wrkJet
' Enumerate Properties collection of Microsoft Jet 
' workspace.
		Debug.Print "Properties of unnamed Microsoft Jet workspace"
		On Error Resume Next
		For Each prpLoop In .Properties
			Debug.Print "    " & prpLoop.Name & " = " & prpLoop
		Next prpLoop
		On Error GoTo 0
	End With

	wrkODBC.Close
	wrkJet.Close

End Sub

Example
The following example finds the first occurrence in the records of the name Smith in the 
current field. It doesn't find occurrences of smith or Smithson.

DoCmd.FindRecord "Smith",, True,, True

Example
The following example creates a new Microsoft Access database from another application 
through Automation, and then creates a new table in that database.

You can enter this code in a Visual Basic module in any application that can act as an 
ActiveX component. For example, you might run the following code from Microsoft Excel, 
Microsoft Visual Basic, or even Microsoft Access.

When the variable pointing to the Application object goes out of scope, the instance of 
Microsoft Access that it represents closes as well. Therefore, you should declare this 
variable at the module level.

' Include following in Declarations section of module.
Dim appAccess As Access.Application

Sub NewAccessDatabase()
	Dim dbs As Database, tdf As TableDef, fld As Field
	Dim strDB As String

	' Initialize string to database path.
	strDB = "C:\My Documents\Newdb.mdb"
	' Create new instance of Microsoft Access.
	Set appAccess = _
		CreateObject("Access.Application.8")
	' Open database in Microsoft Access window.
	appAccess.NewCurrentDatabase strDB

' Get Database object variable.
	Set dbs = appAccess.CurrentDb
	' Create new table.
	Set tdf = dbs.CreateTableDef("Contacts")
	' Create field in new table.
	Set fld = tdf. _
		CreateField("CompanyName", dbText, 40)
	' Append Field and TableDef objects.
	tdf.Fields.Append fld
	dbs.TableDefs.Append tdf
	Set appAccess = Nothing
End Sub

Note
From some applications, such as Microsoft Visual Basic, you can include the New keyword 
when declaring the Application object variable. This keyword automatically creates a new 
instance of Microsoft Access, without requiring you to use the CreateObject function. 
Check your application's documentation to determine whether it supports this syntax.

Example (DAO)
This example uses the OpenDatabase method to open one Microsoft Jet database and two 
Microsoft Jet-connected ODBC databases.

Sub OpenDatabaseX()

	Dim wrkJet As Workspace
	Dim dbsNorthwind As Database
	Dim dbsPubs As Database
	Dim dbsPubs2 As Database
	Dim dbsLoop As Database
	Dim prpLoop As Property

' Create Microsoft Jet Workspace object.
	Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database 
' for exclusive use.
	MsgBox "Opening Northwind..."
	Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb", True)

' Open read-only Database object based on information in 
' the connect string.
	MsgBox "Opening pubs..."
	Set dbsPubs = wrkJet.OpenDatabase("Publishers", _
					   dbDriverNoPrompt, True, _
					  "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

' Open read-only Database object by entering only the 
' missing information in the ODBC Driver Manager dialog 
' box.
	MsgBox "Opening second copy of pubs..."
	Set dbsPubs2 = wrkJet.OpenDatabase("Publishers", _
					    dbDriverCompleteRequired, True, _
					   "ODBC;DATABASE=pubs;DSN=Publishers;")

' Enumerate the Databases collection.
	For Each dbsLoop In wrkJet.Databases
		Debug.Print "Database properties for " & _
			dbsLoop.Name & ":"

		On Error Resume Next
' Enumerate the Properties collection of each Database object.
		For Each prpLoop In dbsLoop.Properties
			If prpLoop.Name = "Connection" Then
' Property actually returns a Connection object.

Debug.Print "    Connection[.Name] = " & _
					dbsLoop.Connection.Name
			Else
				Debug.Print "    " & prpLoop.Name & " = " & _
					prpLoop
			End If
		Next prpLoop
		On Error GoTo 0

	Next dbsLoop

	dbsNorthwind.Close
	dbsPubs.Close
	dbsPubs2.Close
	wrkJet.Close

End Sub

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

To try this example, create a new database called Another.mdb, close it, and place it in 
the same directory as the database from which you are running the code.

Sub OpenAnother()
	Dim wsp As Workspace
	Dim dbs As Database, dbsAnother As Database
	Dim tdf As TableDef
	
' Return reference to current database.
	Set dbs = CurrentDb
' Return reference to default workspace.
	Set wsp = DBEngine.Workspaces(0)
' Return reference to Another.mdb.
	Set dbsAnother = wsp.OpenDatabase("Another.mdb")
' Enumerate all TableDef objects in each database.
	Debug.Print dbs.Name & ":"
	For Each tdf in dbs.TableDefs
		Debug.Print tdf.Name
	Next tdf
	Debug.Print
	Debug.Print dbsAnother.Name & ":"
	For Each tdf in dbsAnother.TableDefs
		Debug.Print tdf.Name
	Next tdf
	Set dbs = Nothing
	Set dbsAnother = Nothing
End Sub

Example (Excel)
This example displays a custom dialog box that contains a list of all the databases with 
the file name extension .mdb that are located in the Microsoft Excel folder, and then it 
opens the database selected by the user.

Dim a(100), db As Database
i = 0
ChDrive "C"
ChDir Application.Path
a(i) = Dir("*.MDB")
If a(i) = "" Then
	MsgBox "You have no databases in the Microsoft Excel folder"
	Exit Sub
End If
Do
	i = i + 1
	a(i) = Dir()
Loop Until a(i) = ""
Set theDialog = DialogSheets.Add
Set list1 = theDialog.ListBoxes.Add(78, 42, 84, 80)
For counter = 0 To i - 1
	list1.AddItem a(counter)
Next
Application.ScreenUpdating = True
theDialog.Show
Set db = Workspaces(0).OpenDatabase(a(list1.Value - 1))

MsgBox "The " & db.Name & " database is now open"
' use database here
db.Close

Example
The following example opens Sales Totals Query in Datasheet view and enables the user to 
view but not to edit or add records:

DoCmd.OpenQuery "Sales Totals Query", , acReadOnly

Example
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 reference to current database.
	Set dbs = CurrentDb
	strSQL = "SELECT * FROM Orders WHERE [ShipCountry] = 'UK'"
	Set rst = dbs.OpenRecordset(strSQL)
	rst.MoveLast
	Debug.Print rst.RecordCount
	rst.Close
	Set dbs = Nothing
End Sub

Example
The following example opens the Employees table in Print Preview:

DoCmd.OpenTable "Employees", acViewPreview

Example
The example verifies that a particular member of the VBComponents collection is a module, 
and then it uses the Remove method to remove the module.

Debug.Print Application.VBE.ActiveVBProject.VBComponents(4).Name
Application.VBE.ActiveVBProject.VBComponents.Remove Application.VBE.ActiveVBProject.VBComponents(4)

Example
This example illustrates the use of the Remove method to remove objects from a 
Collection object, MyClasses. This code removes the object whose index is 1 on each 
iteration of the loop.

Dim Num, MyClasses

' Remove the first object each time through the loop until 
' there areno objects left in the collection.
For Num = 1 To MyClasses.Count	
	MyClasses.Remove 1	
Next Num

Example
This example shows how the Requery method can be used to refresh a query after underlying 
data has been changed.

Sub RequeryX()

	Dim dbsNorthwind As Database
	Dim qdfTemp As QueryDef
	Dim rstView As Recordset
	Dim rstChange As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
			"PARAMETERS ViewCountry Text; " & _
			"SELECT FirstName, LastName, Country FROM " & _
			"Employees WHERE Country = [ViewCountry] " & _
			"ORDER BY LastName")

	qdfTemp.Parameters!ViewCountry = "USA"
	Debug.Print "Data after initial query, " & _
		[ViewCountry] = USA"
	Set rstView = qdfTemp.OpenRecordset
	Do While Not rstView.EOF
		Debug.Print "    " & rstView!FirstName & " " & _
				   rstView!LastName & ", " & rstView!Country
		rstView.MoveNext
	Loop

' Change underlying data.
	Set rstChange = dbsNorthwind.OpenRecordset("Employees")
	rstChange.AddNew
	rstChange!FirstName = "Nina"
	rstChange!LastName = "Roberts"
	rstChange!Country = "USA"
	rstChange.Update

	rstView.Requery

Debug.Print "Requery after changing underlying data"
	Set rstView = qdfTemp.OpenRecordset
	Do While Not rstView.EOF
		Debug.Print "    " & rstView!FirstName & " " & _
			rstView!LastName & ", " & rstView!Country
		rstView.MoveNext
	Loop

' Restore original data because this is only a demonstration.
	rstChange.Bookmark = rstChange.LastModified
	rstChange.Delete
	rstChange.Close

	rstView.Close
	dbsNorthwind.Close

End Sub

This example shows how the Requery method can be used to refresh a query after the query 
parameters have been changed.

Sub RequeryX2()

	Dim dbsNorthwind As Database
	Dim qdfTemp As QueryDef
	Dim prmCountry As Parameter
	Dim rstView As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
			"PARAMETERS ViewCountry Text; " & _
			"SELECT FirstName, LastName, Country FROM " & _
			"Employees WHERE Country = [ViewCountry] " & _
			"ORDER BY LastName")
	Set prmCountry = qdfTemp.Parameters!ViewCountry

	qdfTemp.Parameters!ViewCountry = "USA"

	Debug.Print "Data after initial query, " & [ViewCountry] = USA"
	Set rstView = qdfTemp.OpenRecordset
	Do While Not rstView.EOF
	
		Debug.Print "    " & rstView!FirstName & " " & _
			     rstView!LastName & ", " & rstView!Country
			     
		rstView.MoveNext
	Loop

' Change query parameter.
	qdfTemp.Parameters!ViewCountry = "UK"
	
' QueryDef argument must be included so that the resulting Recordset 
' reflects the change in the query parameter.
	rstView.Requery qdfTemp

Debug.Print "Requery after changing parameter, " & _
		"[ViewCountry] = UK"
	Do While Not rstView.EOF
		Debug.Print "    " & rstView!FirstName & " " & _
			    rstView!LastName & ", " & rstView!Country
		rstView.MoveNext
	Loop

	rstView.Close
	dbsNorthwind.Close

End Sub

Example
Suppose you have defined a procedure named NewForm in a database with its ProjectName 
property set to "WizCode." The NewForm procedure takes a string argument. You can call 
NewForm in the following manner from Visual Basic:

Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase ("C:\My Documents\WizCode.mdb")
appAccess.Run "WizCode.NewForm", "Some String"

If another procedure with the same name may reside in a different database, qualify the 
procedure argument, as shown in the preceding example, with the name of the database in 
which the desired procedure resides.

You can also use the Run method to call a procedure in a referenced Microsoft Access 
database from another database. 

Microsoft Access ignores any value returned by a procedure called by the Run method.

Example
The following example runs a user-defined Sub procedure in a module in a Microsoft Access 
database from another application that acts as an Active X component.

To try this example, create a new database called WizCode.mdb and set its ProjectName 
property to WizCode. Open a new module in that database and enter the following code. 
Save the module, and close the database.

Sub Greeting(strName As String)
	MsgBox("Hello, " & strName)
End Sub

In Microsoft Excel or Visual Basic, add the reference: 
Tools menu -> References -> References dialog -> Microsoft Access 8.0 Object Library.
then run the following code from the same..

' Include in Declarations section of module.
Dim appAccess As Access.Application

Sub RunAccessSub()
' Create instance of Access Application object.
	Set appAccess = _
		CreateObject("Access.Application.8")
' Open WizCode database in Microsoft Access window.
	appAccess.OpenCurrentDatabase "C:\My Documents\WizCode.mdb", False
' Run Sub procedure.
	appAccess.Run "Greeting", "Joe"
	Set appAccess = Nothing
End Sub

Example
The following example uses the RunCommand method to open the Options dialog box (available 
by clicking Options on the Tools menu):

Function OpenOptionsDialog() As Boolean
	On Error GoTo Error_OpenOptionsDialog
	DoCmd.RunCommand acCmdOptions
	OpenOptionsDialog = True

Exit_OpenOptionsDialog:
	Exit Function

Error_OpenOptionsDialog:
	MsgBox Err & ": " & Err.Description
	OpenOptionsDialog = False
	Resume Exit_OpenOptionsDialog
End Function

Example
The following example updates the Employees table, changing each sales manager's title 
to Regional Sales Manager:

DoCmd.RunSQL "UPDATE Employees " & _
	"SET Employees.Title = 'Regional Sales Manager' " & _
	"WHERE Employees.Title = 'Sales Manager';"

Example
This example uses the Set statement to assign object references to variables. YourObject 
is assumed to be a valid object with a Text property.

Dim YourObject, MyObject, MyStr
Set MyObject = YourObject	' Assign object reference.
' MyObject and YourObject refer to the same object.
YourObject.Text = "Hello World"	' Initialize property.
MyStr = MyObject.Text	' Returns "Hello World".

' Discontinue association. MyObject no longer refers to YourObject.
Set MyObject = Nothing	' Release the object.

Examples
You enter the expression in the Field cell in the query design grid. The fields you 
reference in the expression are from the tables or queries in the current query. 

Expression
Description

CurrentCountry: IIf(IsNull([Country]), " ", [Country])
Uses the IIf and IsNull functions to display an empty string in the CurrentCountry field if 
the Country field is Null; otherwise, it displays the contents of the Country field.

LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), 
		"Check for a missing date", [RequiredDate] - [ShippedDate])
Uses the IIf and IsNull functions to display in the LeadTime field the message "Check for a 
missing date" if the value of either the RequiredDate or ShippedDate fields is Null; 
otherwise, it displays the difference.

SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales])
Displays in the SixMonthSales field the total of the values in the first- and second-quarter 
sales fields combined, using the Nz function to convert the Null values to zero first.


IIf(IsNull([UnitPrice]),0,[UnitPrice])
Changes a Null value to a zero (0) in the UnitPrice field.

Note
When you use an arithmetic operator (+, -, *, /) in an expression and the value of one of 
the fields in the expression is Null, the result of the entire expression will be Null. 
If some records in one of the fields you used in the expression might have a Null value, 
you can convert the Null value to zero by using the Nz function, as shown in the preceding 

Example
The following example prints some current property settings of the Application object, 
sets an option, and then quits the application, saving all objects:

Sub ApplicationInformation()
	' Print name and type of current object.
	Debug.Print Application.CurrentObjectName
	Debug.Print Application.CurrentObjectType
	' Set Hidden Objects option under Show on View tab of Options dialog box.
	Application.SetOption "Show Hidden Objects", True
	' Quit Microsoft Access, saving all objects.
	Application.Quit acSaveYes
End Sub

The next example shows how to use Microsoft Access as an ActiveX component. From Microsoft 
Excel, Visual Basic, or another application that acts as an ActiveX component, create a 
reference to Microsoft Access by clicking References on the Tools menu in the Module window. 
Select the check box next to Microsoft Access 8.0 Object Library. Then enter the following 
code in a Visual Basic module within that application and call the GetAccessData procedure.

The example passes a database name and report name to a procedure that creates a new 
instance of the Application class, opens the database, and prints the specified report.

' Declare object variable in declarations section of a module
	Dim appAccess As Access.Application

Sub GetAccessData()
	Dim strDB As String
	Dim strReportName As String

	' Initialize string to database path.
	strDB = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
	' Initialize string to Report name.
	strReportName = "Catalog"
	PrintAccessReport strDB, strReportName
End Sub

Sub PrintAccessReport(strDB As String, strReportName As String)

' Return reference to Microsoft Access Application object.
	Set appAccess = New Access.Application
	' Open database in Microsoft Access.
	appAccess.OpenCurrentDatabase strDB
	' Print report.
	appAccess.DoCmd.OpenReport strReportName
	MsgBox "Click OK when " & strReportName & _
		" is finished printing"
	appAccess.CloseCurrentDatabase
	Set appAccess = Nothing
End Sub

Example
This example creates a Collection object (MyClasses), and then creates a dialog box in 
which users can add objects to the collection. To see how this works, choose the Class 
Module command from the Insert menu and declare a public variable called InstanceName 
at module level of Class1 (type Public InstanceName) to hold the names of each instance. 
Leave the default name as Class1. Copy and paste the following code into the General 
section of another module, and then start it with the statement ClassNamer in another 
procedure. (This example only works with host applications that support classes.) 

Sub ClassNamer()
	Dim MyClasses As New Collection			' Create a Collection object.
	Dim Num	' Counter for individualizing keys.
	Dim Msg As String				' Variable to hold prompt string.
	Dim TheName, MyObject, NameList			' Variants to hold information.
	Do
		Dim Inst As New Class1			' Create a new instance of Class1.
		Num = Num + 1				' Increment Num, then get a name.
		Msg = "Please enter a name for this object." & Chr(13) _
		    & "Press Cancel to see names in collection."

		TheName = InputBox(Msg, "Name the Collection Items")
		Inst.InstanceName = TheName		' Put name in object instance.
		
' If user entered name, add it to the collection.
		If Inst.InstanceName <> "" Then
' Add the named object to the collection.
			MyClasses.Add item := Inst, key := CStr(Num)
		End If
		
' Clear the current reference in preparation for next one.
		Set Inst = Nothing
	Loop Until TheName = ""
	
' Create list of names.
	For Each MyObject In MyClasses
		NameList = NameList & MyObject.InstanceName & Chr(13)
	Next MyObject

' Display the list of names in a message box.
	MsgBox NameList, , "Instance Names In MyClasses Collection"

' Remove name from the collection. Since collections are reindexed
' automatically, remove the first member on each iteration.
	For Num = 1 To MyClasses.Count
		MyClasses.Remove 1
	Next
End Sub

Example
The following example opens a form in Form view and moves to a new record.

Sub ShowNewRecord()
	DoCmd.OpenForm "Employees", acNormal
	DoCmd.GoToRecord , , acNewRec
End Sub

For more information on the Microsoft Access action corresponding to a DoCmd method, 
search the Help index for the name of the action

Example
.. open a form in Form view and move to a new record.

Sub ShowNewRecord()
	DoCmd.OpenForm "Employees", acNormal
	DoCmd.GoToRecord , , acNewRec
End Sub

Example (DAO)
This example uses the CreateQueryDef method to create and execute both a temporary and 
a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()

	Dim dbsNorthwind As Database
	Dim qdfTemp As QueryDef
	Dim qdfNew As QueryDef

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	With dbsNorthwind
		' Create temporary QueryDef.
		Set qdfTemp = .CreateQueryDef("", _
			"SELECT * FROM Employees")
		' Open Recordset and print report.
		GetrstTemp qdfTemp
		' Create permanent QueryDef.
		Set qdfNew = .CreateQueryDef("NewQueryDef", _
			"SELECT * FROM Categories")

' Open Recordset and print report.
		GetrstTemp qdfNew
		' Delete new QueryDef because this is a demonstration.
		.QueryDefs.Delete qdfNew.Name
		.Close
	End With

End Sub

Function GetrstTemp(qdfTemp As QueryDef)

	Dim rstTemp As Recordset

	With qdfTemp
		Debug.Print .Name
		Debug.Print "    " & .SQL
		' Open Recordset from QueryDef.
		Set rstTemp = .OpenRecordset(dbOpenSnapshot)

		With rstTemp
			' Populate Recordset and print number of records.

.MoveLast
			Debug.Print "    Number of records = " & _
				.RecordCount
			Debug.Print
			.Close
		End With

	End With

End Function

Example (DAO with collections)
This example creates a new QueryDef object and appends it to the QueryDefs collection of 
the Northwind Database object. It then enumerates the QueryDefs collection and the Properties 
collection of the new QueryDef.

Sub QueryDefX()

	Dim dbsNorthwind As Database
	Dim qdfNew As QueryDef
	Dim qdfLoop As QueryDef
	Dim prpLoop As Property

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	' Create new QueryDef object. Because it has a 
	' name, it is automatically appended to the 
	' QueryDefs collection.
	Set qdfNew = dbsNorthwind.CreateQueryDef("NewQueryDef", _
			"SELECT * FROM Categories")

	With dbsNorthwind
		Debug.Print .QueryDefs.Count & _

" QueryDefs in " & .Name

		' Enumerate QueryDefs collection.
		For Each qdfLoop In .QueryDefs
			Debug.Print "    " & qdfLoop.Name
		Next qdfLoop

		With qdfNew
			Debug.Print "Properties of " & .Name

			' Enumerate Properties collection of new 
			' QueryDef object.
			For Each prpLoop In .Properties
				On Error Resume Next
				Debug.Print "    " & prpLoop.Name & " - " & _
					IIf(prpLoop = "", "[empty]", prpLoop)

On Error Goto 0
			Next prpLoop
		End With

		' Delete new QueryDef because this is a 
		' demonstration.
		.QueryDefs.Delete qdfNew.Name
		.Close
	End With

End Sub

Example (Access with collections)
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 reference 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 on or after 1-1-94.
	strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-94#;"
' Create new QueryDef object.
	Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
' Open query in Datasheet view.
	DoCmd.OpenQuery qdf.Name
	Set dbs = Nothing
End Sub

Example (DAO)
This example demonstrates Parameter objects and the Parameters collection by creating a 
temporary QueryDef and retrieving data based on changes made to the QueryDef object's 
Parameters. The ParametersChange procedure is required for this procedure to run.

Sub ParameterX()

	Dim dbsNorthwind As Database
	Dim qdfReport As QueryDef
	Dim prmBegin As Parameter
	Dim prmEnd As Parameter

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create temporary QueryDef object with two parameters.
	Set qdfReport = dbsNorthwind.CreateQueryDef("", _
		"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
		"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
		"FROM Orders WHERE ShippedDate BETWEEN " & _

"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
		"ORDER BY EmployeeID")
	Set prmBegin = qdfReport.Parameters!dteBegin
	Set prmEnd = qdfReport.Parameters!dteEnd

' Print report using specified parameter values.
	ParametersChange qdfReport, prmBegin, #1/1/95#, _
		prmEnd, #6/30/95#
	ParametersChange qdfReport, prmBegin, #7/1/95#, _
		prmEnd, #12/31/95#

	dbsNorthwind.Close

End Sub

Sub ParametersChange(qdfTemp As QueryDef, _
		     prmFirst As Parameter, dteFirst As Date, _
		     prmLast As Parameter, dteLast As Date)
		     
' Report function for ParameterX.
	Dim rstTemp As Recordset
	Dim fldLoop As Field

' Set parameter values and open recordset from temporary QueryDef object.
	prmFirst = dteFirst
	prmLast = dteLast
	Set rstTemp = _ 
		qdfTemp.OpenRecordset(dbOpenForwardOnly)
	Debug.Print "Period " & dteFirst & " to " & dteLast

' Enumerate recordset.
	Do While Not rstTemp.EOF

' Enumerate Fields collection of recordset.
		For Each fldLoop In rstTemp.Fields
			Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
		Next fldLoop

		Debug.Print
		rstTemp.MoveNext
	Loop

	rstTemp.Close

End Sub

Example (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 reference 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#

' Open recordset on QueryDef object.
	Set rst = qdf.OpenRecordset
	rst.MoveLast
	MsgBox "Query returned " & rst.RecordCount & " records."
	rst.Close
	Set dbs = Nothing
End Sub

Example
The following example creates a reference to a specified type library:

Function ReferenceFromFile(strFileName As String) As Boolean
	Dim ref As Reference

	On Error GoTo Error_ReferenceFromFile
	Set ref = References.AddFromFile(strFileName)
	ReferenceFromFile = True

Exit_ReferenceFromFile:
	Exit Function

Error_ReferenceFromFile:
	MsgBox Err & ": " & Err.Description
	ReferenceFromFile = False
	Resume Exit_ReferenceFromFile
End Function

You could call this function by using a procedure such as the following, which creates a 
reference to the calendar control:

Sub CreateCalendarReference()
	If ReferenceFromFile("C:\Windows\System\Mscal.ocx") = True Then
		MsgBox "Reference set successfully."
	Else
		MsgBox "Reference not set successfully."
	End If
End Sub

Example
This example creates a new Microsoft Jet Workspace object and a new ODBCDirect Workspace 
object and appends them to the Workspaces collection. It then enumerates the Workspaces 
collections and the Properties collection of each Workspace  object. See the methods and 
properties of the Workspace object or Workspaces collection for additional examples.

Sub WorkspaceX()

	Dim wrkNewJet As Workspace
	Dim wrkNewODBC As Workspace
	Dim wrkLoop As Workspace
	Dim prpLoop As Property

' Create a new Microsoft Jet workspace.
	Set wrkNewJet = CreateWorkspace("NewJetWorkspace", _
					"admin", "", dbUseJet)
	Workspaces.Append wrkNewJet

' Create a new ODBCDirect workspace.
	Set wrkNewODBC = CreateWorkspace("NewODBCWorkspace", _
					 "admin", "", dbUseODBC)
	Workspaces.Append wrkNewODBC

' Enumerate the Workspaces collection.
	For Each wrkLoop In Workspaces
		With wrkLoop
			Debug.Print "Properties of " & .Name
			
' Enumerate the Properties collection of the new Workspace object.
			For Each prpLoop In .Properties
				On Error Resume Next
				If prpLoop <> "" Then Debug.Print "    " & _
					prpLoop.Name & " = " & prpLoop
				On Error GoTo 0
			Next prpLoop
		End With
	Next wrkLoop

	wrkNewJet.Close

wrkNewODBC.Close

End Sub

Examples
The following three examples set the Format property by using a predefined format:

Me!Date.Format = "Medium Date"

Me!Time.Format = "Long Time"

Me!Registered.Format = "Yes/No"

The next example sets the Format property by using a custom format. This format 
displays a date as: Jan 1995.

Forms!Employees!HireDate.Format = "mmm yyyy"

The following example demonstrates a Visual Basic function that formats numeric data 
by using the Currency format and formats text data entirely in capital letters. The 
function is called from the OnLostFocus event of an unbound control named TaxRefund.

Function FormatValue() As Integer
	Dim varEnteredValue As Variant

	varEnteredValue = Forms!Survey!TaxRefund.Value
	If IsNumeric(varEnteredValue) = True Then
		Forms!Survey!TaxRefund.Format = "Currency"
	Else
		Forms!Survey!TaxRefund.Format = ">"
	End If
End Function

 

Example
to refer to the LastName field on the Employees form in the Northwind sample database 
from Microsoft Excel, you set a reference to the Northwind database and then use the 
following reference:

Dim strLstName As String
strLastName = Northwind.Application.Forms!Employees!LastName

You can create a reference to a Microsoft Access database from another application 
(eg, Excel) by selecting the project name (the Application object) from the 
application's Module window -> Tools menu -> References -> References dialog. 
Once you select the project name, it also appears in the Project/Library list in the 
Object Browser.

Example (DAO)
This example demonstrates the RecordCount property with different types of Recordsets 
before and after they're populated.

Sub RecordCountX()

	Dim dbsNorthwind As Database
	Dim rstEmployees As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	With dbsNorthwind
' Open table-type Recordset and show RecordCount property.
		Set rstEmployees = .OpenRecordset("Employees")
		
		Debug.Print "Table-type recordset from Employees table"
		Debug.Print "    RecordCount = " & rstEmployees.RecordCount
		
		rstEmployees.Close

' Open dynaset-type Recordset and show RecordCount property before 
' populating the Recordset.
		Set rstEmployees = .OpenRecordset("Employees", _
			dbOpenDynaset)
			
		Debug.Print "Dynaset-type recordset " & _
			    "from Employees table before MoveLast"
			    
		Debug.Print "    RecordCount = " & rstEmployees.RecordCount

' Show the RecordCount property after populating the Recordset.
		rstEmployees.MoveLast
		
		Debug.Print "Dynaset-type recordset " & _
			"from Employees table after MoveLast"

		Debug.Print "    RecordCount = " & rstEmployees.RecordCount
		
		rstEmployees.Close

' Open snapshot-type Recordset and show RecordCount property 
' before populating the Recordset.
		Set rstEmployees = .OpenRecordset("Employees", 	dbOpenSnapshot)
		Debug.Print "Snapshot-type recordset " & _
			    "from Employees table before MoveLast"
			    
		Debug.Print "    RecordCount = " & rstEmployees.RecordCount

' Show the RecordCount property after populating the Recordset.
		rstEmployees.MoveLast
		Debug.Print "Snapshot-type recordset " & _
			    "from Employees table after MoveLast"
			
		Debug.Print "    RecordCount = " & rstEmployees.RecordCount
		rstEmployees.Close

' Open forward-only-type Recordset and show RecordCount property 
' before populating the Recordset.
		Set rstEmployees = .OpenRecordset("Employees", _
			                           dbOpenForwardOnly)
						   
		Debug.Print "Forward-only-type recordset " & _
			    "from Employees table before MoveLast"
			    
		Debug.Print "    RecordCount = " & rstEmployees.RecordCount

' Show the RecordCount property after calling the MoveNext method.
		rstEmployees.MoveNext
		
		Debug.Print "Forward-only-type recordset " & _
			    "from Employees table after MoveNext"
			    
		Debug.Print "    RecordCount = " & rstEmployees.RecordCount
		
		rstEmployees.Close
		.Close
	End With

End Sub

Example (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, rst As Recordset

' Return reference to current database.
	Set dbs = CurrentDb
' Open table-type Recordset object.
	Set rst = dbs.OpenRecordset("Orders")
	Debug.Print rst.RecordCount
	rst.Close
	Set dbs = Nothing
End Sub

Example (Excel)
This example displays the number of records in the Customer recordset in the Nwindex.mdb 
database.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase 
method.

Dim db As Database, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer")
On Error GoTo errorHandler
rs.MoveLast
MsgBox "There are " & rs.RecordCount & " records in " & rs.Name
rs.Close
db.Close
	Exit Sub
errorHandler:
	MsgBox "There are no records in " & rs.Name
	rs.Close
	db.Close

Example
The following example uses the Text property to enable a Next button named btnNext whenever 
the user enters text into a text box named txtName. Anytime the text box is empty, the Next 
button is disabled.

Sub txtName_Change()
	btnNext.Enabled = Len(Me!txtName.Text & "")<>0
End Sub

Example (DAO)
This example demonstrates the Type property by returning the name of the constant 
corresponding to the value of the Type property of four different Recordsets. The 
RecordsetType function is required for this procedure to run.

Sub TypeX()

	Dim dbsNorthwind As Database
	Dim rstEmployees As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	' Default is dbOpenTable.
	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees")
	Debug.Print _
		"Table-type recordset (Employees table): " & _
		RecordsetType(rstEmployees.Type)
	rstEmployees.Close

	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees", _
		dbOpenDynaset)
	Debug.Print _

"Dynaset-type recordset (Employees table): " & _
		RecordsetType(rstEmployees.Type)
	rstEmployees.Close

	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees", _
		dbOpenSnapshot)
	Debug.Print _
		"Snapshot-type recordset (Employees table): " & _
		RecordsetType(rstEmployees.Type)
	rstEmployees.Close

	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees", _
		dbOpenForwardOnly)
	Debug.Print _
		"Forward-only-type recordset (Employees table): " & _

RecordsetType(rstEmployees.Type)
	rstEmployees.Close

	dbsNorthwind.Close

End Sub

Function RecordsetType(intType As Integer) As String

	Select Case intType
		Case dbOpenTable
			RecordsetType = "dbOpenTable"
		Case dbOpenDynaset
			RecordsetType = "dbOpenDynaset"
		Case dbOpenSnapshot
			RecordsetType = "dbOpenSnapshot"
		Case dbOpenForwardOnly
			RecordsetType = "dbOpenForwardOnly"
	End Select

End Function

This example demonstrates the Type property by returning the name of the constant 
corresponding to the value of the Type property of all the Field objects in the Employees 
table. The FieldType function is required for this procedure to run.

Sub TypeX2()

	Dim dbsNorthwind As Database
	Dim fldLoop As Field

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	Debug.Print "Fields in Employees TableDef:"
	Debug.Print "    Type - Name"

	' Enumerate Fields collection of Employees table.
	For Each fldLoop In _
		dbsNorthwind.TableDefs!Employees.Fields
		Debug.Print "        " & FieldType(fldLoop.Type) & _
			" - " & fldLoop.Name
	Next fldLoop

	dbsNorthwind.Close

End Sub

Function FieldType(intType As Integer) As String

	Select Case intType
		Case dbBoolean
			FieldType = "dbBoolean"
		Case dbByte
			FieldType = "dbByte"
		Case dbInteger
			FieldType = "dbInteger"
		Case dbLong
			FieldType = "dbLong"
		Case dbCurrency
			FieldType = "dbCurrency"
		Case dbSingle
			FieldType = "dbSingle"
		Case dbDouble
			FieldType = "dbDouble"
		Case dbDate
			FieldType = "dbDate"
		Case dbText

FieldType = "dbText"
		Case dbLongBinary
			FieldType = "dbLongBinary"
		Case dbMemo
			FieldType = "dbMemo"
		Case dbGUID
			FieldType = "dbGUID"
	End Select

End Function

This example demonstrates the Type property by returning the name of the constant 
corresponding to the value of the Type property of all the QueryDef objects in Northwind. 
The QueryDefType function is required for this procedure to run.

Sub TypeX3()

	Dim dbsNorthwind As Database
	Dim qdfLoop As QueryDef

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")

	Debug.Print "QueryDefs in Northwind Database:"
	Debug.Print "    Type - Name"

	' Enumerate QueryDefs collection of Northwind database.
	For Each qdfLoop In dbsNorthwind.QueryDefs
		Debug.Print "        " & _
			QueryDefType(qdfLoop.Type) & " - " & qdfLoop.Name
	Next qdfLoop

	dbsNorthwind.Close

End Sub

Function QueryDefType(intType As Integer) As String

Select Case intType
		Case dbQSelect
			QueryDefType = "dbQSelect"
		Case dbQAction
			QueryDefType = "dbQAction"
		Case dbQCrosstab
			QueryDefType = "dbQCrosstab"
		Case dbQDelete
			QueryDefType = "dbQDelete"
		Case dbQUpdate
			QueryDefType = "dbQUpdate"
		Case dbQAppend
			QueryDefType = "dbQAppend"
		Case dbQMakeTable
			QueryDefType = "dbQMakeTable"
		Case dbQDDL
			QueryDefType = "dbQDDL"
		Case dbQSQLPassThrough

QueryDefType = "dbQSQLPassThrough"
		Case dbQSetOperation
			QueryDefType = "dbQSetOperation"
		Case dbQSPTBulk
			QueryDefType = "dbQSPTBulk"
	End Select

End Function

Example (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 reference to current database.
	Set dbs = CurrentDb
' Return reference 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
	Set dbs = Nothing
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)

Example (Excel)
This example copies to Sheet1 all fields of the Double type from Orddtail.dbf, a dBASE IV 
table located in the C:\Program Files\Common Files\Microsoft Shared\MSquery folder. 
(In Windows NT, Orddtail.dbf is located in the C:\Windows\Msapps\Msquery folder.)

Dim db As Database, recordsToCopy As Recordset, tDef As Recordset
Dim fieldsToStore(1000), fileName As String
fileName = "ORDDTAIL.DBF"
Set db = _
Workspaces(0).OpenDatabase("C:\Program Files\Common Files\Microsoft Shared\MSquery", _
	False, False, "dBASE IV")
Set tDef = db.OpenRecordset(fileName)
n = 0
Sheets("Sheet1").Activate
For i = 0 To tDef.Fields.Count - 1
	If tDef.Fields(i).Type = dbDouble Then
		fieldsToStore(n) = tDef.fields(i).Name
		n = n + 1

End If
Next
If fieldsToStore(0) = "" Then
	MsgBox "There are no number fields in this table."
	Exit Sub
End If
For i = 0 To n - 1
	records = "SELECT " & "[" & fieldsToStore(i) & "]" _
		& " from " & db.Recordsets(fileName).Name & ";"
	Set recordsToCopy = db.OpenRecordset(records)
	With ActiveSheet.Cells(1, i + 1)
		.CopyFromRecordset recordsToCopy
		.ColumnWidth = recordsToCopy.fields(0).Size
	End With
Next
recordsToCopy.Close
tDef.Close

db.Close

Example
The following example shows how you can call one of two procedures, depending whether the 
Credit check box on the Customers form is selected or cleared.

Sub PaymentType()
	If Forms!Customers!Credit.Value = False Then
		ProcessCash
	ElseIf Forms!Customers!Credit.Value = True Then
		ProcessCredit
	End If
End Sub

Example (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 reference to current database.
	Set dbs = CurrentDb
	' Open recordset on Employees table.
	Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
	Debug.Print rst.Updatable
	rst.Close
	Set dbs = Nothing
End Sub

Example (DAO)
This example opens a snapshot-type Recordset and demonstrates its read-only characteristics.

Sub dbOpenSnapshotX()

	Dim dbsNorthwind As Database
	Dim rstEmployees As Recordset
	Dim prpLoop As Property

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees", _
		dbOpenSnapshot)

	With rstEmployees
		Debug.Print "Snapshot-type recordset: " & _
			.Name

		' Enumerate the Properties collection of the
		' snapshot-type Recordset object, trapping for
		' any properties whose values are invalid in 

' this context.
		For Each prpLoop In .Properties
			On Error Resume Next
			Debug.Print "    " & _
				prpLoop.Name & " = " & prpLoop
			On Error Goto 0
		Next prpLoop

		.Close
	End With

	dbsNorthwind.Close

End Sub

Example (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 reference to current database.
	Set dbs = CurrentDb
	' Construct SQL string.
	strSQL = "SELECT * FROM Employees WHERE HireDate <= #1-1-94#;"
	' Open snapshot-type Recordset object.
	Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
	Debug.Print rst.Updatable
	rst.Close
	Set dbs = Nothing
End Sub

Example (DAO)
This example opens a table-type Recordset, sets its Index property, and enumerates 
its records.

Sub dbOpenTableX()

	Dim dbsNorthwind As Database
	Dim rstEmployees As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	' dbOpenTable is default.
	Set rstEmployees = _
		dbsNorthwind.OpenRecordset("Employees")

	With rstEmployees
		Debug.Print "Table-type recordset: " & .Name

		' Use predefined index.
		.Index = "LastName"
		Debug.Print "    Index = " & .Index

		' Enumerate records.
		Do While Not .EOF
			Debug.Print "        " & !LastName & ", " & _

!FirstName
			.MoveNext
		Loop

		.Close
	End With

	dbsNorthwind.Close

End Sub

Example (Access)
The following example opens a table-type Recordset object, then finds a specified record:

Sub FindEmployee()
	Dim dbs As Database, tdf As TableDef
	Dim rst As Recordset, idx As Index
	Dim fldLastName As Field, fldFirstName As Field

	' Return reference to current database.
	Set dbs = CurrentDb
	' Return reference to Employees table.
	Set tdf = dbs.TableDefs!Employees
	' Create 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 failed."
	Else
		Debug.Print "Seek successful."
	End If
	rst.close
	Set dbs = Nothing
End Sub

Example (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and run 
the query.

Warning
These examples makes changes to the Northwind sample database. Before beginning, you may 
wish to make a backup copy of the sample database.

The following example creates a new table with two Text fields:

CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT);

The next example creates a new table with two Text fields, a Date/Time field, and a 
unique index made up of all three fields:

CREATE TABLE SecondTable (FirstName TEXT,
LastName TEXT, DateOfBirth DATETIME,
CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));

The following example creates a new table with two Text fields and an Integer Number field. 
The SSN field is the primary key.

CREATE TABLE ThirdTable (FirstName TEXT, LastName TEXT, SSN INTEGER
CONSTRAINT MyFieldConstraint PRIMARY KEY);

Example (DAO)
This example creates an index consisting of the fields Home Phone and Extension in the 
Employees table.

Sub CreateIndexX1()

	Dim dbs As Database

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Create the NewIndex index on the Employees table.
	dbs.Execute "CREATE INDEX NewIndex ON Employees " _
		& "(HomePhone, Extension);"

	dbs.Close

End Sub

This example creates an index on the Customers table using the CustomerID field. No two 
records can have the same data in the CustomerID field, and no Null values are allowed.

Sub CreateIndexX2()

	Dim dbs As Database

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Create a unique index, CustID, on the 
	' CustomerID field.
	dbs.Execute "CREATE UNIQUE INDEX CustID " _
		& "ON Customers (CustomerID) " _
		& "WITH DISALLOW NULL;"

	dbs.Close

End Sub

Example (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and run 
the query.

Warning
These examples makes changes to the Northwind sample database. Before beginning, you may 
wish to make a backup copy of the sample database.

The next example creates an index consisting of the fields HomePhone and Extension in 
the Employees table:

CREATE INDEX NewIndex ON Employees (HomePhone, Extension);

The following example creates an index on the Customers table with the CustomerID field. 
No two records can have the same data in the CustomerID field, and no Null values are allowed.

CREATE UNIQUE INDEX CustID ON Customers (CustomerID) WITH DISALLOW NULL;

To delete the index created in the preceding example, you must use the DROP statement. 
You can't delete the index from table Design view unless you also delete the relationship 
on the Customers table. The following statement deletes the new index by using the DROP 
statement:

DROP INDEX CustID ON Customers;

The following example creates an index on an ODBC linked table. The table's remote 
database is unaware of and unaffected by the new index. The following example won't 
work if you simply paste it into Microsoft Access; you first need to create an ODBC 
linked table called OrderDetailsODBC.

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);

Example (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and run 
the query.

Warning
These examples makes changes to the Northwind sample database. Before beginning, you may 
wish to make a backup copy of the sample database.

The following example creates a new table with two Text fields:

CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT);

The next example creates a new table with two Text fields, a Date/Time field, and a 
unique index made up of all three fields:

CREATE TABLE SecondTable (FirstName TEXT,
LastName TEXT, DateOfBirth DATETIME,
CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));

The following example creates a new table with two Text fields and an Integer Number 
field. The SSN field is the primary key.

CREATE TABLE ThirdTable (FirstName TEXT, LastName TEXT, SSN INTEGER
CONSTRAINT MyFieldConstraint PRIMARY KEY);

Example
Use In to determine which orders are shipped to a set of specified regions:

SELECT *
FROM Orders
WHERE ShipRegion In ('Avon','Glos','Som')

Example (DAO)
The following example uses the Orders table in the Northwind.mdb database to create a 
query that includes all orders shipped to Lancashire and Essex and the dates shipped.

This example calls the EnumFields procedure, which you can find in the SELECT statement 
example.

Sub InX()

	Dim dbs As Database, rst As Recordset

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Select records from the Orders table that
	' have a ShipRegion value of Lancashire or Essex.
	Set rst = dbs.OpenRecordset("SELECT " _
		& "CustomerID, ShippedDate FROM Orders " _
		& "WHERE ShipRegion In " _
		& "('Lancashire','Essex');")
	
	' Populate the Recordset.

rst.MoveLast
	
	' Call EnumFields to print the contents of
	' the Recordset.
	EnumFields rst, 12

	dbs.Close

End Sub

Example (Access)
To try the following example, create a copy of the Customers table and name it 
NewCustomers. This example selects all records in the New Customers table and 
adds them to the Customers table. When individual columns aren't designated, the 
SELECT table column names must match exactly those in the INSERT INTO table.

INSERT INTO Customers SELECT * FROM NewCustomers;

The next example creates a new record in the Employees table:

INSERT INTO Employees (FirstName,LastName, Title) VALUES ('Harry', 'Washington', 'Trainee');

The following example selects all trainees from an assumed Trainees table who were hired 
more than 30 days ago and adds their records to the Employees table.

INSERT INTO Employees SELECT Trainees.* FROM Trainees WHERE HireDate < Now() - 30;

You can use the INSERT INTO clause to back up information in your database before it is 
changed. For example, you can back up information in an Employees table immediately 
before a user makes any changes to the data in that table.

To try the following example, in the Database window copy the Employees table to a new 
table called EmployeeHistory by clicking the table, clicking Copy on the toolbar, 
then clicking Paste Under Paste Options in the Paste Table As dialog box, click 
Structure Only. This will copy the structure of the table only, without any data. Next 
paste the following SQL statement into a new query and save the query as BackUpQuery:

INSERT INTO EmployeesHistory (FirstName, LastName, Title)
VALUES (Forms!Employees!FirstName, Forms!Employees!Lastname,Forms!Employees!Title);

Open the Employees form in form Design view and set the form's BeforeUpdate property 
to [Event Procedure]. In the BeforeUpdate event procedure, enter the following code:

DoCmd.OpenQuery "BackUpQuery"

Now the existing data in the Employees table will be copied to the backup table each 
time a user changes it.

Example (DAO)
This example selects all records in a hypothetical New Customers table and adds them 
to the Customers table. When individual columns are not designated, the SELECT table 
column names must match exactly those in the INSERT INTO table.

Sub InsertIntoX1()

	Dim dbs As Database

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")
	
	' Select all records in the New Customers table 
	' and add them to the Customers table.
	dbs.Execute " INSERT INTO Customers " _
		& "SELECT * " _
		& "FROM [New Customers];"
		
	dbs.Close

End Sub

This example creates a new record in the Employees table.

Sub InsertIntoX2()

	Dim dbs As Database

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")
	
	' Create a new record in the Employees table. The 
	' first name is Harry, the last name is Washington, 
	' and the job title is Trainee.
	dbs.Execute " INSERT INTO Employees " _
		& "(FirstName,LastName, Title) VALUES " _
		& "('Harry', 'Washington', 'Trainee');"
		
	dbs.Close

End Sub

Example (DAO)
This example requires the user to provide a job title and then uses that job title as the 
criteria for the query.

This example calls the EnumFields procedure, which you can find in the SELECT statement 
example.

Sub ParametersX()

	Dim dbs As Database, qdf As QueryDef
	Dim rst As Recordset
	Dim strSql As String, strParm As String
	Dim strMessage As String
	Dim intCommand As Integer
	
' Modify this line to include the path to Northwind on your computer.
	Set dbs = OpenDatabase("NorthWind.mdb")
	
' Define the parameters clause.
	strParm = "PARAMETERS [Employee Title] TEXT; "

' Define an SQL statement with the parameters clause.
	strSql = strParm & "SELECT LastName, FirstName, " _
			 & "EmployeeID " _
			 & "FROM Employees " _
			 & "WHERE Title =[Employee Title];"
	
' Create a QueryDef object based on the SQL statement.
	Set qdf = dbs.CreateQueryDef("Find Employees", strSql)
	
	Do While True
		strMessage = "Find Employees by Job " _
			   & "title:" & Chr(13) _
			   & "  Choose Job Title:" & Chr(13) _
			   & "   1 - Sales Manager" & Chr(13) _
			   & "   2 - Sales Representative" & Chr(13) _
			   & "   3 - Inside Sales Coordinator"

		intCommand = Val(InputBox(strMessage))
		
		Select Case intCommand
			Case 1
				qdf("Employee Title") = "Sales Manager"
			Case 2
				qdf("Employee Title") = "Sales Representative"
			Case 3
				qdf("Employee Title") = "Inside Sales Coordinator"
			Case Else
				Exit Do
		End Select
		
' Create a temporary snapshot-type Recordset.
		Set rst = qdf.OpenRecordset(dbOpenSnapshot)

' Populate the Recordset.
		rst.MoveLast
			
' Call EnumFields to print the contents of the Recordset. 
' Pass the Recordset object and desired field width.
		EnumFields rst, 12
	Loop
	
' Delete the QueryDef because this is a demonstration.
	dbs.QueryDefs.Delete "Find Employees"
	
	dbs.Close

End Sub

Example (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and run 
the query.

The following example prompts the user to provide an employee's last name and then uses 
that entry as the criteria for the query:

PARAMETERS [Enter a Last Name:] Text;
SELECT *
FROM Employees
WHERE LastName = [Enter a Last Name:];

The next example prompts the user to provide a category ID and then uses that entry as 
the criteria for the query:

PARAMETERS [Enter a Category ID:] Value;
SELECT CategoryID, ProductName, Count([Order Details].OrderID) AS Tally
FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
GROUP BY CategoryID, ProductName
HAVING CategoryID = [Enter a Category ID:];

Examples
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a 
table or query. Switch to SQL view, paste an individual example into the SQL window, 
and run the query.

The following example prompts the user to provide an employee's last name and then 
uses that entry as the criteria for the query:

PARAMETERS [Enter a Last Name:] Text;
SELECT *
FROM Employees
WHERE LastName = [Enter a Last Name:];

The next example prompts the user to provide a category ID and then uses that entry 
as the criteria for the query:

PARAMETERS [Enter a Category ID:] Value;
SELECT CategoryID, ProductName, Count([Order Details].OrderID) AS Tally
FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
GROUP BY CategoryID, ProductName
HAVING CategoryID = [Enter a Category ID:];

Method 1: Parameter query

The simpest approach is to base the report on a parameter query. This approach 
works for all kinds of queries, but has these disadvantages:

Inflexible: both dates must be entered. 
Inferior interface: two separate dialog boxes pop up 
No way to supply defaults. 
No way to validate the dates. 

To create the parameter query:
Create a query to use as the RecordSource of your report. 
In query design view, in the Criteria row under your date field, enter: 
    Between [StartDate] And [EndDate]
Choose Parameters from the Query menu, and declare two parameters of type 
Date/Time: 

    StartDate    Date/Time
    EndDate      Date/Time
    
To display the limiting dates on the report, open your report in Design View, 
and add two text boxes to the Report Header section. Set their ControlSource 
property to =StartDate and =EndDate respectively. 

(Note: Step 3 is optional, but strongly recommended. It prevents invalid dates 
being entered, and helps Access understand the date regardless of your regional 
setting date format.)

Method 2: Form for entering the dates

The alternative is to use a small unbound form where the user can enter the 
limiting dates. This approach does not work with aggregate (GROUP BY) or crosstab 
(TRANSFORM) queries, but has these advantages:

Flexible: user does not have to limit report to from and to dates. 
Better interface: allows defaults and other mechanisms for choosing dates. 
Validation: can verify the date entries. 

Here are the steps. This example assumes a report named rptSales, limited by values 
in the SaleDate field.

Create a new form that is not bound to any query or table. Save with the name 
frmWhatDates. 

Add two text boxes, and name them StartDate and EndDate. Set their Format property 
to Short Date, so only date entries will be accepted. 

Add two command buttons for Ok and Cancel. Set the Ok button's Default property to 
Yes, and the Cancel button's Cancel property set to Yes. 

Set the On Click property for both buttons to [Event Procedure]. Beside this is a 
"..." button which opens the code window to the procedure. 

Enter this line into Cancel button's Click procedure 
(between the "Private Sub ..." and "End Sub" lines): 

        DoCmd.Close acForm, Me.Name
	
Paste this into the Ok button's Click procedure: 

    Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strReport = "rptSales"
    strField = "SaleDate"

    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
            strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
            strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
        End If
    End If

    ' Debug.Print strWhere                   'For debugging purposes only.
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    
Open the report in Design View, and add two text boxes to the report header for 
displaying the date range. Set the ControlSource for these text boxes to: 

        =Forms.frmWhatDates.StartDate
        =Forms.frmWhatDates.EndDate

Examples (Access)
The following example selects the LastName and FirstName fields of all records in the 
Employees table:

SELECT LastName, FirstName FROM Employees;

The next example selects all fields from the Employees table:

SELECT * FROM Employees;

The following example counts the number of records that have an entry in the PostalCode 
field and names the returned field Tally:

SELECT Count(PostalCode) AS Tally FROM Customers;

The next example shows what product unit prices would be if each were increased by 10 
percent. It doesn't change the existing unit prices in the database.

SELECT ProductName, UnitPrice AS Current, UnitPrice * 1.1 AS ProposedNewPrices
FROM Products;

The following example calculates the number of products in the database and the average 
and maximum unit prices:

SELECT Count(*) AS [Total Products], Avg(UnitPrice) AS [Average Unit Price], Max(UnitPrice)
AS [Maximum Unit Price] FROM Products;

The next example displays the ProductName and UnitPrice for each record in the Products 
table. The string "has a unit price of" separates the two fields in the result set.

SELECT ProductName, 'has a unit price of', UnitPrice FROM Products;

Example (DAO)
Some of the following examples assume the existence of a hypothetical Salary field in 
an Employees table. Note that this field does not actually exist in the Northwind database 
Employees table.

This example creates a dynaset-type Recordset based on an SQL statement that selects 
the LastName and FirstName fields of all records in the Employees table. It calls the 
EnumFields procedure, which prints the contents of a Recordset object to the Debug window.

Sub SelectX1()

	Dim dbs As Database, rst As Recordset

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Select the last name and first name values of all 
	' records in the Employees table.
	Set rst = dbs.OpenRecordset("SELECT LastName, " _
		& "FirstName FROM Employees;")

	' Populate the recordset.
	rst.MoveLast

' Call EnumFields to print the contents of the
' Recordset.
	EnumFields rst,12

dbs.Close


End Sub

This example counts the number of records that have an entry in the PostalCode field 
and names the returned field Tally.

Sub SelectX2()

	Dim dbs As Database, rst As Recordset

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Count the number of records with a PostalCode 
	' value and return the total in the Tally field.
	Set rst = dbs.OpenRecordset("SELECT Count " _
		& "(PostalCode) AS Tally FROM Customers;")

	' Populate the Recordset.
	rst.MoveLast

	' Call EnumFields to print the contents of 
	' the Recordset. Specify field width = 12.
	EnumFields rst, 12

	dbs.Close

End Sub

This example shows the number of employees and the average and maximum salaries.

Sub SelectX3()

	Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind
	' on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

' Count the number of employees, calculate the 
	' average salary, and return the highest salary.
	Set rst = dbs.OpenRecordset("SELECT Count (*) " _
		& "AS TotalEmployees, Avg(Salary) " _
		& "AS AverageSalary, Max(Salary) " _
		& "AS MaximumSalary FROM Employees;")

	' Populate the Recordset.
	rst.MoveLast

	' Call EnumFields to print the contents of
	' the Recordset. Pass the Recordset object and
	' desired field width.
	EnumFields rst, 17

dbs.Close

End Sub

The Sub procedure EnumFields is passed a Recordset object from the calling procedure. 
The procedure then formats and prints the fields of the Recordset to the Debug window. 
The intFldLen variable is the desired printed field width. Some fields may be truncated.

Sub EnumFields(rst As Recordset, intFldLen As Integer)

	Dim lngRecords As Long, lngFields As Long
	Dim lngRecCount As Long, lngFldCount As Long
	Dim strTitle As String, strTemp As String

	' Set the lngRecords variable to the number of
	' records in the Recordset.
	lngRecords = rst.RecordCount
	' Set the lngFields variable to the number of
	' fields in the Recordset.
	lngFields = rst.Fields.Count
	
	Debug.Print "There are " & lngRecords _
		& " records containing " & lngFields _

& " fields in the recordset."
	Debug.Print
	
	' Form a string to print the column heading.
	strTitle = "Record  "
	For lngFldCount = 0 To lngFields - 1
		strTitle = strTitle _
		& Left(rst.Fields(lngFldCount).Name _
		& Space(intFldLen), intFldLen)
	Next lngFldCount
	
	' Print the column heading.
	Debug.Print strTitle
	Debug.Print
	
	' Loop through the Recordset; print the record
	' number and field values.
	rst.MoveFirst
	For lngRecCount = 0 To lngRecords - 1

Debug.Print Right(Space(6) & _
			Str(lngRecCount), 6) & "  ";
		For lngFldCount = 0 To lngFields - 1
			' Check for Null values.
			If IsNull(rst.Fields(lngFldCount)) Then
				strTemp = ""
			Else
				' Set strTemp to the field contents. 
				Select Case _
					rst.Fields(lngFldCount).Type
					Case 11
						strTemp = ""
					Case dbText, dbMemo
						strTemp = _
							rst.Fields(lngFldCount)

Case Else
						strTemp = _
							str(rst.Fields(lngFldCount))
				End Select
			End If
			Debug.Print Left(strTemp _ 
				& Space(intFldLen), intFldLen);
		Next lngFldCount
		Debug.Print
		rst.MoveNext
	Next lngRecCount

End Sub

Example (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and 
run the query.

The following example selects all records in the Employees table and copies them into 
a new table named Employees Backup:

SELECT * INTO [Employees Backup] FROM Employees;

The next example creates a new table called Sales Representatives that contains only 
employee records that have the title Sales Representative:

SELECT Employees.FirstName, LastName INTO [Sales Representatives]
FROM Employees
WHERE Title = 'Sales Representative';

The following example makes a copy of the Employees table and places the new table in 
the assumed database Backup.mdb:

SELECT Employees.* INTO Employees IN Backup.mdb FROM Employees;

The next example assumes a Payroll table with two fields: EmployeeID and Salary. This 
example creates a new table that contains employee and payroll data for all trainees. 
The Employees and Payroll tables have a one-to-one relationship. The new table contains 
all of the data from the Employees table plus the Salary field from the Payroll table.

SELECT Employees.*, Salary INTO Trainees
FROM Employees
INNER JOIN Payroll ON Employees.EmployeeID = Payroll.EmployeeID
WHERE Title = 'Trainee';

Example (DAO)
This example selects all records in the Employees table and copies them into a new table 
named Emp Backup.

Sub SelectIntoX()

	Dim dbs As Database
	Dim qdf As QueryDef

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Select all records in the Employees table 
	' and copy them into a new table, Emp Backup.
	dbs.Execute "SELECT Employees.* INTO " _
		& "[Emp Backup] FROM Employees;"
		
	' Delete the table because this is a demonstration.
	dbs.Execute "DROP TABLE [Emp Backup];"

dbs.Close

End Sub

Example (Access)
The following example shows all non-discounted orders whose total is higher than 
the average order value:

SELECT OrderID, (UnitPrice * Quantity) As OrderTotal FROM [Order Details]
WHERE Discount = 0 AND (UnitPrice * Quantity) > ALL(SELECT Avg(UnitPrice * Quantity)
FROM [Order Details]);

The next example lists the name and unit price of every product whose unit price is the 
same as that of Aniseed Syrup:

SELECT ProductName, UnitPrice FROM Products
WHERE UnitPrice = (SELECT UnitPrice FROM [Products]
WHERE ProductName = 'Aniseed Syrup');

The following example lists the company and contact of every customer who placed an 
order in the second quarter of 1995:

SELECT ContactName, CompanyName, ContactTitle, Phone FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate
BETWEEN #04/1/95# AND #06/30/95#);

The next example selects the name of every employee who has booked at least one order. 
This could also be done with an INNER JOIN.

SELECT FirstName, LastName FROM Employees
WHERE EXISTS (SELECT OrderID FROM Orders
WHERE Orders.EmployeeID = Employees.EmployeeID);

Example (DAO)
This example lists the name and contact of every customer who placed an order in the 
second quarter of 1995.

This example calls the EnumFields procedure, which you can find in the SELECT statement 
example.

Sub SubQueryX()

	Dim dbs As Database, rst As Recordset

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")
	
	' List the name and contact of every customer 
	' who placed an order in the second quarter of
	' 1995.
	Set rst = dbs.OpenRecordset("SELECT ContactName," _
		& " CompanyName, ContactTitle, Phone" _
		& " FROM Customers" _
		& " WHERE CustomerID" _
		& " IN (SELECT CustomerID FROM Orders" _
		& " WHERE OrderDate Between #04/1/95#" _
		& " And #07/1/95#);")
	
	' Populate the Recordset.
	rst.MoveLast
	
	' Call EnumFields to print the contents of the 
	' Recordset. Pass the Recordset object and desired
	' field width.
	EnumFields rst, 25

	dbs.Close

End Sub

Example (Access)
To try the following examples in Microsoft Access, first create a new query in the Northwind 
sample database. Close the Show Table dialog box without specifying a table or query. Switch 
to SQL view, paste an individual example into the SQL window, and run the query.

Warning   These examples makes changes to the Northwind sample database. Before beginning, 
you may wish to make a backup copy of the sample database.

The following example changes values in the ReportsTo field to 5 for all employee records 
that currently have ReportsTo values of 2:

UPDATE Employees SET ReportsTo = 5 WHERE ReportsTo = 2;

The next example increases the UnitPrice for all non-discontinued products from supplier 
#8 by 10 percent:

UPDATE Products SET UnitPrice = UnitPrice * 1.1
WHERE SupplierID = 8 AND Discontinued = No;

The following example reduces the UnitPrice for all non-discontinued products supplied by 
Tokyo Traders by 5 percent. The Products and Suppliers tables have a many-to-one relationship.

UPDATE Suppliers INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID SET UnitPrice = UnitPrice * .95
WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;

Example (DAO)
This example changes values in the ReportsTo field to 5 for all employee records that 
currently have ReportsTo values of 2.

Sub UpdateX()

	Dim dbs As Database
	Dim qdf As QueryDef

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")
	
	' Change values in the ReportsTo field to 5 for all 
	' employee records that currently have ReportsTo 
	' values of 2.
	dbs.Execute "UPDATE Employees " _
		& "SET ReportsTo = 5 " _
		& "WHERE ReportsTo = 2;"
		
	dbs.Close

End Sub

Examples (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and run 
the query.

The following example selects the LastName and FirstName fields of each record in which 
the last name is King:

SELECT LastName, FirstName FROM Employees
WHERE LastName = 'King';

The next example selects the LastName and FirstName fields for employees whose last 
names begin with the letter S:

SELECT LastName, FirstName FROM Employees
WHERE LastName Like 'S*';

The following example selects products whose unit prices are between $20 and $50, 
inclusive:

SELECT ProductName, UnitPrice FROM Products
WHERE (UnitPrice >=20.00 And UnitPrice <= 50.00);

The next example selects all products whose names fall in alphabetic order between 
"Cha" and "Out", inclusive. It doesn't retrieve "Outback Lager" because "Outback Lager" 
follows "Out" and therefore is outside the specified range.

SELECT ProductName, UnitPrice FROM Products
WHERE ProductName Between 'Cha' And 'Out';

The following example selects orders placed during the first half of 1995:

SELECT OrderID, OrderDate FROM Orders
WHERE OrderDate Between #1-1-95# And #6-30-95#;

The next example selects orders shipped to Idaho, Oregon, or Washington:

SELECT OrderID, ShipRegion FROM Orders
WHERE ShipRegion In ('ID', 'OR', 'WA');

Examples (DAO)
The following example assumes the existence of a hypothetical Salary field in an 
Employees table. Note that this field does not actually exist in the Northwind 
database Employees table.

This example selects the LastName and FirstName fields of each record in which the 
last name is King. 

This example calls the EnumFields procedure, which you can find in the SELECT 
statement example.

Sub WhereX()

	Dim dbs As Database, rst As Recordset

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Select records from the Employees table where the
	' last name is King.
	Set rst = dbs.OpenRecordset("SELECT LastName, " _
		& "FirstName FROM Employees " _
		& "WHERE LastName = 'King';")
	
	' Populate the Recordset.
	rst.MoveLast
	
	' Call EnumFields to print the contents of the
	' Recordset.
	EnumFields rst, 12

	dbs.Close

End Sub

Example
This example uses the For Each...Next statement to search the Text property of all 
elements in a collection for the existence of the string "Hello". In the example, 
MyObject is a text-related object and is an element of the collection MyCollection. 
Both are generic names used for illustration purposes only.

Dim Found, MyObject, MyCollection

Found = False					' Initialize variable.

For Each MyObject In MyCollection		' Iterate through each element.
	If MyObject.Text = "Hello" Then		' If Text equals "Hello".
		Found = True			' Set Found to True.
		Exit For			' Exit loop.
	End If
Next

Example (Excel)
This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 
0.001, the code replaces the value with 0 (zero).

For Each c in Worksheets("Sheet1").Range("A1:D10")
	If c.Value < .001 Then
		c.Value = 0
	End If
Next c

This example loops on the range named "TestRange" and then displays the number of empty 
cells in the range.

numBlanks = 0
For Each c In Range("TestRange")
	If c.Value = "" Then
		numBlanks = numBlanks + 1
	End If
Next c
MsgBox "There are " & numBlanks & " empty cells in this range."

This example closes and saves changes to all workbooks except the one that's running 
the example.

For Each w In Workbooks
	If w.Name <> ThisWorkbook.Name Then
		w.Close savechanges:=True
	End If
Next w

This example deletes every worksheet in the active workbook without displaying the 
confirmation dialog box. There must be at least one other visible sheet in the workbook.

Application.DisplayAlerts = False
For Each w In Worksheets
	w.Delete
Next w
Application.DisplayAlerts = True

This example creates a new worksheet and then inserts into it a list of all the names 
in the active workbook, including their formulas in A1-style notation in the language 
of the user.

Set newSheet = ActiveWorkbook.Worksheets.Add
i = 1
For Each nm In ActiveWorkbook.Names
	newSheet.Cells(i, 1).Value = nm.NameLocal
	newSheet.Cells(i, 2).Value = "'" & nm.RefersToLocal
	i = i + 1
Next nm

Example
This example uses the Shell function to run the Calculator application included with 
Microsoft Windows.It uses the SendKeys statement to send keystrokes to add some numbers, 
and then quit the Calculator. The SendKeys statement is not available on the Macintosh. 
(To see the example, paste it into a procedure, then run the procedure. Because AppActivate 
changes the focus to the Calculator application, you can't single step through the code.)

Dim ReturnValue, I
ReturnValue = Shell("CALC.EXE", 1)	' Run Calculator
AppActivate ReturnValue 		' Activate the Calculator
For I = 1 To 100			' Set up counting loop
  SendKeys I & "{+}", True		' Send keystrokes to Calculator
Next I					' to add each value of I
SendKeys "=", True			' Get grand total
SendKeys "%{F4}", True			' Send ALT+F4 to close Calculator

Example (Excel)
This example adds a new worksheet to the active workbook and then sets the name of 
the worksheet.

Set newSheet = Worksheets.Add
newSheet.Name = "1995 Budget"

This example creates a new worksheet and then inserts into it a list of all the names 
in the active workbook, including their formulas in A1-style notation in the language 

Set newSheet = ActiveWorkbook.Worksheets.Add
i = 1
For Each nm In ActiveWorkbook.Names
	newSheet.Cells(i, 1).Value = nm.NameLocal
	newSheet.Cells(i, 2).Value = "'" & nm.RefersToLocal
	i = i + 1
Next

Examples (Access)
To try the following examples in Microsoft Access, first create a new query in the 
Northwind sample database. Close the Show Table dialog box without specifying a table 
or query. Switch to SQL view, paste an individual example into the SQL window, and 
run the query.

The following example retrieves the names and cities of all suppliers and customers in 
Brazil:

SELECT CompanyName, City FROM Suppliers
WHERE Country = 'Brazil'
UNION SELECT CompanyName, City FROM Customers
WHERE Country = 'Brazil';

The next example retrieves the names and cities of all suppliers and customers located 
in Brazil:

SELECT CompanyName, City, 'Supplier' AS Source
FROM Suppliers
WHERE Country = 'Brazil'
UNION SELECT CompanyName, City, 'Customer'
FROM Customers
WHERE Country = 'Brazil'
ORDER BY City, Source;

The following example retrieves the names and IDs of all suppliers and customers. This 
union assumes that there are the same number of columns in each table.

TABLE Customers UNION TABLE Suppliers;

Example (DAO)
This example retrieves the names and cities of all suppliers and customers in Brazil.
This example calls the EnumFields procedure, which you can find in the SELECT statement 
example.

Sub UnionX()

	Dim dbs As Database, rst As Recordset

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")
	
	' Retrieve the names and cities of all suppliers 
	' and customers in Brazil.
	Set rst = dbs.OpenRecordset("SELECT CompanyName," _
		& " City FROM Suppliers" _
		& " WHERE Country = 'Brazil' UNION" _
		& " SELECT CompanyName, City FROM Customers" _
		& " WHERE Country = 'Brazil';")

	' Populate the Recordset.
	rst.MoveLast
	
	' Call EnumFields to print the contents of the 
	' Recordset. Pass the Recordset object and desired
	' field width.
	EnumFields rst, 12

	dbs.Close

End Sub