Microsoft Office Tutorials and References
In Depth Information
Lesson 29: Working with Access from Excel
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
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.
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 ➪ References. Navigate to the reference
for Microsoft ActiveX Data Objects 2.8 Library (or the highest Objects Library
number you see), select it as indicated in Figure 29-1, and click OK.