Why Do I Have to Refresh My Linked Tables All the Time?

[txdave35]

Hey guys,

I have an Access mdb that uses linked tables to our SQL server. For some reason, I have to go into Tools - Database Maintenance - Linked Table Manager and refresh the linkes every time that I want to run the report. Otherwise, the report fails and says it can not connect to the SQL server.

Is there any way I can get the database to keep a permanent link? It's a real hassle to do the refresh all the time.

 

[lameid]

I have never had a problem with linked tables. My guess would be that you are not authenticated to SQL Server for some reason and relinking the tables causes the authentication. Do you use a passwor when you refresh your links?

 

[jsteph]

There is a 'save password' box (for most odbc providers--definitely sql-server) when you make the connection.

You may want to delete all the links, then relink them all and check that box.

If linking in code, when you create the tabledef you'd set it's .Connect property to the odbc connection string, and the .Attributes property to dbAttachSavePwd (131072 numerically).
--Jim

 

[lameid]

The Caveat to saving passwords is that a savy person can look at the linked table and retrieve the user and password used.

 

[kjv1611]

txdave35,

Did you ever resolve this issue?

If not, let us know where you're at.

For one, if you're sharing the database with others, then those links may have to be updated for each person. I suppose the only way around that is to split the database, and store the linked tables within each front-end, so as to have the login/password matching each user.

--

"If to err is human, then I must be some kind of human!" -Me