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

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 Salesforce.com.  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 Salesforce.com 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 …

Shares
Sumit Sarkar

Sumit Sarkar

Principal Systems Engineer, Progress DataDirect at Progress Software
Sumit Sarkar

@SAsInSumit

Data Access Evangelist, Speaker and World's leading consultant on direct SQL access to Cloud Data. TDWI, Pescatarian, Simcity Celeb - I tweet my own tweets.
In release training for #DataDirect quality Impala #JDBC driver by @brodymessmer available as a single high performance JAR #notkludgy - 20 hours ago
Sumit Sarkar

4 Comments

  1. I have been searching for weeks for something like this and your solution worked exactly as advertised. Are you planning any similar tutorials for SSRS?

    Reply
    • @Gregg. Glad to hear the Salesforce POC with MS Access went well. I will look into doing an SSRS tutorial for direct real-time access to Salesforce.com. In the meantime, you can follow the SSRS documentation from Microsoft on configuring ODBC drivers since the steps also apply to the Salesforce ODBC driver.

      Reply
  2. Do you have to wait for Salesforce to run a pre-defined report to extract the data (which you then interrogate), or do you completely bypass Sf’s own reporting mechanisms and pull the data direct into Access/Excel for subsequent analysis?

    Reply
    • Terry, the Salesforce ODBC driver uses the public web service API to query data directly from the platform.

      We do expose Salesforce reports as stored procedures. Is your question related to extracting data from prebuilt reports?

      Reply

Trackbacks/Pingbacks

  1. Software Programming » Blog Archive » Importing your Text Files Into Microsoft Access - [...] inside the "paste errorstable at the erroneous records will give you a good idea of what went wrong.329 Most …
  2. 64-bit Oracle ODBC driver for Microsoft Access on Windows 7 | Data Connections - [...] running enterprises today, and this was further confirmed based on the interest in connecting Microsoft Access directly to Salesforce.com using ODBC.  …

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>