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" _
tdf.SourceTableName = strBaseTable ' passed to procedure
Debug.Print tdf.Connect ' See output A
Debug.Print tdf.Connect ' See 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!