Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 32Working with Access from Excel

As terrific a product as Excel is, there can come a point when the volume of data you are working with will exceed Excel's capacity for storing records. Even with more than one million available rows starting with version 2007, some projects require a larger data management platform with Microsoft Access. If you plan to develop projects for business clients, sooner or later you'll encounter a client that uses Access for its relational database capabilities.

Using Excel VBA with the storage capabilities of an Access relational database is a powerful combination for front-end data management. This lesson offers examples for adding, retrieving, and updating records in Access data tables from the familiar comfort of your Excel workbook.

Adding a Record to an Access Table

Among the more common actions you'll do when interacting with Access from Excel is to transfer records from an Excel worksheet to an Access database table, and vice versa. Suppose there is an Access database named Database2.accdb that contains a table named Table1 with six fields. In Sheet5 of your Excel workbook, you amass records during the day that are added to Table1 at the end of the workday.

NOTE A reference to the Microsoft ActiveX Data Objects 2.8 Library is required for the code in this lesson to run. Before attempting to run the macros, get into the VBE and from the menu bar, click Tools References. Navigate to the reference for Microsoft ActiveX Data Objects ...