Link to or import data from Salesforce

Applies to: Access for Microsoft 365, Access 2019.

You can link to or import data from Salesforce, a customer relationship management (CRM), cloud-based business solution. For more information, see Salesforce.com.

  • Once you link to data, Access forges a two-way connection that synchronises changes to data in Access and Salesforce.
  • After you import data, Access produces a single-use, copy of the data, and so changes to data in either Access or Salesforce are unsynchronised.

Connecting from Access to Salesforce

Note:

The ability to link to or import data from Salesforce is only supported in volume licenced versions of Access 2019 or the following Microsoft 365 Enterprise plans: Microsoft 365 Apps for enterprise, Office 365 Enterprise E3, and Office 365 Enterprise E5. For more information, see Compare Microsoft 365 Enterprise Plans.

Before you begin

Want things to go smoother? Then make the following preparations before you link or import:

  • Clarify necessary connection information, including a username, password, and token. You must specify a security token, which is a case-sensitive alphanumeric code, if you try to access Salesforce from an Internet Protocol (IP) address that’s external to your company’s trusted IP range. For more information, see Reset Your Security Token.
  • The ODBC Driver used to enter Salesforce needs a Salesforce account based on the Developer Edition, Professional Edition, Enterprise Edition, or Unlimited Edition. To use this driver, you must have API access enabled. For more information, see Control Individual API Client Access to Your Salesforce Org.
  • Confirm the tables that you want to link to or import. You can link to or import more than one table in a single operation.
  • Decide the number of columns in each table. Access does not support more than 255 fields in a table, so Access links or imports only the first 255 columns.
  • Set the total amount of data being imported. The maximum size of an Access database is two gigabytes, minus the space needed for system objects. If Salesforce contains large tables, you might not be able to import them all into a single Access database. In this case, consider linking to the data instead of importing.
  • Plan for generating extra relationships. Access links to or imports selected tables but not any related tables in the Salesforce data model. For more information on this data model, see Data Model Overview.Access does not automatically create relationships between these related tables. You can manually create the relationships between new and existing tables by using the Relationships window. For more information, see What is the Relationships window? and Create, edit or delete a relationship.

Stage 1: Get started

  1. Choose External Data > New Data Source > From Online Services > From Salesforce.
  1. Do one of the following:
  • To import, click Import the source data into a new table in the current database.
  • To link, press Link the data source by creating a linked table.
  1. Click OK.

Stage 2: Enter credentials and connection string parameters

In the Microsoft Access – Connect to Salesforce dialogue box, do the following:

  1. State a username and password in the Username and Password boxes.
  1. Input a security token in the Security Token box.
  1. Optionally, type one or additional connection parameters in the Additional Connection String Parameters box. For more information, see Connection Parameters.

Stage 3: Select Tables to link to or import

  1. In the Link Tables or Import Objects dialogue box, beneath Tables, choose each table that you want to link or import, and then select OK.
List of tables to link or import
  1. In a link operation, decide whether to select Save Password. Security  – Choosing this option dilutes the necessity to type credentials constantly whenever you launch Access and gain entry to that data. But, this preserves an unencrypted password in the Access database, meaning that people with view permissions for the source contents can actually notice the user name and password. If you confirm this option, we strongly advise storing the Access database in a trusted location and making an Access database password. For more information, see Decide whether to trust a database and Encrypt a database by using a database password.

Stage 4: Create specifications and tasks (Import only)

Results

After a link or import operation finishes, the tables emerge in the Navigation Pane with the same name as the Salesforce table or view. Amid an import operation, if that name is already in use, Access applies “1” to the new table name. But you can rename the tables to a more discernible name.

In an import operation, Access never amends a table in the database. Although you cannot manually transfer Salesforce data to an existing table, you can produce an append query to append data following you importing data from associated tables.

In a link operation, if columns are read-only in the Salesforce table, they will also be read-only in Access.

Tip:

To view the connection string, hover over the table in the Access navigation pane.

Update the linked table design

