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

[Vita]

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,

Ah I remember what the problem with this code is. For whatever reason it does not actually relink the tables. When I use this instead of FixConnections it takes longer to run and then when I try to use the combobox that retrieves info from one of the tables I get a prompt asking for the SQL login password even though I included it in SConnect.

This is my current code and it freezes microsoft access for about 30 seconds when it runs and then prompts me for the password.

Public Function CreateLinkedTables()
Dim name As Variant
Dim i As Integer
Dim TableName(0 To 6) As String
TableName(0) = "dbo.Example1"
TableName(1) = "dbo.ExampleTab2"
TableName(2) = "dbo.TableExample3"
TableName(3) = "dbo.TablesExample4"
TableName(4) = "dbo.Table_Example5"
TableName(5) = "dbo.Tables_Example6"
TableName(6) = "dbo.TableExamples7"

i = 0
For Each name In TableName
    If Not ifTableExists(Replace(TableName(i), ".", "_")) Then
        DoCmd.TransferDatabase acLink, "ODBC Database", _
            "ODBC;Driver={ODBC Driver 11 for SQL Server};Server=ServerName;Database=DatabaseName;Network=DBNMPNTW;UID=UserID;PWD=Password;", acTable, CStr(TableName(i)), CStr(TableName(i)) 'ODBC;Driver={ODBC Driver 11 for SQL Server};
                                                                                                'Before someone asks I believe without the CStr the code was giving me errors. Will probably try removing it for readability.
    End If
    i = i + 1
Next
Call RefreshConnections
End Function

Function ifTableExists(tblName As String) As Boolean
    
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then


        ifTableExists = True


    End If


End Function

Function RefreshConnections()
'   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.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
    If tdf.Connect <> "" And Left(tdf.name, 1) <> "~" Then
        tdf.Connect = "ODBC;Driver={ODBC Driver 11 for SQL Server};Server=ServerName;Database=DatabaseName;Network=DBNMPNTW;UID=UserID;PWD=Password;"
        tdf.RefreshLink
    End If
Next
If CurrentProject.AllForms("Main").IsLoaded = True Then
    Forms!Main.Refresh
End If
End Function

However if I use FixConnections instead of RefreshConnections then it works a bit faster and doesn't give me the prompt.