As discussed, below is a screenshot of the message I get when trying to execute an insert request in MS Access VBA. (I have administrator rights to SF, which include full read/write permissions.) I’m guessing that it has something to do with the format of the following SQL request and/or SF required fields. Can anyone help with this issue?
INSERT INTO External_Working_Group_Member__c (Contact__c, Deal__c, Role__c, Company__c, Contribution_Value__c, AS_Team_Leader__c) VALUES ('003f000000D7mvnAAB', 'a08f0000001EycfAAC', 'AS Investment Team', '0013000000gerHNAAY', 'Not Assessed', True);
Remove __c from the column names, by default the Salesforce driver strips off the custom suffix.
The __c should be removed from table names as well. Alternatively you can set the Map Options option to the value customSuffix=include. This will cause D2C to leave the __c suffixes on custom objects instead of stripping the suffixes. The Map Options option is on the advanced tab of the Salesforce data source setup dialog as shown in the image below
Sorry for the acronym...D2C ==> DataDirect Cloud
The error from the log files is "insufficient access rights on cross-reference id: 0013000000gerHN". This looks like the value that is being inserted into the Company__c column, but the value appears to be truncated. I'm not sure if the value is correctly sent by the driver and truncated in the error message or if the value is being truncated by the driver or application.
If the value is being correctly passed through the application and the driver, perhaps the value is not of the correct type for the column or no longer exists in the system. I have seen this error when passing a value in to a reference column where the id does not match the record type for the field in question or the id no longer exists.
If the id does exist and is of the right type for the field in question, then we will need to get ODBC traces and driver logs from the DataDirect Cloud driver to try to determine if the value is being passed correctly to DataDirect Cloud
One other thing about Map Options. If the value of Map Options is changed on an existing data source, the map for the data source must be rebuilt. To rebuild the map change the value of the Create Mapping option to Force New and then select save and test. Then change the value of Create Mapping back to Not Exist and save the data source again. Setting Create Mapping back to Force New will prevent the map from being rebuilt on every connection request.
If the map for the updated data source is not rebuilt you will get the following error the next time a connection is made.
[d2c][JDBC Cloud driver][Salesforce]The configuration options used to open the database do not match the options used to create the database.
The Data Source Manager should rebuild the map automatically when the Map Options are changed, but currently that does not happen
There is a video that details the Mapping Options in DataDirect Cloud that may prove helpful.
Thanks, everyone, for the quick and thoughtful replies. I believe the Map Options are set correctly so the suffixes should not be the issue.
I'll work on getting the log file. I thought I had set it properly in the ODBC Administrator, but when I looked there were no entries despite successful execution of queries prior to the request that is failing.
This link explains the difference between the 15 digit ID values and the 18 digit ID values. The 15 digit values are what is displayed through the Salesforce UI while the 18 digit values are how the ID values are handled through the Salesforce API.
I don't think the value is being truncated by the driver or application. I think the error is just returning the 15 digit ID value that is used in the Salesforce UI and not the 18 digit value used in the API.
Does the value that is being inserted reference an object that actually exists in the test data? Does the ID reference the correct type of object for the Company__c column?
Thanks for everyone's help! The error was the result of a reference to a hard coded production ID (don't ask) by a query that was executed in a sandbox. (i.e., different ID).
Glad you got it working. Thanks for following up