DSN-Less Connection Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Carl's sample string doesn't have Trusted_connection=no in it. Also, you've
got spaces around some of your equal signs in the connection string: I don't
believe they're allowed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tasha said:
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
Click to expand...
 
It was the spaces that was bombing. THANK YOU SO VERY MUCH!!!! I'd kiss you
if I could. <kiss> <Kiss>

I've spent 3 days looking at that code and for the life of me couldn't
figure it out.

Thanks again for your help!

Tasha


Douglas J. Steele said:
Carl's sample string doesn't have Trusted_connection=no in it. Also, you've
got spaces around some of your equal signs in the connection string: I don't
believe they're allowed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tasha said:
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
Click to expand...
Click to expand...
 
Back
Top