Multiple DNS-Less Connections

[TEB2]

Access 2007 connecting to SQL Server

I'm using an AutoExec marco to call the function and it works great with one
table. How can I modify the code below to create links to more that one
table?

Option Explicit

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
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
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

 

[Douglas J. Steele]

See whether http://www.accessmvp.com/djsteele/DSNLessLinks.html helps.