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>>