Using SQL and ADO to Populate a Recordset

Now that you have learned the basic syntax for Select statements, let's look at an example that uses a SQL statement along with ADO. The following example opens a new recordset and uses a SQL statement as the source for the Open method.

Sub TestSQLSelect()

Dim rsContacts As ADODB.Recordset

'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts

.CursorType = adOpenStatic

'open the recordset based on SQL statement using the existing connection .Open "SELECT * FROM tblContacts", CurrentProject.Connection End With

'loop through the recordset and display some info for

'each record

Do Until rsContacts.EOF

Debug.Print "Contact Id: " & rsContacts!intContactId & _

rsContacts.MoveNext

Loop

'close the recordset rsContacts.Close

'set the recordset and connection to nothing Set rsContacts = Nothing

End Sub

A portion of each record that is returned in the recordset is then printed in the Immediate Window as shown in Figure 5.14 to demonstrate that the SQL statement selected what you expected it to select.

Last Name: " & rsContacts!txtLastName & First Name: " & rsContacts!txtFirstName

Im mediate tu

Te3t5QL5eiect

Contact Id: i La3t Name: Gc3nell First Name: Deri3e

Contact Id: 2 La3t Name: Doe First Name: John

Contact Id: 4 La3t Name: Doe First Name: Jane

The prior example retrieves data from a common data source: a database table. However, ADO enables you to connect to various data sources, and it can treat those data sources as virtual tables. For example, you can connect to Microsoft Outlook using a SQL statement and ADO to populate a recordset. Here is an example of how you can retrieve data from an Outlook Calendar on Microsoft Exchange and print the results to the Immediate Window.

Sub RetrieveCalendar()

Dim strConnection As String Dim cnConn As ADODB.Connection Dim rsCalendar As ADODB.Recordset

Set cnConn = New ADODB.Connection Set rsCalendar = New ADODB.Recordset

With cnConn

.Provider = "Microsoft.JET.OLEDB.4.0" .ConnectionString = "Exchange 4.0;" _

& "MAPILEVEL=Mailbox - Gosnell, Denise|;" _ & "PROFILE=DMG;" _

& "TABLETYPE=0;DATABASE=C: \WINDOWS\TEMP\; "

.Open End With

With rsCalendar

.Open "Select * from Calendar", cnConn, adOpenStatic, _

adLockReadOnly 'print all records in calendar Do While Not rsCalendar.EOF

Debug.Print rsCalendar(3).Name & ": " & rsCalendar(3).Value Debug.Print rsCalendar(10).Name & ": " & rsCalendar(10).Value Debug.Print vbCrLf rsCalendar.MoveNext

Loop .Close End With

Set rsCalendar = Nothing cnConn.Close

Set cnConn = Nothing

End Sub

In the preceding example, the Mailbox and Profile as used by your Outlook database should be specified. When you run the preceding code with a valid Mailbox and Profile, you see results similar to the following in the Immediate Window:

Subject: Doctor's Appointment Received: 5/24/2004 2:30:18 PM

Subject: Meet Benita for lunch

Received:

5/25/2004 10:06:52

AM

Subject:

Jazz Fest Training

Meeting

Received:

6/10/2004 2:28:51

PM

0 0

Post a comment