Link to or import data from Salesforce

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

1. Select External Data > New Data Source > From Online Services > From Salesforce.

2. 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.

3. Select OK.

Stage 2: Enter credentials and connection string parameters

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

1. Enter a username and password in the Username and Password boxes.

2. Enter a security token in the Security Token box.

3. 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

1. 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.

   

2. 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:

1. Select External Data > Linked Table Manager.

2. 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;