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.
Some of the use cases shared by business analysts include:
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.
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.
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
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".
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 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.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.