DSN Less connections by DJSteele

[HSalim]

Hi,
I just read and tried using Doug Steele's article on creating DSNLess
Connections
While it works well for a trusted connections, I just can't seem to get it
to work with SQL authentication, which is what I need.

Thanks in advance for the assistance
Regards
HS

 

[Douglas J. Steele]

When you say SQL authentication, I assume you're talking about the situation
where you have a User ID and password defined in SQL, rather than using
their network ID.

You'll have to change the sub definition from

Sub FixConnections(ServerName As String, DatabaseName As String)

to

Sub FixConnections(ServerName As String, _
DatabaseName As String, _
UserName As String, _
UserPassword As String)

and then replace the line

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

with

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
"Uid=" & UserName & ";Pwd=" & UserPassword

For other possibilities, check out Carl Prothman's
http://www.able-consulting.com/ado_conn.htm, as referred to in my article.

 

[HSalim]

Doug,

Thanks for the answer.

I tried that and was getting an error - I think ODBC 3015, at other times
(after making a change to the connection string) the ODBC administrator
dialog would pop open.

Anyway, I deleted the old connections string and rebuilt it and tried again
and it works like a charm, so I must have had a typo somewhere.

Thanks for the help

Regards
HS