DataWright Information Services

Consulting and Resources for Excel and Access

Using ADO to transfer data between Excel and Access

Updated Feb 2013: This tutorial was written for 2003 and earlier versions, and the original file will not work with the newer .accdb database format. There is an updated version that works with .accdb; download the file that suits your needs. The only change required to make the code work in the newer version was to modify the Provider property for the ADOX catalog and the ADODB connection.

  • For .mdb version: Provider=Microsoft.Jet.OLEDB.4.0
  • For .accdb version: Provider=Microsoft.ACE.OLEDB.12.0

There is some extra functionality in the Excel files: you can now make a multiple selection when downloading data by region (Part 5 in the tutorial)

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:

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.

Next: Part 1>>