ODBC Connection String mystery

[Marina]

Hi guys,

I'm connecting to SQL Server via ODBC from a VB module in Access (front-end
DB) and I'm using the following code:

Set userQuery = SQLdb.CreateQueryDef("")
userQuery.Connect = "ODBC;DSN=MyServer;DATABASE=MyDB"

I then run various queries against the database and all seems to working
fine during tests.

I assume that the SQL Server recognizes the connection as a trusted
connection, since I have Windows Authentication for the Windows login from
which Access is opened.

What I'm wondering is, why is the connection to SQL Server happily
connecting? Shouldn't I have used:
userQuery.Connect = "ODBC;DSN=MyServer;DATABASE=MyDB;
trusted_connection=yes"

I'm just wondering, since I'm plan to change users' logins to Windows
Authentication logins and this makes me wonder whether I'm missing something
that will bite me in the tail later on. I thought trusted_connection was by
default "no", in which case something else must be going on?

The Access-front end is linked to various tables in the SQL backend, could
that be why?

Thanks,
Marina

 

[Douglas J. Steele]

You're using a DSN. Presumably the authentication approach is defined in the
DSN.

See http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
for sample strings you can use if you want to avoid having to define a DSN.
(Note that those connection strings are shown for ADO, hence the oConn.Open
statement. When using the connection strings the way you are in Access,
there must be ODBC; in front, even though it's not there in most of the
examples)

 

[Marina]

Ah, that makes sense - thanks very much!

You're using a DSN. Presumably the authentication approach is defined in the
DSN.

See http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
for sample strings you can use if you want to avoid having to define a DSN.
(Note that those connection strings are shown for ADO, hence the oConn.Open
statement. When using the connection strings the way you are in Access,
there must be ODBC; in front, even though it's not there in most of the
examples)