I encounter problems when trying to use Excel MSQuery with the User table.
As User is a reserved keyword in SQL and therefore it must be surrounded in double quotes whenever it is used in a query. Unfortunately, MSQuery removes double quotes as it thinks that they are unnecessary. This makes MSQuery incompatible with any query using the User Table. If you use the “Data Connection Wizard” instead of MSQuery, it will also be able to correctly query the User table.
Does the Salesforce Driver support SOQL
The driver supports both SQL and SOQL syntax. Because the languages are similar, there is an order of precedence for how a query is interpreted. By default, the driver will attempt SOQL first, then SQL. However, this can be can be changed in the Advanced Settings Dialog.
How do I see my reports?
Reports are currently represented by stored procedures. To query a stored procedure, use the ODBC stored procedure syntax: {call “<report name>”}
How do I connect to a sandbox?
In the DSN configuration, open the Advanced Options and enable the “Enable connection to sandbox URL” option and enter the Sandbox URL in the provided space. OK to close the Advanced Options and append the sandbox name to your username: user@organization.com.<sandbox>. Then press Test to make a connection.
Does the driver support write-back?
Yes, it supports DML (INSERT, UPDATE, DELETE), but not DML (CREATE, DROP).
Does the Driver use the Bulk API?
Yes, the driver will use the Salesforce Bulk API for queries with results between 30000 and 10 000 000 records. These numbers can be altered in Advanced Configuration Dialog.
Does the driver support Primary Key Chunking?
Yes, the driver will use Primary Key Chunking for queries that exceed 10 000 000 records. This number can be configured in the Advanced Configuration Dialog
What functionality is pushed to Salesforce?
Currently, aggregation, filters, and joins are passed to Salesforce.
Why do I get an error like “invalid operator on Id field”?
This error happens when you try to compare an Id type column to something that Salesforce does not recognize as an Id value. For example SELECT Name FROM Account WHERE Id LIKE ‘001610%’, will return an error. To work around this, you can CAST the Id column like this: SELECT Name FROM Account WHERE CAST(Id AS “VARCHAR”) LIKE ‘0016100%’. This forces the driver to handle the filter instead of passing it to Salesforce.