Access.2007.VBA.Bibl..

The syntax for creating a connection to the current database is simple:

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

The ADO syntax for working with a recordset in an external database is a little different; you have
to specify the database path and file name, and specify the Microsoft Jet 4.0 provider, as in the pro-
cedure listed as follows, which uses a SQL string to create a recordset:

Private Sub OpenRecordsetSQL()

On Error Resume Next

Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim strDBName As String

Dim strConnectString As String

Dim strSQL As String

Dim strDBNameAndPath As String

Dim strCurrentPath As String

Dim fso As New Scripting.FileSystemObject

Dim fil As Scripting.File

Dim strPrompt As String

Create connection to an external database.

strCurrentPath = Application.CurrentProject.Path & “\”

strDBName = “Northwind.mdb”

strDBNameAndPath = strCurrentPath & strDBName

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

Working with Access Data 5