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

Posted on 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

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.


Comments are disabled in preview mode.

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation