Not promted to login..? 1

[Kujen]

Hi all,

I have linked tables in Access to a SQL-server with SQL authentication.

For some reason I'm not promted to login after I run this code:
==========================
gStrConnect = "ODBC;DRIVER={SQL SERVER};SERVER=MyServer;Database=MyDb;UID=" & Me.strUserId & ";PWD=" & Me.strPassword

For Each Tdf In db.TableDefs
If InStr(1, Tdf.Connect, "MyDb") > 0 Then
With Tdf
.Connect = gStrConnect
.RefreshLink
End With
End If
Next
====================
If I delete all linked tables and link them again then I'm promted to login again. It seems to me that the connect string is stored somewhere.

What do I have to do so that I'm always promed to login?

Kent J.

 

[LonnieJohnson]

gStrConnect = "ODBC;DRIVER={SQL SERVER};SERVER=MyServer;Database=MyDb;UID=" & Inputbox("what is the username?") & ";PWD=" & Inputbox("what is the password?")

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[Kujen]

Hi,

Username and password is stored in the connectstring.
I don't think that's the problem.

If use a connect string that points to a DNS:
gStrConnect = "DSN=MyDNS;...."
then I'm promted to login

So when I use a connect string:
gStrConnect = "ODBC;DRIVER={SQL SERVER}..."
then I'm not promted to login.

The two string behaves differently.
Why?

Kent J.

 

[LonnieJohnson]

When you create the DSN you can store the user and password in there as well.

When going through the wizard to create a new DSN right after you select the server name the next set of option has a choice for SQL Server Authentication. Did you set up the DSN yourself?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[Kujen]

Hi,

Yes, I set up the DSN myself.

But is the userid and password stored in Access somewhere when I use?: gStrConnect = "ODBC;DRIVER={SQL SERVER}..."

Kent J.

 

[LonnieJohnson]

Ok. It looks like you want to be prompted. If so, then do not pass the password and userID in the connection string (i.e. ;UID=" & Me.strUserId & ";PWD=" & Me.strPassword).

I am assuming that your DSN does not have a password and userid stored and that is why it prompts you when you manually link the tables. The code above to refresh the links is passing the user and pwd from t he text boxes on the form and that is why it is not asking for it.



ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!