VBA to Connect to Sybase/SQL Anywhere ODBC

[kazoli]

Long story short, I have a linked table connected to a Sybase database via ODBC. Every time Access is opened and the user tries to interact with the table, it requires them to enter their credentials to log into the ODBC on their machine. To avoid this, my goal is to prompt the user for their credentials when Access is opened and then run a passthrough query to the Sybase database to connect to the ODBC without having the obnoxious ODBC pop-up that shows more than we'd want general users to see.

I have all of the VBA I need that gets the user credentials, and then I have code to run a passthrough query that will connect to the ODBC in the backend. I'm able to set up the ODBC connection string exactly how I need except for one piece - the Data Source Name box remains blank even when I call out the DSN in the connection string; as a result, the connection fails. I've attached a screenshot to this post of the field I'm looking to populate. Additionally, below is the connection string I'm using. My understanding is that the "DSN" portion of the string should accomplish what I'm looking for, but I've had no success so far with this phrasing or several other variations. Is there some other syntax I need to use or other info I might be missing?

    
strConnection = "ODBC;DRIVER={SYBASE IQ};DSN=MyDataSourceName;" & _                     
                        "Host=MyHostInfo;Server=MyServerInfo;" & _
                        "Database=MyDBName; userid=MyUserID;" & _
                        "password=MyPWD";suppresswarnings = True;" & _
                        "preventnotcapable = true; encryption = true"

[Gicu]

Before you do all that try to delete the link and recreate it making sure that you click the Save Password towards the end of the linking process. You shouldn't get the prompt after that. If you provide the driver name you shouldn't need the DSN, just leave it out (google DSN-less connection).

Cheers,

[kazoli]

If I save the password, will that allow other users to gain access to my credentials worst case? Or if I save my own password and still prompt them for their credentials, would their login info replace mine so that they would not be able to see my password?

[Gicu]

Are you saying each user has their own login in Sybase? I don't use it but usually with ODBC the developer creates a generic UserID that gets shared among all front-end users (other than when using Trusted Connection or similar option like in SQL server when it uses the Windows login of the user).
The password gets saved (along with the rest of the connection information) in msysObjects system table in the Connect field, and yes it is not encrypted. If that bothers you then you can try to refresh the link to the table using something like this:

Dim tdf As DAO.TableDef,db as DAO.Database sConnect As String
Dim strMyUserID as String,strMyPWD as string 'pass these from your opening form code


Set db=CurrentDb
Set tdf = db.TableDefs("tblYourSybaseTable")
sConnect = "ODBC;DRIVER={SYBASE IQ};" & _                     
                        "Host=MyHostInfo;Server=MyServerInfo;" & _
                        "Database=MyDBName; userid=" & strMyUserID & ";" & _
                        "password=" & strMyPWD & ";suppresswarnings = True;" & _
                        "preventnotcapable = true; encryption = true"
tdf.Connect = sConnect
tdf.RefreshLink
Set tdf = Nothing

Cheers,