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
Solved

tabledef.connect changed when appended to Tabledefs collection!

Posted on 2004-04-20
2
1,565 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
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 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