1

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?

2 Answers 2

2

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.

1

Use the SQL Client configuration app ("Client Network Utility" on older versions of SQL), set up a named service (aka "alias") that uses tcpip to connect to the server. Set the Access app to use the named service/alias you create.

In more recent versions of SQL, this is done via the "SQL Server Configuration Manager" tool, in the "SQL Native Client Configuration" area, where you can create an alias.

Another approach that works for ADO and I believe ODBC: In your connection string, specify server and port. In SQL speak this is done with a comma:

<sql server name or ip>,<port>

e.g.

mysqlserver2008instance,1433
4
  • The server / database in use are both Windows 2000 / Access 2000. Is this is possible for them? I sadly could not find the options.
    – Zulakis
    Feb 17, 2013 at 21:42
  • @Zulakis On SQL 2000, I believe you should have the "Client Network Utility" -- you may need to install the client utils to get this app. Feb 18, 2013 at 1:35
  • Sorry my bad, I found it. How can I set the access application to use this alias now? I couldn't find any option for configuring that.
    – Zulakis
    Feb 18, 2013 at 4:03
  • @Zulakis The access app connects to a SQL Server. Use the alias name you configure as the SQL server. Feb 18, 2013 at 16:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .