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;”

rst.Open Source:=strSQL, _

ActiveConnection:=strConnectString, _

CursorType:=adOpenStatic, _

LockType:=adLockReadOnly

Iterate through the recordset, and print values from fields to the Immediate window.

With rst

.MoveLast

.MoveFirst

Debug.Print .RecordCount _

& “ records in recordset” & vbCrLf

Do While Not .EOF

Debug.Print “Australian Company name: “ _

& ![CompanyName] _

& vbCrLf & vbTab & “Contact name: “ _

& ![ContactName] _

Part II Writing VBA Code to Exchange Data between Office Components