DSN-less untrusted ODBC connection

[Guest]

I am trying to set up a DSN-less connection using Doug Steele's code found
at: http://www.accessmvp.com/DJSteele/DSNLessLinks.html.

I want to be able to supply the username and password, so I have used the
modified connection string as follows:

tdfCurrent.Connect = "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

I have also added code per Doug's site to save the username and password for
each table:

tdfCurrent.Attributes = DB_ATTACHSAVEPWD

So, assuming I am using an untrusted connection and have already supplied
login credentials, why am I receiving an error message that states 'Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.'? After I click ok, a login box appears, I uncheck 'Use Trusted
Connection', I enter the same login credentials I specified when I ran the
VBA code, and only then am I able to gain access to the linked tables.
Ideally, I don't want the user to have to enter anything. Why does Access
still want me to use a trusted connection after I have specified in the VBA
code that I don't? Any help would be appreciated.

Thanks,
Melanie

 

[Alex Dybenko]

Hi,
perhaps you forgot ODBC, here how it works at me:

tdfCurrent.Connect ="ODBC;DRIVER={sql server};DATABASE=" & strDatabase &
";SERVER=" & strDsnOrServer & ";UID=" & strUID & ";PWD=" & strPWD &
";Trusted_Connection=No;"

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Alex,

Thanks. I added the ODBC word, but I still have the problem. Do you know
how to check for the rights on the server side? Perhaps the ID I am using is
set up as trusted connection only.

Thanks,
Melanie

 

[Alex Dybenko]

hi,
if you are using non-trusted connection (sql server security) then you have
to check that login you use exist on SQL server, and has access to database
you connection. You can use SQL server enterprise manager to do so (by
default it is installed on SQL server PC)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com