Connection

Although the ADO object model is not hierarchical (unlike the DAO object model), the Connection object is the foundation object, because connections are the link to data in databases.

In DAO recordsets, the BOF property represents the beginning of the recordset (Beginning Of File), and the EOF (End Of File) property represents the end.

The handy NoMatch property of DAO recordsets is missing from ADO; instead, you have to determine whether or not a search succeeded by examining where the cursor is. For example, in doing a Find, starting from the beginning of the recordset (BOF) and moving forward, if the cursor ends up at the end of the recordset (EOF), the search was unsuccessful. Here is some sample code to illustrate this technique; if the search is successful, this means that the proposed new category name has already been used, whereas if the cursor ends up at the end of the recordset (EOF), the search was unsuccessful, and the new record can be created using the new category name in the strSearch variable (the code segment is part of the TestKeysetOptimistic procedure, which is listed later in this chapter):

rst.Find strSearch If rst.EOF = False Then strPrompt = Chr$(39) & strCategory _ & Chr$(3 9) & " already used; " _ & "please enter another category " _ & "name" strTitle = "Category used" MsgBox prompt:=strPrompt, _

Buttons:=vbExclamation + vbOKOnly, _ Title:=strTitle GoTo CategoryName

The Tables collection and form recordsets are not supported in ADO, so you will need to continue to use DAO (either the old DAO 3.6 or the new Access 2007 DAO object model) to work with them.

If you don't close and set to Nothing DAO database or recordset objects, it is extremely unlikely that you will have any problems; however, if you leave ADO connections and recordsets open, the next time you run the code, you may get this error message (with your logon name and computer name), and you will have to close down and reopen the database to get the code working again: "The database has been placed in a state by user 'Admin' on machine 'DELL_DIMEN_8300' that prevents it from being opened or locked." The sample ADO procedures have code to close any open connection or recordset in their error handlers.

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 procedure 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

Need to specify the Jet 4.0 provider for connecting to Access .mdb format databases. With cnn

.Provider = "Microsoft.Jet.OLEDB.4.C>" .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

If Not cnn Is Nothing Then

If cnn.State = adStateOpen Then cnn.Close Set cnn = Nothing End If End If

Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit End Sub

0 0

Post a comment