You can link to or import data from Salesforce, which is a customer relationship management (CRM), cloud-based business solution. For more information, see Salesforce.com.
-
When you link to data, Access creates a two-way connection that synchronizes changes to data in Access and Salesforce.
-
When you import data, Access creates a one-time, copy of the data, and so changes to data in either Access or Salesforce are not synchronized.
Note The ability to link to or import data from Salesforce is only supported in volume licensed instances 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:
-
Identify necessary connection information, including a username, password, and token. You must enter a security token, which is a case-sensitive alphanumeric code, if you try to access Salesforce from an Internet Protocol (IP) address that's outside your company's trusted IP range. For more information, see Reset Your Security Token.
-
The ODBC Driver used to access Salesforce requires 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.
-
Identify the tables that you want to link to or import. You can link to or import more than one table in a single operation.
-
Consider 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.
-
Determine 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.
-
Secure your Access database and the connection information it contains by using a trusted location and an Access database password. For more information, see Decide whether to trust a database and Encrypt a database by using a database password.
-
Plan for making additional 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
-
Select External Data > New Data Source > From Online Services > From Salesforce.
-
Do one of the following:
-
To import, select Import the source data into a new table in the current database.
-
To link, select Link the data source by creating a linked table.
-
-
Select OK.
Stage 2: Enter credentials and connection string parameters
In the Microsoft Access – Connect to Salesforce dialog box, do the following:
-
Enter a username and password in the Username and Password boxes.
-
Enter a security token in the Security Token box.
-
Optionally, enter one or more connection parameters in the Additional Connection String Parameters box.
For more information, see Connection Parameters.
Stage 3: Select Tables to link to or import
-
In the Link Tables or Import Objects dialog box, under Tables, select each table that you want to link or import, and then click OK.
-
In a link operation, decide whether to select Save Password.
Security Selecting this option eliminates the need to enter credentials each time you open Access and access the data. But, this stores an unencrypted password in the Access database, which means people who can access the source contents can see the user name and password. If you select this option, we strongly recommend storing the Access database in a trusted location and creating 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)
-
In the Get External Data - Salesforce Database dialog box, you can save the import steps as a specification and create an Outlook task to automate the import operation on a regular basis. For more information, see Save the details of an import or export operation as a specification.
Results
When a link or import operation completes, the tables appear in the Navigation Pane with the same name as the Salesforce table or view. During an import operation, if that name is already in use, Access appends "1" to the new table name. But you can rename the tables to something more meaningful.
In an import operation, Access never overwrites a table in the database. Although you cannot directly append Salesforce data to an existing table, you can create an append query to append data after you have imported data from similar tables.
In a link operation, if columns are read-only in the Salesforce table, they are also read-only in Access.
Tip To see 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 change data types in a linked table. If you want to make design changes, do it in Salesforce. To see the design changes in Access, update the linked tables:
-
Select External Data > Linked Table Manager.
-
Select each linked table you want to update, select OK, and then select Close.
Connection Parameters
The following table describes the connection parameters you can enter in the Additional Connection String Parameters box of the Microsoft Access – Connect to Salesforce dialog box.
Separate two or more parameters with a semicolon. Use the following syntax examples as a guide:
UID=myaccount;BulkBatchSize=9000;UseNumeric=1;
| Key Name | Description | Default value | Required |
| PWD | The password corresponding to the user name that you provided in the Username field (the UID key). | None | Yes |
| UID | The user name for your Salesforce account. | None | Yes |
| AutoLogout | When 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. | 1 | No |
| BulkBatchSize | The maximum number of rows contained in a single Bulk API call when executing DML. The maximum value is 10000 | 5000 | No |
| CERTSPATH | The 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 driver | The cacerts.pem file in the \lib subfolder within the driver's installation directory. | No |
| METADATALEVEL | When 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 |
| PARSEMETHOD | The 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 First, Then SQL (2 or SOQL_ FIRST) | No |
| PROXYHOST | The host name or IP address of a proxy server that you want to connect through. | None | Yes, if connecting through a proxy server. |
| PROXYPORT | The number of the port that the proxy server uses to listen for client connections. | None | Yes, if connecting through a proxy server. |
| PROXYPWD | The password that you use to access the proxy server. | None | Yes, if connecting to a proxy server that requires authentication. |
| PROXYUID | The user name that you use to access the proxy server. | None | Yes, if connecting to a proxy server that requires authentication. |
| QueryAll | When 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. | 0 | No |
| SANITIZECATALOGNAME | When 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 |
| URL | The URL for connecting to a Salesforce sandbox. | None | No |
| UseAnalyticAPI | When 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 |
| USELABEL | When 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 |
| UseNumeric | When 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 |
| UseWVarChar | This 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 |
No comments:
Post a Comment