Connecting to a Database Using DAO

Implementing DAO and Microsoft Excel

In this exercise, you will use DAO to enter data from a Jet database into a Microsoft Excel spreadsheet.

* To make a reference to DAO in Microsoft Excel

  1. Start Microsoft Excel.
  2. On the Tools menu, click Macro and then click Visual Basic Editor .

    This will start the Office 97 Visual Basic Editor.

  3. On the Visual Basic Editor's Tools menu, click References .
  4. Select Microsoft DAO 3.5 Object Library and click OK .

* To code the data extraction

  1. On the Insert menu, click Module to add a new module to the application.
  2. In the General Declaration section, add the following declarations:
     Public db as Database Public rs as Recordset 
  3. On the Insert menu, click Procedure to open the Add Procedure dialog box.
  4. Enter the following information:
    Field Value
    Name LoadData
    Type Sub
    Scope Public

  5. Click OK .
  6. Enter the following Visual Basic code in the LoadData subprocedure:
     Dim iCounter As Integer Dim iRecordNum As Integer Set db = OpenDatabase("C:WA\Practice\NWind.mdb") Set rs = db.OpenRecordset("Employees") rs.MoveLast iRecordNum = rs.RecordCount rs.MoveFirst Range("A1").Select For iCounter = 1 To iRecordNum Cells(iCounter, 1).Value = rs.Fields("FirstName") Cells(iCounter, 2).Value = rs.Fields("LastName") rs.MoveNext Next iCounter rs.Close db.Close 

* To test your application

  1. Close the Visual Basic Editor and save the workbook as Ex1.xls
  2. On the Tools menu, click Macro and then click Macros to open the Macro dialog box.
  3. Select LoadData 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. Leave Microsoft Excel running.


Microsoft Windows Architecture Training
Microsoft Windows Architecture for Developers Training Kit
ISBN: B00007FY9D
EAN: N/A
Year: 1998
Pages: 324

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net