Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

tabledef.connect changed when appended to Tabledefs collection!

Posted on 2004-04-20
2
Medium Priority
?
1,582 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 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