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.