troubleshooting Question

TableDef Connect String gets overwritten

Avatar of ipendlebury
ipendlebury asked on
Microsoft Access
8 Comments1 Solution912 ViewsLast Modified:
I have written an Access 2000 applicatiion for my client. In this database I use some DSNless tabledefs to link to a SQL Server. I have a piece of code that creates these TableDefs. My problem is that when I create one of these tabledefs, the credentials I place in the connect string are overwritten. Here's how I create the TableDef:

ConnectString = "ODBC;DRIVER=SQL Server;Server=;" & _
                    "DATABASE=merlin;UID=MyUser;PWD=MyPassword;Connect Timeout=60"
 Set MyTabledef = CurrentDb.CreateTableDef("MyTable", dbAttachSavePWD, "MyTable", ConnectString)
CurrentDb.TableDefs.Append MyTabledef
However, after this code has run, the Tabledef has this in the .Connect property:

ODBC;DRIVER=SQL Server;SERVER=;UID=Ian;APP=Microsoft® Access;WSID=GANDALF;DATABASE=Merlin;Trusted_Connection=Yes

So without valid credentials in the .Connect property, non admin users are being denied access to the SQL Server. How do I fix this? Presumably there is something wrong in the Connect String  I am supplying.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros