ODBC connection in Access after upgrade to Sage 100 Advanced 2016

Posted By Stacie Cotter over 7 years ago

We have just completed our upgrade to Sage 100 Advanced from Sage 100 standard (2015). I have updated my ODBC connection string in Access to point to the new directory, however I am getting the following error when trying to login to run the pass through query.

The Sage 100 login box does not give me the option to choose a company. There is no company chosen and that area is grayed out. (Knowledgebase entry concerning this did not pan out)

My ODBC connection string is: ODBC;DSN=SOTAMAS90;Description=MAS 90 32-Bit ODBC Driver;Directory=L:\Sage\Sage 100 Advanced\MAS90;Prefix=L:\Sage\Sage 100 Advanced\MAS90\soa\;ViewDLL=L:\Sage\Sage 100 Advanced\MAS90\Home\;CacheSize=4;SERVER=NotTheServer

If I go in to the design view property sheet and click on the 3 little dots at the end of the ODBC string, I choose the SOTAMAS90 data source and try to log in, the login screen comes up correctly and I can choose a company. Upon logging in I get an error that the ODBC connection string cannot be longer than 255 characters. If I exit that I am able to run the pass through query successfully.

I am using Microsoft Access 2007.

Is there something different that I need to have in my connection string for the advanced version?

0 BigLouie over 7 years ago

Try this where XXX = User Log On and ZZZ = password

ODBC;DSN=SOTAMAS90;UID=XXX|ABC;PWD=ZZZ;Directory=L:\Sage\Sage 100 Advanced\MAS90;SERVER=NotTheServer
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Stacie Cotter over 7 years ago in reply to BigLouie

I tried that string and it did not work. It took me to the login window with the company blank and grayed out. I type in my User name and password and get the same error message. I see my error message did not post above. The error message says "Invalid Clint/Server Communication. Please ensure that the ODBD driver and security DLL versions are correct."
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 BigLouie over 7 years ago in reply to Stacie Cotter

OK here is what I have on one of mine that works. Forgot to mention that ABC = Company Code

ODBC;DSN=SOTAMAS90;UID=???|ABC;PWD=???;Description=MAS 90 4.0 ODBC Driver;Directory=N:\Sage 100 Standard ERP\MAS90;Server = NotTheServer
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 BigLouie over 7 years ago

ODBC;DSN=SOTAMAS90;Description=MAS 90 32-Bit ODBC Driver;UID=???|YYY;PWD=ZZZ;Directory=L:\Sage\Sage 100 Advanced\MAS90;SERVER=NotTheServer

where ??? = User ID YYY= Company Code and ZZZ = password.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Stacie Cotter over 7 years ago in reply to BigLouie

That did not work either. If I go into the configure option on SOTAMAS90 in the ODBC data source administrator, I get error messages as well. One of them says there is a architecture mismatch between the driver and application. I wonder if I should re-install the ODBC driver?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 BigLouie over 7 years ago in reply to Stacie Cotter

That explains it all. That means you are using the 32 bit ODBC driver on a 64 system or a 64 bit ODBC driver on a 32 bit system. There is an entry in the knowledge base about it.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Stacie Cotter over 7 years ago in reply to BigLouie

I installed the 64-bit ODBC driver and that fixed the issue in the ODBC data source administrator but did not fix the Access problem. I still get the same login screen and then the message "Invalid Client/Server Communication. etc." Is there a .dll file that I need to replace?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Kevin M over 7 years ago in reply to Stacie Cotter

In order to configure the SOTAMAS90 DSN you need to use the 32-bit ODBC system tool found in the SYSWOW64 folder. If you try to configure SOTAMAS90 with the 64-bit utility there will be the architecture mismatch error.
If you can get an Excel query to work, using MS Query, the syntax for connecting should be within the connection properties.

(All you'd need then is to add the user / company / password components to the path).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Stacie Cotter over 7 years ago in reply to Kevin M

I cannot get an Excel query to work either. Same login screen with blank company name grayed out and then same error message.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Kevin M over 7 years ago in reply to Stacie Cotter

Can you print a report from within Sage 100? It could be the drivers / workstation were not properly installed.
Run the workstation program "as admin" at least once to get the DSN set up correctly.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 100

ODBC connection in Access after upgrade to Sage 100 Advanced 2016

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING