Synametrics Technologies
Revolving around the core of technology
Connecting to Salesforce with ODBC
Benefits
By connecting to Salesforce with an ODBC connection, your ability to work with the database will greatly improve. By doing this, it will make querying the database a much easier task. Additionally, you could use several third-party reporting and querying tools that are otherwise not available. In this article, we will go through the necessary steps on how to connect to Salesforce through ODBC. We will be using WinSQL, powerful SQL query software, to achieve this.Requirements
- Java run time 8 (JRE 8) or greater must be installed on the machine where WinSQL is running.
- Ensure java is in the path system variable. In windows, you can achieve this by modifying the system environment variables. Please see here for the necessary steps.
- Confirm the web api is enabled in Salesforce. Otherwise, you will run into the following error: api_disabled_for_org
Step 1: Register ODBC Drivers in WinSQL
Open up WinSQL and click on Help. Next, click on Register ODBC Drivers. Make sure that "WinSQL Salesforce" is registered.Step 2: Configure the Connection in WinSQL
- Open WinSQL and go to File then click on open ODBC Manager.
- Click on the "User DSN" tab.
- Click "Add".
- Select the "WinSQL Salesforce".
You will need to fill out the following fields:
| Field | Description |
|---|---|
| Data Source Name/Description | This is a friendly name you want to apply to this Data Source. |
| Host Name | Enter the Hostname of your Salesforce. By default, this is login.salesforce.com. |
| User Name | Specify the username you use to login into Salesforce. |
| Security Token | Specify the security token for Salesforce. This token is required along with the user password to access Salesforce via API. You can reset your security token by following the instructions here. |
Once you specify your credentials, click the "Test Connect" button at the bottom. A new popup will show up, where you will need to specify your User Name and Password.
Once the username and password are specified, click on OK. You will see a "Connection established" message popup if WinSQL can connect.
Running Queries and Interacting with Salesforce
Now that we are successfully connected, we can begin running queries and modifying the database.
For example, I am adding a new person named Sam who has 50 chocolate, 25 vanilla, and 15 strawberry flavored ice cream:
If I browse the data I can see that there is now the new data:
Importing Data from Excel
Once you are connected to your Salesforce in WinSQL, you will be able to see all of your tables and data within WinSQL. To begin importing data directly from Microsoft Excel, simply drag and drop the .xlsx file directly into your Tables section as seen here:
Conclusion
You can interact with Salesforce easily by using your ODBC query tool. The benefit is that you also get the entire suite of your chosen tool as well as a much more informative interface. Using WinSQL allows you to use several additional reporting and querying tools that are otherwise not available. Please see here for a list of features/benefits.
