Excel® VBA: 24-Hour Trainer

Chapter 29. Working with Access from Excel

As terrific a product as Excel is, there will likely 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 Database1.accdb that contains a table named Table1 that has eight fields. In Sheet4 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, click Tools

Figure 29.1. FIGURE 29-1

To automate the daily task of transferring the day's ...