tcp/ip rather than named pipes for dsn setup

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

Keith G Hicks

The code I'm using to set up a user dsn in order for my access 2000 app to
link to ms sql 2000 tables is working fine. But sometimes on a computer
that does not already have the dsn set up, it defaults network libraries to
"named pipes". How do I set up my code so that it will always use "tcp/ip"?

Thanks,

Keith

My code is below:


Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
Dim strDSN As String
' ---------------------------------------------
' Register ODBC database(s).
' ---------------------------------------------
DoCmd.Hourglass True
Set db = CurrentDb
db.QueryTimeout = 300

Set rs = db.OpenRecordset("Select * From tblODBCDataSources Order By
DSN")
With rs
While Not .EOF
If strDSN <> rs("DSN") Then
DBEngine.RegisterDatabase rs("DSN"), _
"SQL Server", _
True, _
"Description=" & rs("DataBase") & _
Chr(13) & "Server=" & rs("Server") & _
Chr(13) & "Database=" & rs("DataBase")

End If
strDSN = rs("DSN")
' ---------------------------------------------
' Link table.
' ---------------------------------------------
strTblName = rs("LocalTableName")
strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
strConn = strConn & "UID=" & rs("UID") & ";"
strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName") & ";"
strConn = strConn & "network=dbmssocn"
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
DoCmd.Hourglass False
MsgBox "ODBC Data Sources Refreshed Successfully.", vbInformation
CreateODBCLinkedTables_End:
DoCmd.Hourglass False
Exit Function
CreateODBCLinkedTables_Err:
DoCmd.Hourglass False
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
 
Sorry.
But I don't know how to code it.
I just check the DSN manually when I create it.

Try a Google search for how to create a DSN in code.
 
network=dbmssocn

is the line that says your connection should use tcp/ip.

This works for us on DSN-less Jet 3.51 connections.
Are you having the problem with the connection, or with using the DSN
elsewhere?
Perhaps you could just try including that line in the DSN as well as in the
connection?

(david)
 
Back
Top