DAO and ODBC

Using ODBCDirect

In this exercise, you will use ODBCDirect to retrieve data from the Northwind database. The Microsoft Access ODBC driver will be used to simulate access to an ODBC data source.

* To use ODBCDirect with Microsoft Excel

1. Switch to Microsoft Excel.
2. Open a new workbook.
3. Start the Visual Basic Editor.
4. Add a new module to the workbook.
5. Add a reference to the Microsoft DAO 3.5 Object Library.
6. Add the following declarations in the General Declarations section of the module.

    Public wsODBC As Workspace Public cnODBC As Connection Public rsODBC As Recordset 

7. Create a new public sub procedure and name it LoadODBCData .
8. Type the following code into LoadODBCData :

    Dim iCounter As Integer  Set wsODBC = DBEngine.CreateWorkspace("ODBC_ws","Admin","",dbUseODBC) Set cnODBC = wsODBC.OpenConnection _ ("New",dbDriverNoPrompt,False,"ODBC;DSN=nwind") Set rsODBC = cnODBC.OpenRecordset("Employees") Range("A1").Select  iCounter = 1 Do Until rsODBC.EOF Cells(iCounter, 1).Value = rsODBC.Fields("FirstName") Cells(iCounter, 2).Value = rsODBC.Fields("LastName") rsODBC.MoveNext iCounter = iCounter + 1 Loop rsODBC.Close cnODBC.Close wsODBC.Close 

* To test your application

1. Close the Visual Basic Editor and save the workbook as Ex2.xls .
2. On the Tools menu, click Macro and then click Macros to open the Macro dialog box.
3. Select LoadODBCData from the list and then click Run .

   You should now see the list of employees entered into the spreadsheet.

4. Save changes and close the workbook and exit from Microsoft Excel running.