Linking SQL tables . - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Linking SQL tables .

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-27 : 11:28:20

Ronald writes "Is it possible to relink linked sql tables in an MS Access mdb to a sql database, using a different userid and password to connect to the same sql database? For example, the following code would be in a loop enumerating all linked tables in the mdb:

Normal Tables Relink:
Db.TableDefs(tblName).Connect = "ODBC;DRIVER=SQL Server;SERVER=Myserver;UID=MyUID_1;PWD=MyPWD;APP=Microsoft® Access;WSID=My_PC;DATABASE=MyDatabase

Read-Only Tables Relink:
Db.TableDefs(tblName).Connect = "ODBC;DRIVER=SQL Server;SERVER=Myserver;UID=READONLY;PWD=PWD_ReadOnly;APP=Microsoft® Access;WSID=My_PC;DATABASE=MyDatabase

No matter what I try, all tables are relinked using the information from the first connection made. It appears that once you make a connection to the sql database you can't reconnect with a different userid and password information in the same session.

How can I program around this? I am using VBA, SQL-DMO, ADO, DAO, SQL Server 7.0, and Win2000 Pro.

Thanks,
Ron Baskin"

bdloving
Starting Member

4 Posts

Posted - 2002-09-16 : 11:01:44

One answer is get rid of the Links!
Drop all the linked tables and access/query them using
pass-through queries against the tables/views/stored procedures. You can then change the ODBC connect string
in the PT-queries using VBA code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources