Retrieving Data from Microsoft Access Using a Stored Query

Microsoft Access allows you to create and store SQL queries in the database. You can retrieve data from these stored queries just as easily as you can use a plain text SQL statement. The following procedure demonstrates this:

Public Sub SavedQuery()

Public Sub SavedQuery()

Dim sConnect As String ' Create the connection string.

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\Northwind 2007.accdb"

' Create the Recordset object and run the query. Set rsData = New ADODB.Recordset rsData.Open "[Product Sales By Category]", sConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

' Make sure we got records back

If Not rsData.EOF Then

' Add headers to the worksheet.

With Sheet1.Range("A1")

For Each objField In rsData.Fields

.Offset(0, lOffset).Value =

objField.Name

lOffset = lOffset + 1

Next objField

.Resize(1, rsData.Fields.Count)

.Font.Bold = True

End With

' Dump the contents of the recordset onto the worksheet. Sheet1.Range("A2").CopyFromRecordset rsData

' Dump the contents of the recordset onto the worksheet. Sheet1.Range("A2").CopyFromRecordset rsData

' Close the Recordset object. rsData.Close

' Fit the column widths to the data. Sheetl.UsedRange.EntireColumn.AutoFit

Else

' Close the Recordset object. rsData.Close

MsgBox "Error: No records returned.", vbCritical End If

' Destroy the Recordset object. Set rsData = Nothing

End Sub

There are two important points to note about this procedure:

□ Examine the differences between the Recordset.Open method used in this procedure and the one used in the plain text query. In this case, rather than providing a SQL string, you specified the name of the stored query you wanted to execute. You also told the provider that the type of query being executed was a table query. The Access OLE DB provider treats stored queries and queries of entire database tables in the same manner.

□ Because you did not create the SQL statement yourself, you did not know the names of the fields you were retrieving, or even how many fields there were. Therefore, to create the correct set of headers for each column in the destination worksheet, you needed to loop the Fields collection of the Recordset object and determine this information dynamically. To accomplish this, the recordset had to be open, so you added the fields to the worksheet prior to closing the recordset.

0 0

Post a comment