Refreshing Excel from Access DBs

Using MS Access to store data for use in Excel files can be a good solution because all MS Office users will already have the drivers installed.

It is also easy for inexperienced users to create queries and more experienced Excel users can learn to build ETL processes with VBA.

The process for linking database content is essentially the same for any other DBMS and I strongly recommend linking databases to Excel instead of requiring users to download and manually edit reports.

MS Access Application Overview

The data storage in Access functions a lot like SQLite but it supports concurrent editing and has more data types. It does not perform as fast as SQLite and it will struggle if there are too many concurrent users but it is good enough for many smaller applications.

I have found that building multi-user applications with MS Access can quickly become a sunk cost trap but it could be worth while in some situations. I would avoid building applications where any tables are going to be hundreds of MB, or you will regularly need more than ~10 concurrent users, or you need to allow users to enter long text.

For good examples of how to start with Access, review the built-in 'Northwind Traders' database and content from Allen Browne.

There are 2 good uses that I have found for MS Access:

1. Linking data files to Excel (see Refreshing Excel from .xlsx Files)
2. Small-Medium size ETLs for data that will be linked to Excel

In this example I will use the Northwind Traders database and show how to link a query to Excel.

Splitting the Database

Most applications have layers of separation from the actual data storage and with Access it is important to separate the data if there is any chance that it will be re-used in any way. For additional resources on splitting an Access database see Allen Browne's Tips

When Access data is being linked to Excel I have found that it is best to create a separate .accdb file to be used exclusively for linking to Excel. This is because Excel will lock the database on refresh until the Excel file is closed.

There are basically 3 types of Access files that I use and always keep separate:

1. Backend Data Storage
2. Front End User Forms (this is also where you would save a VBA ETL)
3. Excel Link File

For this Excel link file I need to create links to backend tables so below I show how to add linked data tables.

Note: It is usually best to keep the backend on a network drive and avoid using drive letter assignments (i.e. D:) so that the files can be shared with others.

Now that the tables are linked I will create queries to filter to the items that I want to link to Excel.

Here I have created a query that will display a lot of data related orders that require some action. The filter excludes items that are Allocated, Invoiced, or Shipped.

Linking the Data to Excel

Now I will link the data to the Excel file by choosing the .accdb file, the query and to link as an Excel table.

After the Excel table is created I usually like to go into Properties and disable 'Adjust column width'.

You can also click the menu icon here to view or edit the Connection Properties. In this menu you can quickly change the source by pasting a new location into the connection string or change the query by pasting a new name into the Command text field.

Once the data is linked, you can create any pivot tables and charts and all of these items can use the Refresh options.

Below I have linked to the total sales data to show how sales compared between products and between months.

This is the kind of process but many businesses ask someone to repeatedly perform and now it can be done effortlessly by just clicking refresh!

Refresh Sequence Notes

The sequence of the refresh applies to the Pivot tables before the Database links so you will need to refresh all twice to refresh everything.

If the database link refresh takes too much time then you can right click and select Refresh on any Pivot table to just refresh that kind of object. This will require a separate refresh on each type of Pivot Table (default, classic, tables linked to pivot charts, and possibly others). If it becomes too hard to keep track of then just create a VBA solution but I try to avoid using the macro enabled Excel files.