Solved

tabledef.connect changed when appended to Tabledefs collection!

Posted on 2004-04-20
2
1,575 Views
Last Modified: 2007-12-19
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!

Thanks!





0
Comment
Question by:teiwaz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
a1x earned 500 total points
ID: 10874886
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 http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/5/26.ASP&NoWebContent=1, so check your OCBC driver version.
0
 
LVL 1

Author Comment

by:teiwaz
ID: 10878727
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
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question