Problem: Access connection to Salesforce API

[JTaylor]

Hi all - This may show as my first post but my normal account was associated with email address at previous employer and I no longer have access to reset and retrieve the password...so...new account. Thanks for your understanding.


Quick backstory

Salesforce has an API from which we can extract data. I've used this in MS Excel via the native functionality 'Get Data - From Online Services - From Salesforce Objects', and it has worked flawlessly. I now want to transition this to a more sustainable and expandable solution, and our IT department is taking forever to assist; pushing delivery dates from one quarter to the next. With no real date in sight, I'd like to leverage MS Access but am having problems getting started.


Problem

In MS Access, the problem simply put, is that our SalesForce name is CompanyXYZ, Inc. with a period at the end. When I use Access 'New Source Data - From Online Services - From Salesforce', and supply username, password, and Token, I am then presented with a full list of Objects (tables). Each object name listed is preempted with our company name, followed by a period, followed by the object name (an example is below), and the two back-to-back periods are what is causing the issue.

Examples:

• CompanyXYZ, Inc..Accounts
  
• CompanyXYZ, Inc..Projects

When I try to continue, I get the following error: "The Microsoft Access database engine could not find the object CompanyXYZ, Inc..Projects. Make sure the object exists and that you spell its name and the path name correctly. If CompanyXYZ, Inc..Projects is not a local object, check your network connection or contact the server administrator"

I did a bunch of research on this error, and finally found a forum thread on the Salesforce developer site (link here if your'e able) where another user had the same issue. The advice in that thread was to change the name of my company as it is listed in Salesforce...which I'm not in a position to do.




Question

Within Access, is there any way for me to edit the code manually to make this connection? Since I'm not the first user to experience this issue, and likely won't be the last, I also feel like I should be able to alert Microsoft to what I would consider a bug. Any thoughts on this?


Thanks in advance for sharing your wisdom. Look forward to hearing your thoughts.

[CJ_London]

I would have thought an API that works in Excel will also work in Access in terms of the data being submitted. Whether the returned result is in a form that works for Access is another matter

not sure if this will work but when you are searching for wildcard characters in text you use square brackets around the wild card. You could try the same

replace("CompanyXYZ, Inc..Accounts",".","[.]",,1)=CompanyXYZ, Inc[.].Accounts

Other thought is to contact SFDC helpdesk rather than using a forum - they may have a solution

Or do your import via Excel (start an excel session within Access)

I'm not familiar with the SFDC API so cannot advise further, but so far as I am aware, it is not possible to edit the API.