Retrieving Data with the Get Rows Method

To place Microsoft Access data into an Excel spreadsheet, you can use the GetRows method. This method returns a two-dimensional array where the first subscript is a number representing the field, and the second subscript is the number representing the record. Record and field numbering begins with 0.

You can programmatically return data to a Microsoft Excel worksheet by using Data Access Objects (DAO) in your VBA procedure. The following example procedure demonstrates how to run the Invoices query in the Northwind database and return records to a worksheet. For this procedure to work correctly, you must first establish a reference to the Microsoft Access 3.6 Object Library. Refer to the instructions on setting up a reference to object libraries earlier in this chapter.

After opening an Access database, the GetData_withDAO2 procedure illustrated below runs the Invoices query using the following statement:

Set qdf = db.QueryDefs("Invoices")

The QueryDefs object in the Microsoft Access 3.6 Object Library represents a Select or Action query. Select queries return data from one or more tables or queries, while Action queries allow you to modify data (you can add, modify, or delete records using Action queries).

After executing the query, the procedure places the records returned by the query in the object variable of type Recordset using the OpenRecord-set method, as shown below:

Set rst = qdf.OpenRecordset

Next, the record count is retrieved using the RecordCount method and placed in the countR variable. Notice that to obtain the correct record count, the record pointer has to be moved to the last record in the recordset by using the MoveLast method.

rst.MoveLast countR = rst.RecordCount

Next, the procedure prompts the user to enter the number of records to return to the worksheet. You can cancel at this point by clicking the Cancel button in the Input dialog box or typing the number of records to retrieve. If you enter a number that is greater than the record count, the procedure will retrieve all the records. Before retrieving records, you must move the record pointer to the first record by using the MoveFirst method. If you forget to do this, the record pointer will remain on the last record and only one record will be retrieved. The procedure then goes on to activate the Get Records worksheet and clear the current region. The records are first returned to the Variant variable containing a two-dimensional array by using the GetRows method of the Recordset object. Next, the procedure loops through both dimensions of the array to place the records in the worksheet starting at cell A2. When this is done, another loop will fill in the first worksheet row with the names of fields and autofit each column so that the data is displayed correctly.

Sub GetData_withDAO2() Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset Dim recArray As Variant Dim i As Integer Dim j As Integer Dim strPath As String Dim a As Variant

Dim countR As Long Dim strShtName As String strPath = "C:\Program Files\Microsoft Office\" _

& "Office\Samples\northwind.mdb" strShtName = "Returned records" Set db = OpenDatabase(strPath)

Set qdf = db.QueryDefs("Invoices") Set rst = qdf.OpenRecordset rst.MoveLast countR = rst.RecordCount a = InputBox("This recordset contains " & _ countR & " records." & vbCrLf _ & "Enter number of records to return: ", _ "Get Number of Records")

MsgBox "The number you entered is too large." & vbCrLf _ & "All records will be returned."

End If

Workbooks.Add ActiveWorkbook.Worksheets(1).Name = strShtName rst.MoveFirst With Worksheets(strShtName).Range("A1") .CurrentRegion.Clear recArray = rst.GetRows(a) For i = 0 To UBound(recArray, 2) For j = 0 To UBound(recArray, 1)

.Offset(0, j) = rst.Fields(j).Name .Offset(0, j).EntireColumn.AutoFit Next j End With db.Close End Sub

0 0

Post a comment