ODBC Connection String mystery

M

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
 
D

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)
 
M

Marina

Ah, that makes sense - thanks very much!

Douglas J. Steele said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top