Your best and really only option is to create a silent DSN. Plenty of post on how to create one. What you are seeing with the SOTAMAS90 DSN is working as designed. No way to change.  

I agree that the SOTAMAS90 DSN is working as intended.  We'd like to use the silent DSN we've created but even when defining the connection string to use our silent DSN it still behaves as though it's connecting to the SOTAMAS90 DSN

Is your application 64-bit?  Did you create a 64-bit silent DSN?

Refreshing this spreadsheet query does not prompt for credentials:

Get a working connection string that doesn't prompt (validated in Excel when you Refresh All), and use that.  Then if you get a prompt in your application you know it is not related to the string.

It is a 32bit application (written in VB6, actually).  To be certain, we did recently install the 64-bit driver in case the ODBC was trying to use that, but that didn't seem to make any difference in the behavior, either.

It is a 32bit application (written in VB6, actually).  To be certain, we did recently install the 64-bit driver in case the ODBC was trying to use that, but that didn't seem to make any difference in the behavior, either.

Hi You can try a DSN-Less ODBC Connection string

i.e.

Driver={MAS 90 4.0 ODBC Driver}; Company = ABC; UID=ALL; PWD='' Directory=C:\Sage\Sage 100 Standard\MAS90; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer

The SOTAMAS90 DSN should not be used for any third party ODBC access.  The DSN is recreated on each startup of Sage 100.

John Nichols

Sage

It's been some time since I've worked with VB6, but the following code snippet is what we used to use in our VB.NET apps when accessing Sage data..  It dynamically builds a connection string depending on if the install is Standard or Advanced (C/S).  

Perhaps, you can re-tool it to fit in VB6.

Dim constr As String = ""

If CBool(My.Settings.CS) Then
constr = constr & "DSN=SOTAMAS90; Directory="
constr = constr & My.Settings.SageInstall
constr = constr & "; Prefix="
constr = constr & My.Settings.SageInstall
constr = constr & "\SY\, "
constr = constr & My.Settings.SageInstall
constr = constr & "\==\; ViewDLL="
constr = constr & My.Settings.SageInstall
constr = constr & "\HOME; LogFile=\PVXODBC.LOG; RemotePVKIOHost="
constr = constr & My.Settings.SageServerName
constr = constr & "; RemotePVKIOPort="
constr = constr & My.Settings.SagePort
constr = constr & "; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer"
constr = constr & ";UID=" & My.Settings.SageUserName & ";PWD=" & My.Settings.SagePassword & ";Company=" & CompanyCode
Else
constr = constr & "DSN=SOTAMAS90; Directory="
constr = constr & My.Settings.SageInstall
constr = constr & ";UID=" & My.Settings.SageUserName & ";PWD=" & My.Settings.SagePassword & ";Company=" & CompanyCode
End If

Dim con As System.Data.Odbc.OdbcConnection = New Data.Odbc.OdbcConnection(constr)

Thanks for the suggestion.  We've tried forgoing the DSN and using the driver specification previously, however when we had we had not included curly brackets around the driver name.  I should be able to give that a try later today

I am afraid that using the syntax with curly braces around the "Driver" variable did not resolve the issues we are seeing.

Did you try Kevin's suggesting to make sure you first get a working connection string in another application, such as Excel or Crystal Reports? 

Are you sure you are passing a valid company code that exists in the Sage 100 install? We have been using ABC as an example because its demo data can be optionally installed when Sage 100 is installed but it could have been deleted after the fact or never installed in the first place.

Hi David,  yes, we have been able to verify connection strings do work in Excel (without a login prompt) and the company code we've been using in the connection string is the one applicable to the environment and not "ABC" as in the example.

Search your VB6 project for any reference to SOTAMAS90.  There should be none.  It could be there is something buried which is still pointing at that instead of your new DSN.