tabledef.connect changed when appended to Tabledefs collection!

I am building a solution using Access 2000 to connect to SQL Server 2000 database.  I am using DSN-less connections, using code to link the appropriate tables. I'm using DAO 3.6.

My problem is that although I explicitly set the Connect property of my new, un-appended tabledef, when the tabledef is appended, the connect changes.  Specifically, I set the Connect as follows:

dim db as DAO.database
dim tdf as DAO.tabledef

set db = currentdb()
set tdf = db.CreateTableDef(strTable) ' strTable passed to procedure

tdf.Connect = "ODBC;DRIVER=SQL Server" _
                & ";Server=(local);DATABASE=myDb"  _
                & ";UID=Tester;Pwd=test" _
                & ";Trusted_Connection=NO"

tdf.SourceTableName = strBaseTable ' passed to procedure

Debug.Print tdf.Connect  ' See output A
db.tabledefs.append tdf
Debug.Print tdf.Connect  ' See output B

Output A:
ODBC;DRIVER=SQL Server;Server=(local);DATABASE=myDb;UID=Tester;Pwd=test;Trusted_Connection=NO

Output B:
ODBC;DRIVER=SQL Server;SERVER=(local);UID=myWindows;PWD=;APP=Microsoft Access;WSID=myMachine;DATABASE=myDb;Trusted_Connection=Yes

where myWindows is my windows logon (no domain, i'm doing this locally using machine accounts).  As you can see, the connect string changed, in some very significant ways.  The user name and password are changed and it is now a Trusted connection.  SQL Server is using mixed authentication.

Anyone know what is causing this?  Anything I can do about it?  Obviously, this will greatly affect my security scheme, since I now can't log on specific users!


Who is Participating?
a1xConnect With a Mentor Commented:
On my machine, the change to userid/password/trusted connection didn't happen.  APP and WSID were added per your example.  

The problem may be related to, so check your OCBC driver version.
teiwazAuthor Commented:
Yep, that was it.  I had the MDAC version with this problem.  Thanks!

BTW, look for the follow-up question on how to determine the MDAC version on the user's computer, so I can make sure they don't have this problem :D
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.