Data Access in Non-Access Applications

Recall that ODE offers three routes to data: the Data Environment designer, the ADO Data Control, and the ADO object model. You might initially feel most comfortable with the ADO object model route. (See Chapter 2 for an introduction to the ADO object model; other chapters offer additional details). The following sample populates an Excel worksheet with ADO Connection and Recordset objects. It copies the field names from the Northwind Products table to the first row of an Excel worksheet. Then it copies field values for successive rows from the recordset down the worksheet. The sample runs from the VBA project associated with a workbook file.

Sub northwind2XL() Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Dim RowCnt, FieldCnt As Integer 'Create instances of the connection and recordset objects.     Set cnn1 = New ADODB.Connection     Set rst1 = New ADODB.Recordset 'Set cnn1.ConnectionString to Northwind DSN.     cnn1.ConnectionString = "DSN=Northwind" 'Open connection and recordset.     cnn1.Open     Set rst1.ActiveConnection = cnn1     rst1.Source = "Select * FROM Products"     rst1.Open , , adOpenStatic, adLockOptimistic 'Write in column headings in first row.     RowCnt = 1     For FieldCnt = 0 To rst1.Fields.Count - 1         Cells(RowCnt, FieldCnt + 1).Value = _             rst1.Fields(FieldCnt).Name         Rows(1).Font.Bold = True     Next FieldCnt     'Fill rows with records, starting at row 2.     RowCnt = 2     While Not rst1.EOF         For FieldCnt = 0 To rst1.Fields.Count - 1             Cells(RowCnt, FieldCnt + 1).Value = _                 rst1.Fields(FieldCnt).Value         Next FieldCnt         rst1.MoveNext         RowCnt = RowCnt + 1     Wend End Sub

The connection string for the Connection object relies on a DSN object. The DSN in the sample points at the Northwind database, but it can specify any DSN . Using a DSN simplifies the design of the connection string syntax because you can use the graphical procedure from the ODBC icon in the Control Panel to specify the connection to a database. Also notice the standard syntax for specifying a connection and basing a recordset on it.

The procedure applies the Cells property, which implicitly refers to the ActiveWorksheet object in the Excel object model. You specify individual cells of a worksheet by denoting a row index followed by a column index. Setting the index values inside of loops lets the code move across columns within a row and down the rows of a worksheet.