DataWright Information Services
Consulting and Resources for Excel and Access
Using ADO to transfer data between Excel and Access
- Download the sample file (51,474 bytes)
- If you can't create the database, download this one (8558 bytes)
Excel is a great application for analysing data. It is not so good at storing and collating large and complex data sets, something for which Access is much better suited. By using the two together you can harness the strengths of both applications, and build some powerful tools for your business. There are a number of ways to transfer data between the two, including linking Excel worksheets into Access and using the Excel Query tool to retrieve data. Both of these options have limitations. The main one is that neither allows 2-way traffic; for example, if you pull data into Excel using the Query tool, you can't change the data in Access, and you can't edit linked Excel data with Access since Service Pack 2 of Office XP.
If you use code instead, it is possible to transfer one or many records between the two applications, and to update Access with changes made in Excel. There are two main object models for communicating with Access; DAO, which is optimised for working with the Jet database engine, and the newer and more generic ADO. They are quite different, so because ADO can also be used for transferring data to enterprise databases like SQL Server and Oracle, this tutorial will use ADO. There are a number of parts to the tutorial, all of which use the same Excel download file. They are:
- Part 1: Create an Access database and table
- Part 2: Create multiple records in the new Access table
- Part 3: Add a field to the table, and update the existing records with new data
- Part 4: Download the entire table into Excel
- Part 5: Use an Excel validation list to filter records from Access
- Part 6: From Excel, update a single record in the Access table
- Part 7: Delete a field in the Access table
I suggest that you download the Excel sample file and run through the tutorial in sequence to build and check the functionality. You can go back and adapt any of the sections for later use.
