Integrating Excel and Access

Chapter 2. Using the Excel User Interface

I first realized how powerful integrating Access and Excel could be while I worked for a company that calculated incentives. A database housed all of the data required to calculate the incentives. Before I took over the process, a report was printed from the database and rekeyed into an Excel workbook that performed all of the calculations. Eventually we moved from rekeying, to using Microsoft Query to pull data from the database, to finally having the database fill in the Excel workbook. Using an automated process not only saved time, but it also dramatically reduced errors. In the years since, I have found many more opportunities to integrate Access and Excel.

While it is tempting to jump right into using VBA to perform data functions, understanding when and how to use the Excel interface is still very useful and can provide a springboard to using VBA. When you want to use External Data from the Excel user interface , use the Import External Data function under the Data menu. From here, you can open and edit saved queries or create a new query.

Using External Data

External Data refers to any data that does not reside in Excel. Using the Import Data function on the Data menu, you can import entire tables or queries from Access and other databases. You can access this function by going to Data → Import External Data → Import Data. In addition to importing data from a database, you can also use this feature to import text files, XML files, ...