Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access Live Data in Excel


Introduction.

We have already learned a few tricks to open and work with external data sources like Access Table, dBase Files, and Excel Table without linking them permanently. We have tried VBA and Query-based methods too.

We have yet to try the ODBC connection string in Source Connect Str Property in a Query to open external data files with this method. Even though we can link external data into MS-Access manually, we will be trying the VBA-based method for linking external files later.

But, before going into all that we will try a different trick, for a change in the one-way traffic, so to speak, of data flow from external data sources into Access. But it is not exactly one-way data flow into Access we can update external data from within Access, with or without direct links.

The Data Sources that we have opened after setting Source Database and Source Connect String Properties in Queries are updatable. The changes which you make on these data sets from within MS-Access will be updated back in their respective parent Applications.

Live MS-Access Data in Excel Sheet

Here, we will try linking live MS-Access Data in Excel and see that it stays on reflecting changes made in MS-Access. MS-Access will function as the Server Application and MS-Excel will be on the Client side.

There is only one difference when compared with the earlier methods we tried, the changes that you make to Access data in Excel, which you are allowed to do, will not be updated back in Access.

Then what do we do with the Access Data in Excel? Well, you can create Charts or do calculations or whatever you like if you are more comfortable with Excel-based tasks. You can create links to the data (Copy- -> Paste Special- ->Paste Link) from other parts of the Workbook and use the data for Charts or Reports, instead of making the direct change to the linked source data. The changes that take place in Access will automatically reflect on the Report or Chart in Excel.

For bringing Access Data into Excel we need another Application, Microsoft Query, as a go-between, to connect to Access Table. The Query Wizard will guide us through the various options to link with Access when we start with the Data options in Excel.

We will use the Categories Table from Northwind.mdb sample Database for our example.

Step through the following procedure.

  1. Open a new MS-Excel Workbook.

  2. Select Cell A1 on Sheet1.

  3. Point to Import External Data in Data Menu.

  4. 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 in the Databases Tab that 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.

  5. Select MS-Access Database* from the list and click OK.

  6. The Common Dialog Control opens up, allowing you to browse to the Location of 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.

  7. 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 you can 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.

    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.

  8. After selecting the Fields Click Next. Here, you can define Filter conditions.

  9. Click Next to proceed to the Sort options.

  10. 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 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.

  11. See the Radio Button set on Return Data in Microsoft Office Excel and click Finish.

  12. 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.

The records from the Categories Table will be inserted in Excel starting from 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 you know now how simple it is.

Refreshing Updates from Access Table.

Since we are successful in bringing Access data into Excel let us do some experiments to prove that it is really live data from Access and how the changes made in Access reflect here.

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.

  1. Open the Categories Table of Northwind.mdb Database.

  2. Add Crabs, and Lobsters in the Description field of the last record. Or add a new record with some Category Name and Description.

  3. Minimize MS-Access and Display 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.

  4. Click anywhere within the data Area.

  5. Select Refresh Data from Data Menu.

    Now the changes that you have made will be updated on the Excel side too. We can tell Excel to do this job automatically at a fixed interval rather than doing it manually.

  6. Right-Click somewhere in the middle of the Linked Table and select Data Range Properties from the displayed Shortcut Menu.

  7. On this Control, you will find several options to manage the Data including the Query Name that transports the data from Access to Excel. Select the Radio Button in the Refresh Every option under the Refresh Control Option Group and set the time 1 Minute, so that we can watch the refreshing action without waiting for it for too long to happen.

  8. Open the Access window and remove the changes that we have made in the Categories Table, or make some more changes so that they are clearly visible in Excel when automatic refreshing action takes place.

  9. Open the Excel window and wait for the Refresh Action to take place. The change might have already taken place or it may happen at any time because Excel will continue to refresh the change every minute.

If you have made any changes in the Excel side of the data, then that will be lost in this process.

If you close the Excel Workbook and open it again a prompt will pop up asking you to re-confirm whether Excel should automatically do the refreshing of linked data or not. You may respond the way you want it.

We will continue our discussion on a few more points on this subject in the Next Post, instead of crowding everything in here.

  1. Roundup Function in Excel in MS-Access
  2. Proper Function in Excel in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel, Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel- 2
  11. Opening Excel Database Directly
  12. Create Excel, Word File from Access
Share:

2 comments:

  1. [...] 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 [...]

    ReplyDelete
  2. [...] 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 [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code