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
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
14. Click "ODBC Database" Button
15. Select "Link to data source by creating a linked table"
16. Select Machine Data Source and choose your Salesforce DSN.
17. Select Salesforce tables to link.
18. Click OK and open a table to view the contents.
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 ...
Try Free For 15 Days
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.