Refreshing linked tables results in multiple SQL server login requests.

[Steve]

On a Windows 2000 system I am using Linked Table Manager
in Access 2000 to refresh twelve tables linked to a SQL
server database (SQL Server 2000) using ODBC. The SQL ODBC
driver on this system is 03.81.9031. The refresh is
successful but it requests a login to the SQL server for
each table - twelve logins.

I have another Windows 2000 system that requires just one
SQL server login to update the twelve tables. The SQL ODBC
driver on this system is 03.81.9001.

The preference would of course be to use a single login to
update the tables. The only difference that I can find is
the SQL ODBC driver version. I have not tried downgrading
the ODBC driver yet hoping for another explanation. Any
help would be appreciated.

Thanks.

 

[david epsom dot com dot au]

Access should cache ODBC connections, and should use
an existing cached connection when relinking.

The solution here is probably simple: after relinking
the first table, open a recordset on it, and leave it
open till you have finished relinking.

If that doesn't work, it may indicate that your
connection strings are rewritten when you save the
tabledef (see other postings about problems with
ODBC login).

I would be more worried about what will happen in
later use: You don't want to be unable to use cached
connections when running your application.

(david)