- Basic Excel Databases
- Importing Excel Data into Access
- Linking Excel Data into Access
- Creating an Access Form
- Using the Microsoft Accesslinks Add-In
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
What if you're working primarily in Excel but want to use Access forms or reports? In versions prior to Excel XP (2002), Microsoft included an add-in called AccessLinks (downloadable here) that added three commands to the Excel Data menu. These options allowed Access forms and reports to be created and opened from within Excel and automated the export of Excel data to Access.
No such option is available for Excel 2003, which is probably just as well; it's smoother to link a worksheet into Access and do the work of creating and using forms and reports entirely in Access, as described above. But if you work primarily in Excel 2002, the AccessLinks add-in may be useful.
CAUTION
Use AccessLinks with caution. The documentation is sparse, and there's almost no troubleshooting information on Microsoft's web site.
Follow these steps:
-
After downloading and running Acclink.exe, choose Tools, Add-Ins from the Excel menu to activate the add-in. Select Microsoft AccessLinks Add-In and click OK.
-
Selecting MS Access Form highlights the data range in the current worksheet and launches the form wizard. Creating the form is as described earlier, except that the names of the data table and form are supplied automatically. (Don't change them.)
If desired, modify the forms using the techniques described earlier.
With the add-in running, three new commands appear at the end of the Data menu when a worksheet is open (see Figure 17).
)
Figure
17NOTE
The program may appear to hang after creating the form, leaving its window minimized. If you wait long enough, though, or switch back and forth between the two applications, the form should eventually appear.
If you close Access and go back to the original Excel workbook, a macro button named View MS Access Form appears to the right of the last data column. Clicking this button launches Access and displays the form (see Figure 18).
)
Figure
18CAUTION
If Excel's macro security setting is at High (choose Tools, Security, Macro Security), the macro button doesn't work. Setting Macro Security to Medium gives you the option to allow macros whenever a workbook containing the macros is opened.
If you close the Excel file while the Access form is open, the connection is lost. (The same thing happens with manual linking, as described earlier.) You don't really need to have both files open, anyway. It's cleaner and safer to work in one application at a time.
If the Access menu items disappear from the Data menu in Excel, unload and reload the add-in. After exiting Access, you may get a message from Excel that the file is now available for editing (although clicking it doesn't do anything).
In my opinion, the AccessLinks add-in is best avoided, which is probably why it isn't available for Excel 2003.