asked on

TableDef Connect String gets overwritten

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=192.168.16.2;" & _
"DATABASE=merlin;UID=MyUser;PWD=MyPassword;Connect Timeout=60"
Set MyTabledef = CurrentDb.CreateTableDef("MyTable", dbAttachSavePWD, "MyTable", ConnectString)
CurrentDb.TableDefs.Append MyTabledef
CurrentDb.TableDefs.Refresh

However, after this code has run, the Tabledef has this in the .Connect property:

ODBC;DRIVER=SQL Server;SERVER=192.168.16.2;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.

Ian

TextReport

I would try using the dbAttachedODBC rather than dbAttachSavePWD
Cheers, Andrew

ipendlebury

ASKER

Thanks for the reply, However dbAttachedODBC gives me the error: 'Invalid Argument'. Is it possible that this option was introduced in a later version of Access?

Ian

TextReport

Not sure, I have always done this with separate lines of code rather than just the CreateTableDef

ConnectString = "ODBC;DRIVER=SQL Server;Server=192.168.16.2;" & _
"DATABASE=merlin;UID=MyUser;PWD=MyPassword;Connect Timeout=60"
Set td = db.CreateTableDef("MyTable")
td.Connect = ConnectString
td.SourceTableName = "MyTable"
db.TableDefs.Append td

This you do not set the Attributes and it workd itself out. The APP abd WSID are always added though.

Cheers, Andrew

ipendlebury

ASKER

I just tried your code snippet. I got the same result though, the credentials are overwritten and replaced with 'Trusted_Connection=yes'. So I think there's some other factor at work here.

ipendlebury

ASKER

I wonder if the ConnectString could be phrased differently?

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ipendlebury

ASKER

Ok i've fixed now. But there are a few things worth mentioning....

I tried Trusted_Connection=No in the Connect String. When I did this, Trusted Connection was absent from the .Connect property. It was still absent after I removed this clause from the Connect String.

The MSysConf table seemed to do the trick. The .Connect property now contains the required credentials.

Coincidentally, my pc crashed just aftwards and corrupted the database. My only recourse was to import all the objects into a new database. When I did this, the DSN less Tabledebs were brought across without the credentials in the .Connect property.

Anyway, all is well now. Thank you for your assistance.

Ian

ipendlebury

ASKER

I forgot to mention. After I added the MSysConf table, the credentials were still not saved initially. Only when I reverted my code to use the CreateTableDef method did the credentials get saved.