
Linking External DB to Access (Follow-up)
I had asked how to link an external database to
Access 2000 from a VB program, and someone gave
me the code below. The code links a SQL Server
table beautifully, but the problem is that I am trting
to link DBase and Paradox tables. Access will not
allow an ODBC link for ISAM databases. Can anyone
offer any further help on this topic?
Thanks,
Tony
--------------------------------------------------------------------------------
sSQLServerConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=(local);" & _
"Database=pubs;" & _
"Trusted_Connection=Yes;"
' "Uid=sa;" & _
' "Pwd=;"
' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\advworks.mdb;"
' Create a new Table object
Set oTable = New ADOX.Table
With oTable
.Name = "authors"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "authors"
.Properties("Jet OLEDB:Link Provider String") = sSQLServerConnString
End With
' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh