Microsoft KB Archive/213820

= XL2000: How to Use Data Access Objects to Join Tables of Different Formats =

Article ID: 213820

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213820





SUMMARY
Microsoft Excel 2000 provides Data Access Objects (DAO) for Microsoft Visual Basic to allow you to access external databases. Microsoft Query and the Open Database Connectivity (ODBC) add-in (Xlodbc.xla) do not provide a direct means for you to join tables of different database formats. By 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 how to attach tables of different database formats to a Jet database so that the tables may be joined.



MORE INFORMATION
Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

The following sample macro attaches two tables to a newly created Jet database (Temp.mdb). One table is called Orders, and is in the Microsoft Excel format. 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. You can find the sample dBASE IV files in the following default location:

C:\Program Files\Microsoft Office\Office

To create the Excel file used in this example, follow these steps:  In Excel, create a new workbook.  Type the following data 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.  Select cells A1:E3. On the Insert menu, point to Name, and then click Define. Type Orders, and then click OK. Save the workbook as C:\My Documents\Orders.xls. Close the workbook. Create a new workbook.</li> Press ALT+F11 to start the Visual Basic Editor, and then on the Insert menu, click Module.</li> On the Tools menu, click References. Select Microsoft DAO 3.6 Object Library, and then click OK.</li>  Type the following code in the module sheet: Sub JoinTables Dim db As database Dim rs As recordset Dim OrdersTable As tabledef, EmpTable As tabledef '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 </li></ol>

Additional query words: XL2000

Keywords: kbdtacode kbhowto kbinfo kbprogramming KB213820

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.