DSN Less Connection

  • Thread starter Thread starter Matthew S
  • Start date Start date
M

Matthew S

I am creating a DSN less connection according to Microsofts KB.

I have a question about the stServer name.... My server has dashes in the
name such as \\test-dash

When I replace stServer with test-dash it does not like it. I added double
quotes around it, and tried leaving stServer and then write in Set stServer =
test-dash but that did not work....

Any advice? I think that is the only thing keeping me from gettting this to
work... or are there any better ways of doing this?

'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the
current database
'// stRemoteTableName: Name of the table that you are linking to on the
SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL
Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As
String, stServer As String, stDatabase As String, Optional stUsername As
String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the
linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " &
Err.Description

End Function
 
Matthew S said:
I am creating a DSN less connection according to Microsofts KB.

I have a question about the stServer name.... My server has dashes in the
name such as \\test-dash

When I replace stServer with test-dash it does not like it. I added double
quotes around it, and tried leaving stServer and then write in Set
stServer =
test-dash but that did not work....

Any advice? I think that is the only thing keeping me from gettting this
to
work... or are there any better ways of doing this?

'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the
current database
'// stRemoteTableName: Name of the table that you are linking to on
the
SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking
to
'// stUsername: Name of the SQL Server user who can connect to SQL
Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName
As
String, stServer As String, stDatabase As String, Optional stUsername As
String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the
linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " &
Err.Description

End Function
Click to expand...


Have you tried these variations?

' surround server name with single-quotes
stConnect = "ODBC;DRIVER=SQL Server;SERVER='" & stServer & _
"';DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

' ... or ...

' surround server name with double-quotes
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & _
Chr(34) & stServer & Chr(34) & _
";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

' ... or ...

' surround server name with square brackets
stConnect = "ODBC;DRIVER=SQL Server;SERVER=[" & stServer & _
"];DATABASE=" & stDatabase & ";Trusted_Connection=Yes"


I haven't dealt with this situation, so I don't know if any of those will
work.
 
I basically wrote stServer = "abc-server" ... so the double quotes worked...
I then entered stServer as part of the connection string....

Modified some other things in the code too so it is working now.

Thanks,
--
Matt
(e-mail address removed)



Dirk Goldgar said:
Matthew S said:
I am creating a DSN less connection according to Microsofts KB.

I have a question about the stServer name.... My server has dashes in the
name such as \\test-dash

When I replace stServer with test-dash it does not like it. I added double
quotes around it, and tried leaving stServer and then write in Set
stServer =
test-dash but that did not work....

Any advice? I think that is the only thing keeping me from gettting this
to
work... or are there any better ways of doing this?

'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the
current database
'// stRemoteTableName: Name of the table that you are linking to on
the
SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking
to
'// stUsername: Name of the SQL Server user who can connect to SQL
Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName
As
String, stServer As String, stDatabase As String, Optional stUsername As
String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the
linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " &
Err.Description

End Function
Click to expand...


Have you tried these variations?

' surround server name with single-quotes
stConnect = "ODBC;DRIVER=SQL Server;SERVER='" & stServer & _
"';DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

' ... or ...

' surround server name with double-quotes
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & _
Chr(34) & stServer & Chr(34) & _
";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

' ... or ...

' surround server name with square brackets
stConnect = "ODBC;DRIVER=SQL Server;SERVER=[" & stServer & _
"];DATABASE=" & stDatabase & ";Trusted_Connection=Yes"


I haven't dealt with this situation, so I don't know if any of those will
work.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Back
Top