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
--
Matt
(E-Mail Removed)
Similar Threads