You can’t add, delete, or modify columns or alter data types in a linked table. If you want to set design changes, do it in Salesforce. To track the design changes in Access, update the linked tables:

  1. Pick External Data > Linked Table Manager.
  1. Choose each linked table you want to update, press OK, and then click Close.

Connection Parameters

The following table summarises the connection parameters you can type in the Additional Connection String Parameters box of the Microsoft Access – Connect to Salesforce dialogue box.

Split two or further parameters with a semicolon. Use the following syntax examples as a guide:

UID=myaccount;BulkBatchSize=9000;UseNumeric=1;

Key NameDescriptionDefault valueRequired
PWDThe password corresponding to the user name that you provided in the Username field (the UID key).NoneYes
UIDThe user name for your Salesforce account.NoneYes
AutoLogoutWhen this option is enabled (1), the Salesforce connection is logged out when the driver closes the connection.

When this option is disabled (0), the Salesforce connection is not logged out when the driver closes the connection.
1No
BulkBatchSizeThe maximum number of rows contained in a single Bulk API call when executing DML. The maximum value is 100005000No
CERTSPATHThe full path of the PEM file containing trusted CA certificates for verifying the server. If this option is not set, then the driver defaults to using the trusted CA certificates PEM file installed by the driverThe cacerts.pem file in the \lib subfolder within the driver’s installation directory.No
METADATALEVELWhen this option is enabled (the key is set to 1 or LIGHT), the driver infers metadata based on a small sampling of data rather than all of the data.

When this option is disabled (the key is set to 0 or FULL), the driver infers metadata based on all of the data.
Clear (0 or FULL)No
PARSEMETHODThe query language that the driver uses to parse queries. Select one of the following settings, or set the key to one of the values in the parentheses:

Attempt to Parse Queries as SOQL Only (0 or SOQL_ONLY)Attempt to Parse Queries as SQL Only (1 or SQL_ONLY)

Attempt to Parse Queries as SOQL First, Then SQL (2 or SOQL_FIRST)

Attempt to Parse Queries in SQL First, Then SOQL (3 or SQL_FIRST)
Attempt to Parse Queries as SOQL First, Then SQL (2 or SOQL_ FIRST)No
PROXYHOSTThe host name or IP address of a proxy server that you want to connect through.NoneYes, if connecting through a proxy server.
PROXYPORTThe number of the port that the proxy server uses to listen for client connections.NoneYes, if connecting through a proxy server.
PROXYPWDThe password that you use to access the proxy server.NoneYes, if connecting to a proxy server that requires authentication.
PROXYUIDThe user name that you use to access the proxy server.NoneYes, if connecting to a proxy server that requires authentication.
QueryAllWhen this option is enabled (1), Salesforce users will be able to conduct searches for deleted records by including the parameter isDeleted=true.

When this option is disabled (0), users will not be able to search for deleted records.
0No
SANITIZECATALOGNAMEWhen this option is enabled (1), the driver modifies catalog names by removing all invalid SQL-92 identifier characters and replacing all spaces with underscores.

When this option is disabled (0), the driver does not modify catalog names.
Clear (0)No
URLThe URL for connecting to a Salesforce sandbox.NoneNo
UseAnalyticAPIWhen this option is enabled (1), the driver executes reports using the Analytics API.

When this option is disabled (0), the driver executes reports via URL.
Selected (1)No
USELABELWhen this option is enabled (1), the driver uses the field names and labels from Salesforce as the names and labels in the returned data, respectively.

When this option is disabled (0), the driver uses the field names from Salesforce as both the names and the labels in the returned data.
Clear (0)No
UseNumericWhen this option is enabled (1), the driver returns data as SQL_NUMERIC data instead of SQL_DOUBLE data.

When this option is disabled (0), the driver returns data as SQL_DOUBLE data.
Clear (0)No
UseWVarCharThis option specifies how data types are mapped to SQL. When this option is enabled (1), the driver returns data as SQL_WVARCHAR data instead of SQL_VARCHAR data.

When this option is enabled (1), the driver returns data as SQL_VARCHAR data.
Clear (0)No

Leave a Reply

%d bloggers like this: