teiwaz
asked on
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);DATA BASE=myDb; UID=Tester ;Pwd=test; Trusted_Co nnection=N O
Output B:
ODBC;DRIVER=SQL Server;SERVER=(local);UID= myWindows; PWD=;APP=M icrosoft Access;WSID=myMachine;DATA BASE=myDb; Trusted_Co nnection=Y es
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!
Thanks!
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
tdf.Connect = "ODBC;DRIVER=SQL Server" _
& ";Server=(local);DATABASE=
& ";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);DATA
Output B:
ODBC;DRIVER=SQL Server;SERVER=(local);UID=
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!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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