Linking a Microsoft Excel Spreadsheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method of the DoCmd object, as shown in Hands-On 11-9. Note, however, that neither the DoCmd object nor its Transfer-Spreadsheet method are members of the ADO Object Model. The DoCmd object is built into the Microsoft Access library.

© Hands-On 11-9: Linking an Excel Spreadsheet

This hands-on uses the Regions.xls spreadsheet file provided in the book's downloadable files. You can revise the procedure to use any spreadsheet file that you have available.

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Link_ExcelSheet procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Link_ExcelSheet()

Dim rst As ADODB.Recordset

DoCmd.TransferSpreadsheet acLink, _ acSpreadsheetTypeExcel8, _ "mySheet", _

CurrentProject.Path & "\Regions.xls", _ -1, _


Part II

Set rst = New ADODB.Recordset With rst

.ActiveConnection = CurrentProject.Connection .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open "mySheet", , , , adCmdTable End With

Do Until rst.EOF

Debug.Print rst.Fields(0).Value, rst.Fields(1).Value rst.MoveNext Loop rst.Close Set rst = Nothing End Sub

The Link_ExcelSheet procedure begins by creating a linked table named mySheet from the specified range of cells (A1:B15), located in the Regions worksheet in the Regions.xls file. The -1 argument in the DoCmd statement indicates that the first row of the spreadsheet contains column headings. Next, the procedure uses the ADO Recordset object to retrieve the data from the mySheet table into the Immediate window. Notice that prior to opening the Recordset object, several properties of the Recordset object must be set. The ActiveConnection property sets the reference to the current database. The CursorType property specifies how the Recordset object should interact with the data source.

The adOpenKeyset setting tells Visual Basic that instead of retrieving all the records from the data source, only the keys are to be retrieved. The data for these keys is retrieved only as you scroll through the recordset. This guarantees better performance than retrieving big chunks of data at once. The LockType property determines how to lock the data while it is being manipulated.

The adLockOptimistic setting locks the record only when you attempt to save it.

Opening the Recordset object also requires that you specify the data source. The data source in this procedure is the linked table named mySheet. The parameter passed depends on the source type used.

The adCmdTable setting indicates that all rows from the source table should be included.

You could also open the Recordset object by passing all the required parameters at once, as follows:

rst.Open "mySheet", _ CurrentProject.Connection, adOpenKeyset, _ adLockOptimistic, adCmdTable

Creating and Manipulating Databases with ADO

Was this article helpful?

0 0

Post a comment