Sql-server – make microsoft access use TCP/IP instead of Named Pipes

microsoft accessodbcsql server

I got a Microsoft Access database which is constantly trying to contact the corresponding Microsoft SQL Server using named pipes, even though only TCP/IP is available and the ODBC is configured to use TCP/IP. It's not possible to link all the tables once again with the working ODBC.

How can I fix this?

Best Answer

In my case, samsmith's example did not work.

However, I got it fixed by using something like this:

    Dim DB As DAO.Database
    Set DB = CurrentDb
    Dim Tdf As TableDef

    'Loops through list of tables
    For Each Tdf In DB.TableDefs
        If Tdf.SourceTableName <> "" Then
          'this checks if it actually is a odbc table as i do not want to change the connection string of local tables obviously
          If Not InStr(Tdf.Connect, "ODBC") = 0 And Not (Tdf.Attributes And
dbAttachedODBC) = 0 Then
              Tdf.Connect = "ODBC;DSN=xxx;APP=MicrosoftR Access;WSID=A2200;DATABASE=xxx;Network=DBMSSOCN;TABLE=dbo." & Tdf.Name
              Tdf.RefreshLink 'Saves the changes made
          End If
        End If
    Next
Debug.Print "Finished!"

The Network=DBMSSOCN is the key part, this specifies that tcp should be used.

Related Topic