Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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
share|improve this answer
    
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 '13 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. –  samsmith Feb 18 '13 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 '13 at 4:03
    
@Zulakis The access app connects to a SQL Server. Use the alias name you configure as the SQL server. –  samsmith Feb 18 '13 at 16:08
add comment
up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.