Apr 23 2020 06:36 AM
Apr 23 2020 06:36 AM
Hi all, I am new to the communities although I am a long time MS user, so here goes.
Over 6 months ago we ran into a problem the built in salesforce connector of Access. First release we noticed was 1908, since than all releases show the same problem ( tested up to 2003). For all I know it might not even be an access problem (although that would be strange since we tested on a variety of setups).
I tried to get this 'bug' registered to MS (or at least inform them about the problem) but up to now I have failed in doing so. I am running constantly in circles.
So now I just thought about asking how to report the problem.
Regarding the problem itself:
The bug we see in MS Access is that all the Field Size info’s for a table from Salesforce are wrong (they are always shown as a triple in size of the real value). So a salesforce field (like ShippingCity for example) that is 40 in size will show in Access as being 120 in size… This means that any field that is larger than 85 in size would no longer be a Short Text but suddenly becomes Long Text … This is causing a lot of issues and renders the connector useless (definitely when you are trying to do updates to salesforce)
I added some screenshots showing the problem very clear, wishing that I could report this to MS.
In the end we are now looking into third party solutions, how sad is that...
Apr 23 2020 02:21 PM
@DaneelsMedia Let's see if we can't get some attention to this. It may or may not be an Access issue, as you acknowledge, but it doesn't hurt to follow up as much as we can.
Apr 24 2020 05:42 AM
@DaneelsMedia I will report back if and when I hear anything, or perhaps someone from MS will be here to see the details. Good luck.
Apr 28 2020 05:42 AM
@DaneelsMedia have you checked if using a Pass through query maps correctly the fields .
Maybe you could create a PT ...retrieve some data and then use a make table to create a local (temp) table.
Check if the sizes now are correct
Apr 28 2020 10:18 AM - edited Apr 28 2020 10:18 AM
My apologies for the trouble you're experiencing.
I'm the original developer that worked on this feature and I've logged an issue on our end to track this. We updated the Salesforce driver at this time and it looks like this is a new issue introduced by this update.
Apr 28 2020 10:42 PM
@tsgiannis I don't think I fully understand your suggestion, where would my pass through query go? To an SQL server but that server doesn"t have access to the salesforce data? Doing a passthrough to salesforce direct? Don't think that is possible.
Please advice if I am overlooking something.
Apr 28 2020 10:45 PM
@Mike Sullivan Thanks for posting Mike, if the test or development team need more info or additional data or can't reproduce the problem, let me know.
Apr 28 2020 10:59 PM
@DaneelsMedia Let me understand something because from screenshots is not that clear.
When you work from Access "to" SalesForce you work with
In both cases 1 & 2 you need a driver to connect to SalesForce BE whatever that might be...if not and you are falling to cases 3 & 4 maybe SalesForce exports the data correctly but the importing module fails for some reason to recognize the datatype's length and doubles everything...(maybe something like 32bit-64bit)
It seems that SalesForce doesn't provide an ODBC driver (that's why the mixup...i saw the headlines ) but there are vendors providing ODBC drivers : https://success.salesforce.com/ideaview?id=08730000000BqquAAC so you might want to consider buying one.
Apr 28 2020 11:07 PM
@tsgiannis We are looking into other solutions (paying ones) that would indeed provide some kind of ODBC connection but that is kind of parallel track.
In the first place you would expect that standard provided functionality in a product works (and it did when we started out this type op integration) so that is why we did no pursue any other method of interaction with Salesforce.
As can be seen in my first post: Up to now I did not even succeed in getting the message across to MS (although I now got a message from a nice person (that is close to the development group of this component), so we keep our fingers cross that the dev team gets the message and finds something.
Apr 28 2020 11:15 PM
@tsgiannis Regarding you tech part of the question:
In our version of Access (we have an O365 E3 license) there is as standard (when you want to setup an external data source) a Salesforce connector. This connector is NOT part of all version of O365 Access.
This allows to access the Salesforce objects as linked (or copied) tables in our applications. You can do almost everything (select, insert, delete...) with the right permissions, so it really fits our case (provided it works of course ).
Apr 28 2020 11:16 PM
@DaneelsMedia I am not sure i got the info how you link Access to Salesforce..
If some guy from MS took it over i reckon is a done deal....although i think is not an Access problem....its the "bridge" that "feeds" wrongly...
Apr 28 2020 11:19 PM - edited Apr 28 2020 11:24 PM
@DaneelsMedia Some light
Can you please check if the ODBC driver appears in the ODBC Data Source Administrator (Add) in the Control Panel.
If yes then you can create Pass Through queries just fine....
By the way .... things look bad from the start...1st link to get info about SalesForce ODBC driver and is broken
And by the way...there others with issues : https://developer.salesforce.com/forums/?id=9060G0000005jE9QAI it seems the connector is somewhat sensitive
Apr 29 2020 12:10 AM
@tsgiannis No there isn't any ODBC driver showing as a data source, it is really embedded into MS-Access (I think it would be really hard for MS to limit use to a specific license plan if it was a standard ODBC driver).
For further info: read the URL I send earlier, it explains the configuration process in detail.
Apr 29 2020 12:22 AM - edited Apr 29 2020 12:24 AM
@DaneelsMedia thanks for clarifying...i did try to read the article but some crucial link is broken.
So its an embedded connector (surprise surprise)....then you have 3 options
1. Accept it as it and work your way to modify the tables or the reports/form that are depending on them to adjust to the wrong length (maybe its just a missing Trim)...probably IF you are sure that everything is doubled you could create queries that half the info...i reckon that Access team will give a proper solution to it but...
2. I would really recommend testing 3rd party ODBC connectors... i have NONE association with any of them - i don't even know who are the vendors that provide such connectors, only what google search returned - just setup a test machine and start working...i guess that with the pay comes support...so download (or request) a trial and see what is the outcome.
3. The hard one...maybe you should resort to low level monitoring to see where the problem lies....e.g. Wireshark to see what you are sending and what you are receiving...maybe something will enlight the whole issue.
Apr 29 2020 04:07 AM
Mike Sullivan is not just "some guy from Microsoft". He is, in fact, the person who built the SalesForce connector for Access originally.
The problem is, as he noted, a regression in a recent update to the driver. It's being addressed now by someone on the MS Access team at Microsoft. It's unfortunate when things get broken. It's fortunate that the MS Access team does respond to reports of problems, though.
Apr 29 2020 04:35 AM - edited Apr 29 2020 04:38 AM
Given the fact that is a long problem given the info provided by the author and probably he is not the only one facing issues but maybe found a work around and with the time span of 6 months i wouldn't say its safe to assume anything.
I did miss that Mike responded as the "original developer"....
Apr 29 2020 06:20 AM
@tsgiannis While it is true that the problem is not recent, it is also probably true that the number of Access developers using the SalesForce connector is relatively small. Not to minimize the problem for those who are impacted by the problem, but it's less surprising that it's not received that much publicity compared to other bugs found in more commonly used functions. This is, in fact, the first time I've seen it reported in a forum I frequent.
When I brought it to MS attention, the response was quick and positive. It's all about being able to penetrate the layers of "support" that is not all that supportive, unfortunately.
Apr 29 2020 06:58 AM
@George Hepworth It's all about being able to penetrate the layers of "support" that is not all that supportive, unfortunately.
Well that really sums up my initial question/complaint in a very eloquent way