Access.2007.VBA.Bibl..

Attempt to find the database, and put up a message if it is not found.

Set fil = fso.GetFile(strDBNameAndPath)

If fil Is Nothing Then

strPrompt = “Can’t find “ & strDBName & “ in “ _

& strCurrentPath & “; please copy it from the “ _

& “Office11\Samples subfolder under the main “ _

& “Microsoft Office folder “ _

& “of an earlier version of Office”

MsgBox strPrompt, vbCritical + vbOKOnly

GoTo ErrorHandlerExit

End If

On Error GoTo ErrorHandler

Set cnn = New ADODB.Connection

Set rst = New ADODB.Recordset

Need to specify the Jet 4.0 provider for connecting to Access databases.

With cnn

.Provider = “Microsoft.Jet.OLEDB.4.0”

.Open strDBNameAndPath

strConnectString = .ConnectionString

End With

Use a SQL string to create a filtered recordset.

strSQL = “SELECT CompanyName, ContactName, City “ _

& “FROM Suppliers “ _

& “WHERE Country = ‘Australia’ “ _

& “ORDER BY CompanyName;”

Modify the cursortypeand locktypearguments as desired to test what type of recordset is cre-
ated when the procedure is run.

rst.Open Source:=strSQL, _

ActiveConnection:=strConnectString, _

CursorType:=adOpenForwardOnly, _

LockType:=adLockOptimistic

strCursorType = Switch(rst.CursorType = _

adOpenDynamic, _

“Dynamic (“ & adOpenDynamic & “)”, _

rst.CursorType = adOpenForwardOnly, _

“Forward-only (“ _

& adOpenForwardOnly & “)”, _

rst.CursorType = adOpenKeyset, “Keyset (“ _

& adOpenKeyset & “)”, _

Working with Access Data 5