Access.2007.VBA.Bibl..

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

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] _

& vbCrLf & vbTab & “City: “ & ![City] _

& vbCrLf

rst.MoveNext

Loop

End With

ErrorHandlerExit:

Close the Recordset and Connection objects.

If Not rst Is Nothing Then

If rst.State = adStateOpen Then

rst.Close

Set rst = Nothing

End If

End If

Part II Writing VBA Code to Exchange Data between Office Components