ODBC Connection Question

[Adrienne B.]

I'm trying to solve a mystery and need some help.

At various times during the development and maintenance cycle for my Access
database I need to establish a connection to a SQL Server 2005 Express
backend database. I define the tabledefs for the SQL tables using the
following code:

Set tdefTemp = CurrentDb.CreateTableDef(strTableName)
svrpath = "DRIVER={SQL Server};SERVER=" & server_name & ";DATABASE=AIM;
UID=uid;PWD=pwd;"
tdefTemp.Connect = "ODBC;" & svrpath
tdefTemp.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdefTemp


When I check the connection after this code has run, the tabledef looks like
this:

ODBC;DRIVER=SQL Server;SERVER=servername;APP=MS Office
2003;WSID=mypcid;DATABASE=dbname;TABLE=dbo.tablename

The WSID and APP parameters create problems for my users and defeat the
purpose for using the DSNless connection.

Two questions:
1) Why do the tabledefs for the SQL Server tables contain the APP and WSID
parameters when they are not included in the connect string parameter that is
used to create the tabledef entry?
2) What can I do to do to ensure that these parameters do not get put into
the tabledef entries?

Also, every now and then this code generates the expected result although
95% of the time it doesn't.

Any ideas or suggestions for resolving this would be greatly appreciated.

 

[BeWyched]

Hi. Sorry I don't know the answer but have a look at the following link that
might point you in the right direction:

http://www.connectionstrings.com/?carrier=sqlserver2005

Good luck.

BW