Private Sub Command0_Click()
Dim dbsCurrent As Database
Dim tdfLinked As TableDef
' Open a database to which a linked table can be
' appended.
Set dbsCurrent = CurrentDb() 'OpenDatabase("DB1.mdb")
' Create a linked table that points to a Microsoft
' SQL Server database.
Set tdfLinked = _
dbsCurrent.CreateTableDef("LSFPROD")
' Note: The DSN referenced below must be configured to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
tdfLinked.Connect = _
"ODBC;DATABASE=LSFPROD;DSN=LSFPROD;UID=*******;PWD=********"
tdfLinked.SourceTableName = "LAWSON.GLNAMES"
dbsCurrent.TableDefs.Append tdfLinked
' Display contents of linked table.
Debug.Print _
"Data from linked table connected to first source:"
RefreshLinkOutput dbsCurrent
' Change connection information for linked table and
' refresh the connection in order to make the new data
' available.
' Note: The DSN referenced below must be configured to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
tdfLinked.Connect = _
"ODBC;DATABASE=LAWSON.GLNAMES;DSN=NEWLSFPROD;UID=********;PWD=*******"
tdfLinked.RefreshLink
' Display contents of linked table.
Debug.Print _
"Data from linked table connected to second source:"
RefreshLinkOutput dbsCurrent
' Delete linked table because this is a demonstration.
dbsCurrent.TableDefs.Delete tdfLinked.Name
dbsCurrent.Close
End Sub
Sub RefreshLinkOutput(dbsTemp As Database)
Dim rstRemote As Recordset
Dim intCount As Integer
' Open linked table.
Set rstRemote = _
dbsTemp.OpenRecordset("LAWSON_GLNAMES")
intCount = 0
' Enumerate Recordset object, but stop at 50 records.
With rstRemote
Do While Not .EOF And intCount < 50
Debug.Print , .Fields(0), .Fields(1)
intCount = intCount + 1
.MoveNext
Loop
If Not .EOF Then Debug.Print , "[more records]"
.Close
End With
End Sub
