November 24, 2008 at 2:43 pm
I am using Access 2003 with SQL 2005 Enterprise backend. I manually linked the tables using the Access Link Table Manager using a Trusted Connection. On some pc's, I am able to connect, but not all pc's (using the same account). I finally narrowed down the problem to timeout issues: on first attempt to open the table, I get a connection error, but if I immediately attempt to open the same table again, I can successfully open the table!
I didn't see any timeout settings when I manually linked the tables. So, I tried to dynamically refresh the links. I guessed on the "TIMEOUT" parameter, not sure if that is valid. It didn't fix my problem. Does anyone know a solution to my timeout problem? Thanks.
Dim tdf As DAO.TableDef
Dim strConnect As String
strConnect = "ODBC" & _
";DRIVER=SQL Server" & _
";SERVER=MyIPAddress\MyInstance" & _
";TRUSTED_CONNECTION=Yes" & _
";DATABASE=MyDatabase" & _
";TIMEOUT=60"
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next tdf
November 24, 2008 at 3:42 pm
Hi
I've not noticed a timeout property for linked tables either, there is within queries (right click in the blank space and select properties then go to ODBC Timeout).
I have found a similar issue in the past and it was with the password not being saved. When you try to open the table it fails to open, when you open a different table which uses the same login Id, but has the password saved it works fine, then when you go back to the original table it works fine.
Assuming that this is the problem delete the problem table the relink manually, being careful when selecting the table from the database to put a tick in the save password box.
How this helps.
November 25, 2008 at 9:16 am
Thanks for the reply. I tried your suggestion, but it didn't work.
I did some more tests and discovered this:
- If I link using the "SQL Server" driver, timeout error occurs.
- If I link using the "SQL Native Client" driver, the timeout problem is fixed.
My dilemma is that the "SQL Native Client" driver is not installed in all the pc's in my company. That is why I have been using the SQL Server driver.
Can anyone help? Thanks.
February 1, 2009 at 10:34 am
In my experience, the SQL Native Client performs much faster than the old provider. I would recommend having the admins install the Native Client on your user PC's.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply