T
Tasha
Hello!
I really hope you guys can help me with this because I am completely at my
wits end here on what to try next.
I have an Access mdb connected to a SQL Server backend via a DSN-Less
connection recommended by Douglas Steele and using the IP connection from
Carl Prothman. I have tested the application on 7 different systems. 4 at 1
site, 2 at a 2nd site, and 1 at a 3rd site. The 4 at the 1st site can all
connect, the other 3 can't. 6 systems are running Window XP with Office 2003.
The application was actually designed in Access 2000. I am getting an error
message that states:
Connection Failed:
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQL Server Driver][NamedPipes]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or
access denied.
This is the call I use in the form load event
Call FixConnections("999.999.999.999", "validDBName")
This is the code being called:
Function FixConnections(ServerName As String, DatabaseName As String)
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: This subroutine looks for any TableDef objects in the
' database which have a connection string, and changes the
' Connect property of those TableDef objects to use a
' DSN-less connection.
' This specific routine connects to the specified SQL Server
' database on a specified server.
'
' Inputs: ServerName: Name of the SQL Server server (string)
' DatabaseName: Name of the database on that server (string)
'
On Error GoTo Err_FixConnections
Dim dbCurrent As DAO.Database
Dim intLoop As Integer
Dim intToChange As Integer
Dim tdfCurrent As DAO.TableDef
Dim typNewTables() As TableDetails
intToChange = 0
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
' Build a list of all of the connected TableDefs and
' the tables to which they're connected.
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
ReDim Preserve typNewTables(0 To intToChange)
typNewTables(intToChange).Attributes = tdfCurrent.Attributes
typNewTables(intToChange).TableName = tdfCurrent.Name
typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
intToChange = intToChange + 1
End If
Next
' Loop through all of the linked tables we found
For intLoop = 0 To (intToChange - 1)
' Delete the existing TableDef object
dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName
' Create a new TableDef object, using the DSN-less connection
Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";ADDRESS =" & ServerName & ",1433" & _
";Trusted_Connection=no" & _
";NETWORK = DBMSSOCN" & _
";Uid=ValidUserID" & _
";Pwd=validpassword"
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent
' Where it existed, create the __UniqueIndex index on the new table.
If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If
Next
End_FixConnections:
Set tdfCurrent = Nothing
Set dbCurrent = Nothing
Exit Function
Err_FixConnections:
' Specific error trapping added for Error 3291
' (Syntax error in CREATE INDEX statement.), since that's what many
' people were encountering with the old code.
If Err.Number = 3291 Then
MsgBox "Problem creating the Index using" & vbCrLf & _
typNewTables(intLoop).IndexSQL, _
vbOKOnly + vbCritical, "Fix Connections"
Else
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
End If
Resume End_FixConnections
End Function
________________________
On the systems that don't work I have pinged the IP address that I am using
and the ping comes back fine. I also know that it is not a user name and
password issue because I have hardcoded the name and password for now so all
of them are using the same one. I am at a loss as to what to check to
troubleshoot the three that are not connecting. If anyone has any suggestions
it would be greatly appreciated.
Thanks,
Tasha
I really hope you guys can help me with this because I am completely at my
wits end here on what to try next.
I have an Access mdb connected to a SQL Server backend via a DSN-Less
connection recommended by Douglas Steele and using the IP connection from
Carl Prothman. I have tested the application on 7 different systems. 4 at 1
site, 2 at a 2nd site, and 1 at a 3rd site. The 4 at the 1st site can all
connect, the other 3 can't. 6 systems are running Window XP with Office 2003.
The application was actually designed in Access 2000. I am getting an error
message that states:
Connection Failed:
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQL Server Driver][NamedPipes]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or
access denied.
This is the call I use in the form load event
Call FixConnections("999.999.999.999", "validDBName")
This is the code being called:
Function FixConnections(ServerName As String, DatabaseName As String)
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: This subroutine looks for any TableDef objects in the
' database which have a connection string, and changes the
' Connect property of those TableDef objects to use a
' DSN-less connection.
' This specific routine connects to the specified SQL Server
' database on a specified server.
'
' Inputs: ServerName: Name of the SQL Server server (string)
' DatabaseName: Name of the database on that server (string)
'
On Error GoTo Err_FixConnections
Dim dbCurrent As DAO.Database
Dim intLoop As Integer
Dim intToChange As Integer
Dim tdfCurrent As DAO.TableDef
Dim typNewTables() As TableDetails
intToChange = 0
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
' Build a list of all of the connected TableDefs and
' the tables to which they're connected.
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
ReDim Preserve typNewTables(0 To intToChange)
typNewTables(intToChange).Attributes = tdfCurrent.Attributes
typNewTables(intToChange).TableName = tdfCurrent.Name
typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
intToChange = intToChange + 1
End If
Next
' Loop through all of the linked tables we found
For intLoop = 0 To (intToChange - 1)
' Delete the existing TableDef object
dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName
' Create a new TableDef object, using the DSN-less connection
Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";ADDRESS =" & ServerName & ",1433" & _
";Trusted_Connection=no" & _
";NETWORK = DBMSSOCN" & _
";Uid=ValidUserID" & _
";Pwd=validpassword"
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent
' Where it existed, create the __UniqueIndex index on the new table.
If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If
Next
End_FixConnections:
Set tdfCurrent = Nothing
Set dbCurrent = Nothing
Exit Function
Err_FixConnections:
' Specific error trapping added for Error 3291
' (Syntax error in CREATE INDEX statement.), since that's what many
' people were encountering with the old code.
If Err.Number = 3291 Then
MsgBox "Problem creating the Index using" & vbCrLf & _
typNewTables(intLoop).IndexSQL, _
vbOKOnly + vbCritical, "Fix Connections"
Else
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
End If
Resume End_FixConnections
End Function
________________________
On the systems that don't work I have pinged the IP address that I am using
and the ping comes back fine. I also know that it is not a user name and
password issue because I have hardcoded the name and password for now so all
of them are using the same one. I am at a loss as to what to check to
troubleshoot the three that are not connecting. If anyone has any suggestions
it would be greatly appreciated.
Thanks,
Tasha