Your assumption about the credentials for accessing the tables is probably faulty. Check with Sage on that.
Thanks for the input. Managed to get the ODBC link working directly with the Sage account via Excel, with the password as expected.
When I use the same link via FM, this is where things fall apart with 'the account and password you entered cannot be used to access this file' appearing from FM.
I know the credentials are correct due to the Excel test, so can only assume FM is doing something else here?
Any ideas gratefully received
You are using the same DSN for the excel test?
What version of FM do you have and if 14, is it the 32 or 64bit edition?
Same DSN yes,
Using FM version 14 Pro Advanced 64 bit.
Then what you mentioned in your first post would not work: ODBC32.exe.
For FM 64-bit to work you need to create the DSN in the 64-bit ODBC data sources.
I think looking at things here it's more of a Sage issue than FM, although the final error is delivered by FM.
Upon trying to setup 64bit DSNs, there is no option to select the Sage ODBC driver. When this is done from the 32bit side of things, the Sage drivers are visible.
I have a feeling that Sage is only 32bit. Given I have FM 64bit, it seems the ODBC interface may not work.
If it is critical you can certainly install the 32-bit version of FM14 on Windows 64-bit. Make sure to consider other integrations (like sending email to Outlook...)
That may be the way forward for this particular use case. Shouldn't have too many major impacts.
Thanks for your help with this wimdecorte.