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 .mdb format databases.

With cnn

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

.Open strDBNameAndPath

strConnectString = .ConnectionString

End With

Set cmdSQL = New ADODB.Command

Set cmdSQL.ActiveConnection = cnn

Use a SQL string to create a command.

strSQL = “SELECT CompanyName, ContactName, “ _

& “City FROM Suppliers “ _

& “WHERE Country = ‘Sweden’ “ _

& “ORDER BY CompanyName;”

cmdSQL.CommandText = strSQL

Set rst = cmdSQL.Execute

Check cursor and lock type of recordset.

strCursorType = Switch(rst.CursorType = _

adOpenDynamic, _

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

rst.CursorType = adOpenForwardOnly, _

“Forward-only (“ _

& adOpenForwardOnly & “)”, _

rst.CursorType = adOpenKeyset, “Keyset (“ _

& adOpenKeyset & “)”, _

rst.CursorType = adOpenStatic, “Static (“ _

& adOpenStatic & “)”)

Part II Writing VBA Code to Exchange Data between Office Components