16.6. Pulling Data from Access

There are unlimited opportunities to use VBA in an application to manipulate an instance of Access to utilize data in a database solution. The Access Object Model can be managed from other applications that support VBA to enhance those applications with Access functionality. This example gathers data from an Access database into an Excel spreadsheet, which is included with the sample files for this chapter. As with all other Office applications, adding a reference to the Microsoft Access 12.0 Object Model via the VBA References dialog box in Excel's Visual Basic Editor is required to begin employing the Access feature set in a database solution.

In this example, you use Excel to start an Access database application via a button click. Open the Use Access Excel workbook file included with the sample code for this chapter. Notice that a Form button on it is opened automatically. Excel has some lightweight forms, called UserForms, which can be used much like an Access form. Although they don't have all of the properties of Access forms, they are handy and provide quite a bit of functionality. To create one of these forms and add code behind it, simply open VBE for Excel by right-clicking on a tab for any spreadsheet and selecting View Code. In this case some code is added to the Open event for the Workbook module to open the form when the Workbook is opened. Of course, the Excel workbook must have code enabled for the Open event code to run when the ...

Get Access™ 2007 VBA Programmer's Reference now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.