MS-Access Live Data in Excel ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We have already explored several techniques for accessing and working with external data sources—such as Access tables, dBase files, and Excel worksheets—without permanently linking them. These included both VBA-based and query-based approaches.

However, we have not yet experimented with using an ODBC connection string in the SourceConnectStr property of a query to open external data files. While linking external data to Access manually is straightforward, we will later explore how to perform this linking dynamically through VBA.

Before diving into that, let’s look at something slightly different—a reversal of the usual “one-way traffic” of data flow from external sources into Access. Interestingly, the data flow isn’t truly one-way: we can also update external data directly from Access, whether or not those data sources are permanently linked.

When we use queries configured with SourceDatabase and SourceConnectStr properties to open external data, the resulting datasets are fully updatable. Any changes made to these records within Access are automatically reflected in their original parent applications.

Live MS-Access Data in an Excel Sheet

In this section, we will explore how to link live Microsoft Access data into Excel so that any updates made in Access are automatically reflected in Excel. In this setup, Access functions as the server application, while Excel acts as the client.

There is, however, one important distinction from our earlier methods: although you can edit the linked data in Excel, those changes will not be written back to Access.

So, what’s the purpose of having Access data in Excel? You can use it to create charts, perform calculations, or prepare analytical reports—especially if you’re more comfortable working in Excel. You can also create links (using Copy → Paste Special → Paste Link) to reference this live data in other parts of the workbook. This way, any updates in Access will automatically appear in your reports or charts within Excel, ensuring your information always stays current.

To bring Access data into Excel, we use Microsoft Query, which serves as the intermediary between the two applications. The Query Wizard will guide us through the necessary steps to connect Excel with the Access database.

For our example, we’ll use the Categories table from the Northwind.mdb sample database.

Step through the following procedure.

Open a new MS-Excel Workbook.
Select Cell A1 on Sheet1.
Point to Import External Data in the Data Menu.
Select New Database Query from the displayed menu.
Now, the Microsoft Query Wizard opens up and displays a Dialog Box. It displays the Database Sources list in the Databases tab, which you can link to MS-Excel. This is a combined list of items appearing in the ODBC Dialog Control User DSN, System DSN, and File DSN Tabs.
Right-click to open Large Image in New Tab/Window.
Select the MS-Access Database* from the list and click OK.
The Common Dialog Control opens up, allowing you to browse to the Location of the MS-Access Database and select it. Find the sample database C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb (MS-Access 2003, you can drop 11 from Office11 in the location address for Access 2000), select and click OK.
Select the Categories Table from the Available Tables and Columns in the Query Wizard and click on the > symbol to select all the Fields of the Categories Table into the Columns in your Query Control. If you don't need all the fields from the Source Table, then expand the Categories Table by clicking on the + symbol to display all the Fields and select only those you need and move them to the right side panel.
Right-click to open Large Image in New Tab/Window.

Before you move the field to the right, you can preview the Field contents by clicking the Preview Now button below. Memo Field or OLE Object field contents cannot be previewed this way.
After selecting the Fields, click Next. Here, you can define Filter conditions.
Right-click to open Large Image in New Tab/Window.
Click Next to proceed to the Sort options.
Right-click to open Large Image in New Tab/Window.
Click Next to move to the Finishing point.
Here, we have the option to save the selected settings in a Microsoft Query (which is an external File) at the location C:\Documents and Settings\User\Application Data\Microsoft Queries\. If we need any changes in the data selection options, then we can open this saved file in Microsoft Query and edit the Query Definition in SQL Window.
Right-click to open Large Image in New Tab/Window.
See the Radio Button set on Return Data in Microsoft Office Excel and click Finish.
In the next Dialog Control, you can select the Location on the Excel Sheet where you want to insert the data from Access. Since we have already selected Cell A1 as the target location in Step 2 above, this will appear as the default location in the control; click OK without change.

Right-click to open Large Image in New Tab/Window.

The records from the Categories Table will be inserted in Excel, starting from the range address A1.

It was a long journey from Access to Excel. Bringing Excel data into Access needs only two property changes in MS-Access Query, and now you know how simple it is.

Refreshing Updates from Access Table.

Now that we have successfully brought Access data into Excel, let’s perform a few simple experiments to confirm that it is indeed live data—directly linked to the Access database. We’ll also observe how any changes made in Access are automatically reflected in Excel, demonstrating the dynamic connection between the two applications.

There are two methods to refresh Access Data in Excel: Manual and Automatic.

Keep the Northwind.mdb sample database open so that we can make changes in the linked table in Excel or in Access and check the results of the change in both Applications.

Open the Categories Table of the Northwind.mdb Database.
Add Crabs and Lobsters in the Description field of the last record. Or add a new record with some Category Name and Description.
Minimize MS-Access and display the Excel Window, and check whether the change has taken place immediately in the linked data in Excel. You may not find any change on the Excel side. We have to refresh the data in Excel to reflect the changes.
Click anywhere within the data Area.
Select Refresh Data from the Data Menu.
Now, any changes you make in Access will be automatically updated on the Excel side as well. Moreover, you can configure Excel to refresh the linked data automatically at regular intervals, eliminating the need to perform manual updates.
Right-click anywhere within the linked table in Excel and select Data Range Properties from the shortcut menu.
In the dialog box that appears, you will find several options to manage the linked data, including the name of the query that retrieves data from Access into Excel. Under the Refresh Control section, select the Refresh every option and set the interval to 1 minute. This allows you to observe the automatic refresh in action without waiting too long.
Next, switch to the Access window and either undo the earlier changes made to the Categories table or make new edits that will be easily noticeable in Excel once the data refreshes.
Return to Excel and wait for the refresh to occur. You should soon see the updates reflected in the worksheet—Excel will continue to refresh the data automatically at one-minute intervals.

If you have made any changes to the data on the Excel side, those modifications will be lost during the refresh process.

When you close and reopen the Excel workbook, a prompt will appear asking whether Excel should automatically refresh the linked data. You can choose to enable or disable this feature according to your preference.

We will continue our discussion on this topic in the next post, rather than crowding all the details here.

MS Access and MS Excel Data conenctionJuly 6, 2011 at 1:59 AM
[...] send the Worksheets to the Users. Ref.: MS-Access Live Data in Excel __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]
Fetch Data From MS-AccessAugust 27, 2011 at 10:50 PM
[...] data refreshed every time change happens in access without writing VBA Code. The Reference link: Access Live Data in Excel. __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]