Microsoft Access and Data Connectors

Data connectors are the first tool of any integration project. Without data connectors it’s not possible to join information across multiple intelligence sources.
 
Some of the best Microsoft Access features are its built-in data connectors. Microsoft Access was part of the Microsoft Office Suite from the very beginning. Because of this Microsoft has always provided information integration capacities. With just a few clicks it is currently possible to connect to information stored in:

• Other Access database files
• Excel files
• Outlook
• Sharepoint Lists
• dBase Files
• Text or csv files
• Dynamics 365
• Salesforce
• ODBC Databases like SQLServer

 
That Microsoft provides point-click capabilities for data connectivity is no small matter. The ease with which a user can simply connect to data from multiple sources is one of the biggest reasons I recommend Access to clients considering an integration project.
 
Even if you can’t find a native data connector within Access for your data integration project, it is still possible to purchase data connectors. One of the most common ways of joining two data sources is by using ODBC data connectors. ODBC stands for “Open Database Connectivity”. The ODBC interface is designed for industry standard relational databases and is used quite often in integration projects.
 
Most often when my clients need data connection capabilities beyond those built into Access, they need a specialized ODBC connector. When this happens my first stop is CData.com. CData sells specialized ODBC connectors for dozens of specialized software/database products. Following is just a partial list of ODBC data connectors sold by CData.com.
 

• Quickbooks
• Mail Chimp
• Sage software
• Google Contacts
• Gmail
• OData Services
• Google Sheets
• MySql
• SQL Postgre

 
CData connectors don’t just allow users to read information from multiple sources. They also make it possible to edit information. For instance, if you used the CData Quickbooks ODBC connector, you would be able to update Quickbooks customer data from Microsoft Access.  Most of the time folks request integration capabilities it is for the purpose of integrating customer/client information stored across multiple platforms. They may have customer information in Quickbooks, Outlook, Excel, their CRM software and a specialized Access database.
 
The use of ODBC data connectors makes it possible to compare customer contact information across these various platforms and synch customer names, addresses, emails, etc… Of course it is possible to integrate other types of information as well. But integration first requires the ability to connect with multiple data sources. This is why data connectors are so important.
 
To find the native Microsoft Access data connectors just click the External Data ribbon. As you can see there are two groups on the ribbon, “Import & Link” and “Export”. The difference between “import” and “link” is important. When you “link” to external data you will always be accessing production data. “Importing” data merely copies the current information into Access.

Most of the time integration projects require “linking” to production data. During the process of connecting to outside data you’ll be asked if you want to “link” or “import”. If you want production data, choose “link”. If you merely want a one-time copy of the information choose “import”.
 
Start testing with Excel connection capabilities. This will allow you to get comfortable with the resulting wizards. After you’ve played with the Excel connection capabilities, you’re in a better position to start testing and working with more advanced data connectors. Consider all the data sources in your workplace. Which ones would benefit from a data integration project?

More Articles About Microsoft Access​​

• Using Microsoft Access for Data Evolution Projects
• 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.