| Microsoft Office 2000/Visual Basic Programmer's Guide | |
If your solution is based in Excel, you can write code in Excel to import static data from an Access database. Once you’ve defined an SQL statement that returns the records you want from the database, you can open a recordset, and then use the CopyFromRecordset method to copy data from a recordset into a range on an Excel worksheet. The recordset may be either an ADO or a DAO recordset.
The CopyFromRecordset method simply copies data into a worksheet. To update the worksheet after the data has changed, you must copy the data to the worksheet again; there is no dynamic link to the data. The following code fragment creates an ADO recordset and copies the data in the recordset into a range on a worksheet.
Dim cnnConnect As New ADODB.Connection
Dim rstData As New ADODB.Recordset
Dim lngReturn As Long
' Open ADO connection.
cnnConnect.Open strConnect
' Open Recordset object.
rstData.Open strSQL, cnnConnect, adOpenForwardOnly
' Copy data from recordset and store return value.
lngReturn = rngDest.CopyFromRecordset(rstData)
This code fragment is taken from the InsertStaticData procedure in the modStaticReport module in the Northwind.xls sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.
Note When you display data from an Access database in Excel, any lookup fields in the source table or query will display their underlying numeric values, rather than the text values that you usually see in Access. A lookup field is one that stores a numeric value but displays a text value. Access uses the numeric value to “look up” the text value to display. To display the corresponding text value for a lookup field, you need to create an SQL statement that performs an inner join on the lookup field and retrieves the text value that it represents.