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
|
