Link or Import data in real time to Microsoft Access in 15 minutes

Link or Import data in real time to Microsoft Access in 15 minutes

April 17, 2012 0 Comments

With all the next generation technology available today from business intelligence, operational responsiveness and cloud computing; organizations still rely on Microsoft Access.  From what Salesforce shops are telling me, there are very compelling use cases from SMBs to the Fortune 100 for connectivity from MSAccess to  And I can relate since I manually run weekly reports using Microsoft Excel to join Salesforce objects without relationships.

Why are organizations using MS Access for integration to Salesforce?

Some of the use cases shared by business analysts include:

  • Target customer marketing campaign
  • Create invoices from contacts
  • Run ad hoc SQL queries

And it's common that these analysts from finance, marketing and sales have enterprise business intelligence solutions running the very same DataDirect Connect XE for ODBC Salesforce drivers on large multi core servers.  However, these users are just more productive with MS Access since they can get their data without having to engage a hierarchy of business and IT approvals.

Get started with real time Salesforce integration with MS Access 2010 in 15 minutes:

1. Determine whether your MS Access is 32-bit or 64-bit, and download the free 15 day trial of the DataDirect Connect or Connect64 XE for ODBC driver.

2. Go to Start > All Programs > Progress DataDirect Connect and Connect XE for ODBC 7.0 > ODBC Administrator

3. Click on User or System DSN and click 'Add'

4. Select 'DataDirect 7.0 Salesforce'

5. Enter Data Source Name as 'Salesforce'

6. Click on the 'Advanced' tab, and enter the following properties in the "Extended Options" field: WorkArounds=16777216;WorkArounds2=8192

Salesforce ODBC Data Source

7. Click test-connect

8. For user, enter your Salesforce account e-mail address

9. For password, enter the password + security token (if required)

10. Click OK and you should see the "connection succeeded" dialog

11. Launch MS Access 2010

12. Select "Blank Database" template and create access database

13. Click on External Data

MS Access ODBC to Salesforce

14. Click "ODBC Database" Button

15. Select "Link to data source by creating  a linked table"

Access Link to Salesforce

16. Select Machine Data Source and choose your Salesforce DSN.

Salesforce machine data source

17. Select Salesforce tables to link.

Salesforce objects returned to ms access

18. Click OK and open a table to view the contents.

Salesforce Account data linked to ms access

How to fix common issues with MS Access and integration:

1. System error 126 or 193 on test connect

This means the jvm.dll in the JRE of matching architecture (32-bit or 64-bit) cannot be loaded from the system path.  Search the knowledgebase for '126 and salesforce' for additional articles.

2. error -7711

This error is thrown when a warning or error is coming back from the driver for which the length is too long for MS Access to display.  It is recommended to generate an ODBC trace log to determine the error.  One example is documented here.

3. reserved error (-7748): there is no message for this error

Verify step #6 was followed above.

4. #deleted returned for all fields from import

Verify step #6 was followed above.

5. Error is thrown after selecting data source: [DataDirect][ODBC Salesforce driver][Salesforce]Unable to create local database file: C:sandbox.config The cause: C:sandbox.config (Access is denied)  This error often caused by the driver not having write access to the target directory.

Run MSACCESS.EXE "As Administrator".  The executable is located here by default, "C:Program FilesMicrosoft OfficeOffice14MSACCESS.EXE"; and run "As Administrator".

I am feeling a little threatened now:

Once you're successful with your project, we encourage you to share with your business intelligence and data warehousing groups how you personally solved Salesforce integration using DataDirect Connect XE for ODBC Salesforce.  As an IT professional, I am feeling a little threatened now that Business Managers are deploying CRMs in the cloud; and Business Analysts are solving the complex integration challenges across the entire organization!

And please share your use case with a blog comment ...

Try Free For 15 Days

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Comments are disabled in preview mode.
Latest Stories in
Your Inbox
More From Progress
The New Mobile Development Landscape
Download Whitepaper
IDC Spotlight Sitefinity Thumbnail
Choosing the Right Digital Experience Platform to Improve Business Outcomes
Download Whitepaper
The Fastest Way to Build Mobile Apps With Cloud Data
Watch Webinar