Connection fails on first attempt per computer and once a day?

[Vita]

Hi, I will start with some background. I have several linked tables that connect to a SQL server via an ODBC connection string.
Upon opening the application I have an autoexec set to run the a connection called FixConnections (Can be found here: http://www.accessmvp.com/DJSteele/DSNLessLinks.html).
It works fine everywhere in the form but for some reason once everyday the first time you open the application on a computer (Each computer has this issue the first time opening it. Hope I'm explaining that clearly) I get the following error message.

Microsoft SQL Server Login

Connection failed:
SQLState: '01S00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Login timeout expired

It seems like it doesn't like the connection string even though it works fine everywhere else?
it then comes up with the normal SQL login prompt with for you to log in. If I log in then everything works correctly but I can't have this happen because Access 2000 doesn't like SQL connections and that is where I mainly open it. I only open it in Access 2019 when I need to reset the linked tables. (I know its a weird situation but everything works fine between the two for the most part.)

It is a bit difficult to troubleshoot since it only seems to happen once I haven't opened the connection for an undetermined amount of time. (somewhere under 24 hours)
The goal of the application is pretty simple. Just to be able to open and read/write to a few fields. I have it set to run the FixConnections function after every update because other people use the DB too and that one works fine. I can't use a trusted connection either since its a company DB.

My best guess is that since the connection string seems to be valid/correct Access can't make the initial connection for the first time? I'm hoping it isn't something on the SQL end.
Any Ideas on what's causing it or how to fix it?

[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?

[Vita]

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:

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"

[Minty]

The driver you are using needs to be matched in your connection string.
So it should read something like

;DRIVER=SQL Server Native Client 11.0;

As I said you only need to relink if you have made design changes to your tables: new fields, changed field names or altered data types.
Actual data changes should be reflected with a simple form refresh.

[Vita]

So I replaced the SQL driver string with the right one from ConnectionStrings. (Not sure how it was functioning before)
Now I get the error of unable to process due to delay in opening server. What is interesting is that the error only comes up in Access 2000.
If I open the DB in Access 2019 it works fine.

The error specifically says:

Connection failed:
SQLState: '08001'
SQL Server Error: 0
[Microsoft][ODBC Driver 11 for SQL Server]Unable to complete login process due to delay in opening server connection

After some googling I am trying to increase the connection timeout but the few attributes I tried give me an additional error saying "Invalid connection string attribute".
I am pretty sure it is not the SQL server because in the ODBC datasources (on windows) the connection tests fine.

[Gicu]

It was functioning before because "sql server" is the legacy driver included in all versions of Windows. What SQL server version are you using? Check out this link and try to force the connection to use the NP (Named Pipes) protocol as shown here:https://social.msdn.microsoft.com/Fo...=sqldataaccess
TCP is usually the default and it should work if the firewall rules are set up correctly.

But the main thing is, as Minty said, you do not need to do this (refresh the connection) to see new data, only to get new structural changes for the tables (new fields, indexes, data types). And usually you would go through all that code for that, you would simply need to look through the TableDefs collection and call the RefreshLink for each tabledef object (linked table):

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim Newserver As String
DIm SConnect as String




SConnect = "ODBC;DRIVER=SQL Server Native Client 10.1;SERVER=" & YourServer & _
			";DATABASE=" & YourDB & ";" & _
			"UID=" & UID & ";" & _
			"PWD=" & PWD & ";"
			
Set dbs = CurrentDb()
'   Loop through TableDefs collection, only processing
'   the table if it already has a Connection property.
'   (all other tables are local ... not linked.)
'   In other words this will only change linked tables.
  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" And Left(tdf.Name, 1) <> "~" Then
      tdf.Connect = sConnect
      tdf.RefreshLink
    End If
  Next

Cheers,

[Vita]

Swapping to named pipes worked! I used this article to find the right attribute to add to the connection string because I didn't want to have to download a separate program on every computer I'm deploying this to. https://learn.microsoft.com/en-us/tr...nnect-instance Network=DBNMPNTW for Named Pipes.

As for the refreshing. Do I need to refresh the links or would it be sufficient to refresh the form?

[Vita]

But the main thing is, as Minty said, you do not need to do this (refresh the connection) to see new data, only to get new structural changes for the tables (new fields, indexes, data types). And usually you would go through all that code for that, you would simply need to look through the TableDefs collection and call the RefreshLink for each tabledef object (linked table):

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim Newserver As String
DIm SConnect as String




SConnect = "ODBC;DRIVER=SQL Server Native Client 10.1;SERVER=" & YourServer & _
            ";DATABASE=" & YourDB & ";" & _
            "UID=" & UID & ";" & _
            "PWD=" & PWD & ";"
            
Set dbs = CurrentDb()
'   Loop through TableDefs collection, only processing
'   the table if it already has a Connection property.
'   (all other tables are local ... not linked.)
'   In other words this will only change linked tables.
  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" And Left(tdf.Name, 1) <> "~" Then
      tdf.Connect = sConnect
      tdf.RefreshLink
    End If
  Next

Cheers,

Is "sConnect" the variable for the connection string? Is there a website with naming conventions? I usually use "ConnectStr" but now I wanna start using "sConnect".
Also I usually use "db" as the database variable. Just curious if the s in "dbs" stands for something or is it just the s from database?

[Welshgasman]

Is "sConnect" the variable for the connection string? Is there a website with naming conventions? I usually use "ConnectStr" but now I wanna start using "sConnect".
Also I usually use "db" as the database variable. Just curious if the s in "dbs" stands for something or is it just the s from database?

They are just variables. You can name them what you like?
I tend to stick to a naming convention to identify what type they are.

https://www.google.com/search?q=vba+...hrome&ie=UTF-8

[Gicu]

You don't have to refresh the links to get the latest data, a form requery would do that (Me.Requery). Note that when you do that the form will go to the first record; if you want to stay on the current one you need to save the current PK, requery then navigate back to it using the bookmark method or using Docmd.FindRecord.

Those were just examples of variable names, use your old ones if you're used with them. Usually I prefix the name with the variable type (s or str for string, l or lng for Long, etc.)
https://software-solutions-online.co...g-conventions/

Cheers,

[Minty]

As for the refreshing. Do I need to refresh the links or would it be sufficient to refresh the form?

If it's just changed data then refresh the form, NOT the link.
As stated previously, the only time you need to refresh the table link is if something in the tables underlying design has been changed.

[Vita]

You don't have to refresh the links to get the latest data, a form requery would do that (Me.Requery). Note that when you do that the form will go to the first record; if you want to stay on the current one you need to save the current PK, requery then navigate back to it using the bookmark method or using Docmd.FindRecord.

Those were just examples of variable names, use your old ones if you're used with them. Usually I prefix the name with the variable type (s or str for string, l or lng for Long, etc.)
https://software-solutions-online.co...g-conventions/

Cheers,

Okay. I am having it refresh the form after updating the ticket to complete. If that is the case then I should have it do a requery on the before update event.

Thank you both so much!

[Gicu]

Can you explain the last sentence please, and\or show us the code. You cannot requery the form in the BeforeUpdate because it will reset the current record. You would do it in the Current event of the form if you really want to capture the very latest of the data but be aware that it will move to the first record so you'll have to navigate back to your current; alternatively you can try to requery just the form's recordset:

Me.Recordset.Requery

Cheers,

[Vita]

So the form is set to search the ticket table and if its not found in that table it searches the ticket history before telling the user its not found. There's a good chance that multiple people will use it at the same time on different computers.
The form is a simple employee name combobox, ticket number textbox, and a mark complete button. So currently I have the code you wrote to refresh the links setup in its own function called "RefreshConnections" and I call that function as the last line of the buttons click event.

When a ticket is entered the after update event searches for info related to the ticket and displays it for the user. Including the status of it. I am now thinking that I should do a requery at the beginning of the after update event. (There are no local tables so I'm not worried about it going to the first record. It uses DLookup to find the relevant information after correctly formatting the input numbers.)

[Gicu]

I don't think you need to do anything at all, I would suggest simply adjust the ODBC refresh interval to something that works for you:

Requerying is updating the record source of a form or report (or row sources of combos and listboxes) but if you use dLookup to search for the ticket it will not help.
Cheers,