Originally Posted by
Minty
It looks as if the connection string you are using is incorrect.
When you supply the correct credentials it will then cache that connection for a period of time.
Why are you using Access 2002?
If push came to show you could install a modern runtime (free to download) if licencing cost is an issue.
If the tables are already linked then you shouldn't need to keep relinking them unless you change something in the table structure, the whole setup sounds a bit off-kilter to me?
I'm actually using Access 2000 (9.0.2720) but 2002 is the earliest version the forums has listed. It's unfortunately not up to me. It's what the whole company uses and they don't want to update right now because of the time and cost to update everyone and make sure everything stays functional. So I am developing in 2000 for compatibility.
I believe the original connection string should be correct. This is what I have in that script:
Code:
Function FixConnections( _
ServerName As String, _
DatabaseName As String, _
Optional UID As String, _
Optional PWD As String _
)
strConnectionString = "ODBC;DRIVER={sql server};" & _
"DATABASE=" & DatabaseName & ";" & _
"SERVER=" & ServerName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";" & _
"ConnectRetryCount=2;"
(I just pasted the relevant bits as the entire script is posted in the link on the OP. I added the "ConnectRetryCount=2;" in attempt to fix the issue by having it retry the connection)
As for the relinking it was to try and refresh the connection and check for changes. Does it do that automatically?
For further explanation the table I am editing contains a bunch of worktickets and I am attempting to have it refresh so that it can be updated and check for any that have been modified by other users.
Chances are this application will be left open all day if not for days on end so if it caches and refreshes the connection while the form is left open then I would be happy to get rid of that but otherwise I needed it for that reason.
(Apologies for any confusion. I am new to database connections still. Learning Resources always appreciated )
Edit: The SQL Driver I am using is "SQL Server Native Client 11.0"