Creating links at runtime with ADO

[SunGodly]

Has anyone out there use ADO to link tables from two Access databases? I am using the following code but I keep getting a "Could not find installable ISAM" error.

Sub ADOTEST()
Dim cat As New ADOX.Catalog
Dim cnn As ADODB.Connection
Dim tblLink As ADOX.Table
Dim strProvider As String
Dim strName As String

strName = "tblCallData0303"
Set cnn = New ADODB.Connection
cnn = CurrentProject.Connection
cnn.Open
cat.ActiveConnection = cnn
Set tblLink = New ADOX.Table
tblLink.Name = strName
tblLink.ParentCatalog = cat
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " _
& "C:\Documents and Settings\shicks\My Documents\VUMH Data.mdb"
With tblLink
.Properties("Jet OLEDB:Create Link&quot = True
.Properties("Jet OLEDB:Link Provider String&quot = strProvider
.Properties("Jet OLEDB:Remote Table Name&quot = strName
End With
cat.Tables.Append tblLink

End Sub


There is a MS Knowledge Base article on this but it is really no help. If anyone would can assist, I would be forever in your debt.

 

[coco86]

Is there any reason why you can't use

DoCmd.TransferDatabase acLink, _
"Microsoft Access", _
"C:\Documents and Settings\shicks\My Documents\VUMH Data.mdb", _
acTable, _
"tblCallData0303", _
"tblCallData0303"

 

[SunGodly]

Wow, thanks, that was easy. I didn't know that argument existed for that method. Of course, I had already figured out there was an error in my ADO syntax - it was actually wrong in the Access book I copied it from! Anyway, I learned something new today. Thanks for your help!