​Microsoft Access and Data Integration

One very common data management problem is integrating and synthesizing data from multiple data sources. How many different sources of data do you have to work with? And how are all these data sources related?

​Most work places use multiple software solutions. The different software platforms may share related data, but it’s almost impossible to pull the various data sets together for analysis. And then, beyond the software solutions, there are all those spreadsheets with related data.

At some point it almost becomes overwhelming. You have all this data, out there (floating around in the cloud), but no way to really bring it all together. I see this problem, all the time.

One of the best products, on the market, for data integration is Microsoft Access. Access has a lot of advantages over other integration software products. Firstly, MS Access is part of the Microsoft Office suite. This means that it has been cultivated (for over 20 years) to work fluidly with other Microsoft products. Specific to integration, Access can link up to, and share information with:

1. Microsoft Excel
2. Microsoft Outlook
3. Microsoft SQL Server
4. Microsoft Sharepoint Lists

In addition, MS Access can also linkup to data from the following data sources:

1. Other ODBC Databases, such as MySQL, etc…
2. Text Files
3. XML Files
4. dBase Files

At first the idea of linking data from multiple data sources may be overwhelming. But, it really isn’t all that hard, once you figure out how to do it. Following are some basic guidelines when using MS Access to integrate various data sources.

Primarily – with MS Access – there are a couple ways to work with external data. The first is simply importing the external data. For instance, you can open up one of your proprietary software applications and run an export file. Then you can import that file into MS Access. Since Access can read and import common text or Excel files, importing data into Access from other software packages is very doable. But, there is another option as well.

With Microsoft Access, instead of importing data that has been exported from another software; you can actually link the other data source directly to Access. This allows you to read “live” production data.

If you open Microsoft Access and click on the External Data ribbon, you will see a group of ribbon commands labeled, “Import & Link”. These are the commands you want to play with, in order to fully grasp all the possibilities for integrating your various data sources. Each ribbon command tool comes with wizards, to walk you through the various steps.

To begin with you may simply want to play with exporting &/or linking data from Excel. Access and Excel work quite well together, as they are both part of the Microsoft Office Suite. The most important thing to remember, before you start working to import/link a spreadsheet, is to prep the spreadsheet. You’ll want to make sure there are column headings and that the spreadsheet is straight data (without sectioned data, etc…) But, after you’ve prepped your spreadsheet, simply:

1. Open up an Access database
2. Click on the External Data ribbon
3. Click the Excel command tool on the Import & Link group
4. A wizard will pop up.

• You will be able to use a navigator and select the spreadsheet you prepped.
• You can choose to import data, append data to an existing table, or link data. --- Stay away from appending data to an existing table, until you’ve played with the other options. Once you’ve played with importing and linking – you’ll feel more comfortable selecting the append option. (Do keep in mind, if you append to an existing table, you’ll want your spreadsheet data to be formatted so that it is compatible with the table you append to.)

Once you play with the differences between importing and linking data (with Excel) then you’re in a better position to start working with some of the more sophisticated data sources. To import/link data from a SQL database, you’ll need to learn how to work with ODBC data sources. ODBC connectors simply define connection strings to the database you want to read. To learn more about ODBC data sources check out this article. You’ll want to follow the instructions on adding an ODBC data source before trying to import/link to a SQL database.

After setting up an ODBC data source for the SQL database you want to connect to, then the wizards in Access can walk you through the various steps. Just make sure to save your ODBC data source in a network folder that you can find, when going through the import/linking process.

Learning how to import &/or link external files to Access, may take a bit of time. But, once you’ve figured it out, you really are in a better position to bring multiple data sources together and synthesize the data. Access comes with the ability to create queries, bring multiple tables together, relate those tables by common key fields and analyze the data. You can also build custom reports and graphs.

These guidelines provide a basic outline of possibilities. Once you start playing with the various import/link commands and wizards, you may have further questions. Feel free to send me a LinkedIn message, if you’ve further questions.

EDIT - 11/09/2016

Microsoft published some great news this past week. Microsoft Access now included in Office 365 Business and Business Premium with new enhancements. Specific to the topic of data integration, included in the Microsoft announcement, is the following:

New data sources in Access

A set of new enterprise data connectors will roll out to Microsoft Access in early 2017. These new connectors include OData Feed, Dynamics CRM, Salesforce and Amazon Redshift and will be available for customers with Office 365 ProPlus, E3 and E5 plans. These new connectors will enable customers to integrate and extend Access into other line of business solutions and databases.
This is just the beginning—there are even more new data sources on the way. In the meantime, we welcome your feedback about Access. Please share your suggestions or submit requests for desired data sources on the Access UserVoice site.

From a data integration perspective this is great news. For instance, if your organization uses Salesforce, you will be able to link directly to Salesforce data from MS Access. And Microsoft says this is "just the beginning, there are even more new data sources on the way".

​Microsoft is taking Access to a new level as a data integration tool. I've worked with MS Access for over 20 years. I've helped businesses and organizations in all sectors of the economy manage that unique "local" or "native" data that doesn't quite fit into their software applications. Microsoft's announcement that Access will now be included in O365 Business and Business Premium shows a new commitment to MS Access. Microsoft's efforts to include new data sources shows they are listening to the needs of their customers and understand the distinctive niche that MS Access holds in a workplace. All around Microsoft's latest announcement is wonderful news. Stay tuned, from the looks of things integrating your in-house data is going to start getting much easier.

More Articles About Microsoft Access​​

• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.