XL: Using DAO to Join Tables of Different Formats

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

The versions of Microsoft Excel listed at the beginning of this article provide Data Access Objects (DAO) for Visual Basic to allow you to access external databases. Microsoft Query and the ODBC add-in (Xlodbc.xla) do not provide a direct means for you to join tables of different database formats. Using DAO, you can join tables of different database formats by attaching the tables to a Jet database. An attached table, or linked table, is a table in another database linked to a Microsoft Jet database. Data for attached tables remains in the external database.

This article provides an example of attaching tables of different database formats to a Jet database so that the tables may be joined.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Product Support Services (PSS) Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The example macro below attaches two tables to a newly created Jet database (Temp.mdb). One table is called Orders and is in the Microsoft Excel format and the other table is called Employee and is in the dBASE IV format.

The dBASE IV file used in this example is the sample file Employee.dbf which is included with Microsoft Query. The default locations for the sample dBASE IV files:

   Microsoft Excel 7.0
   C:\Program Files\Common Files\Microsoft Shared\MSQuery

   Microsoft Excel 97
   C:\Program Files\Microsoft Office\Office

1. In Microsoft Excel, create a new workbook.

2. Enter the following information in cells A1:E3 on Sheet1:

   A1: Order_ID B1: Custmr_ID C1: Employ_ID D1: Order_Date E1: Order_Amt

      A2: 88000    B2: WALNG     C2: '111      D2: 1/1/97     E2: 111.00
      A3: 88001    B3: HIGHG     C3: '333      D3: 1/2/97     E3: 222.00
   
      NOTE: Include the apostrophe before the numbers in cells C2 and C3
      as shown to convert the values to text.
   
   

3. Select cells A1:E3 to select the database. On the Insert menu, point to Name, and then click Define. Type "Orders" (without the quotation marks), and click OK.

4. Save the workbook as C:\My Documents\Orders.xls.

5. Close the workbook.

6. Create a new workbook.

7. Microsoft Excel 97 ------------------ Press ALT+F11 to activate the Visual Basic Editor. Click Module on the Insert menu. Click References on the Tools menu. Select Microsoft DAO 3.5 Object Library and click OK.

   Microsoft Excel 7.0 ------------------- Click Module on the Insert menu. Click References on the Tools menu. Select the Microsoft DAO 3.0 Object Library and click OK.

8. Type the following code on the module sheet:

   Sub JoinTables()
   
   

   'Create a temporary Jet database called Temp.MDB
   Set db = createdatabase("C:\My Documents\Temp.mdb", dbLangGeneral)

   'Attach the Excel table "Orders" from the file Orders.xls to the
   'database

   Set OrdersTable = db.CreateTableDef("Orders")
   OrdersTable.Connect = "Excel 5.0;DATABASE=C:\My Documents\ORDERS.XLS"
   OrdersTable.SourceTableName = "Orders"
   db.TableDefs.Append OrdersTable

   'Attach the dBASE IV table "Employee" to the database
   '** Note: You may need to change the path to the sample dBASE
   '   files for your installation of Excel.
   Set EmpTable = db.CreateTableDef("Employee")
   EmpTable.Connect = _
         "dBASE IV;DATABASE=C:\Program Files\Microsoft Office\Office"
   EmpTable.SourceTableName = "Employee"
   db.TableDefs.Append EmpTable

   'Create the recordset -- Return the Order_ID from the Orders
   'table and

   'The First_name and Last_Name from the Employee table where the
   'Employ_ID in the Employee table matches the Employ_ID in the Orders
   'table

   Set rs = db.OpenRecordset("SELECT orders.ORDER_ID, " & _
      "employee.FIRST_NAME, employee.LAST_NAME FROM employee, orders " & _
      "WHERE employee.EMPLOY_ID = orders.EMPLOY_ID", dbOpenDynaset)

   'Copy the recordset to Sheet1!A1

   Sheets("Sheet1").Range("A1").CopyFromRecordset rs

   'Close the database and delete the database file Temp.mdb

   db.Close
   Kill "c:\my documents\temp.mdb"

End Sub