How to share a silent ODBC Connection

Question

I've connected a single table from Sage 100c 2021 to Excel (data model) via a silent ODBC connection with saved username/password. All works great for me, but if anyone else uses this excel workbook and tries to refresh the data it makes them log in a gazillion times. Is there a way to "share" the DSN? Or am I going about this the wrong way? I've tried creating the same System DSN (same name and settings) on another user's workstation to see if it would work that way, but no luck. We're all using the 32-bit DSN. Any ideas?

FormerMember · Accepted Answer

I use a ODBC system DSN on the server with Scripts I write in ScriptBasic. I don't have any problems with multiple users using the same DSN. Try pointing to the server DSN and see if you have better luck. 
 ODBC doesn't track user counts (PVKIO based). BOI sessions use a seat just like 100.

David Speck · Answer

You can avoid using a DSN and instead specify the connection string using by replacing the "DSN=SOTAMAS90;" portion of the connection string with "D river={MAS 90 4.0 ODBC Driver};" The rest of the connection string should already be present in the query connection string but you can also generate the connection string by accessing the "ODBC Data Sources", selecting the SOTAMAS90 DSN, clicking the Debug tab, clicking the Connection String button, and then copying everything after the DSN parameter. Make sure the paths are in UNC format since users on different machines will need to have a consistent path to the Sage 100 installation on the server. 
 
 You can specify the user by adding a "UID=abc;" parameter to the connection string. 
 You can specify the password by adding a "PWD=123;" parameter to the connection string. 
 You can specify the company by adding a "Company=xyz;" parameter to the connection string. 
 This does require saving the credentials in the connection string though and for this reason, I recommend creating a user in Sage 100 whose only purpose will be to authenticate via the ODBC driver. Give it a role with access to either all or specific companies and make sure the role doesn't have anything selected on ANY of the tabs unless you have ODBC Security enabled, in which case, you will need to specify which tables the role will have access to. Creating a user like this avoids having to save an actual Sage 100 user account that is used within Sage 100 so if anyone ever sees the connection string, even if they try to log into Sage 100, if using the ODBC user, they won't be able to access any Sage 100 tasks within the system.

Sage 100

How to share a silent ODBC Connection

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING