I am trying to use ADOX to dynamically create a "LINKED" ODBC table in an Access database back to DSN table but am not sure what the ODBC syntax is for the connections string. This is the code ....
Code:
cnnToLocalAccessMDB = = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\.....\LocalMDB.mdb"
strLocalTableName = "tblStock"
strRemotePathFileMDB = "Provider=MSDASQL.1;Password=tx;Persist Security Info=True;User ID=txdemo;Extended Properties='DSN=SageLine50 Demo;UID=txdemo;PWD=tx;'"
strRemoteTableName = "STOCK"

Dim catLocal As ADOX.Catalog
Set catLocal = New ADOX.Catalog
catLocal.ActiveConnection = cnnToLocalAccessMDB

Set tblRemote = New ADOX.Table
With tblRemote
Set .ParentCatalog = catLocal
.Name = strLocalTableName
.Properties.Item("Jet OLEDB:Create Link").Value = True
.Properties.Item("Jet OLEDB:Link Datasource").Value = strRemotePathFileMDB
.Properties.Item("Jet OLEDB:Remote Table Name").Value = strRemoteTableName
End With

catLocal.Tables.Append tblRemote


This is the Error that occures on the line catLocal.Tables.Append tblRemote :
Code:
Could not find file
'C:\...\Provider=MSDASQL.1;Password=tx;Persist Security Info=True;User ID=txdemo;Extended Properties='DSN=SageLine50 Demo;UID=txdemo;PWD=tx;'
It appears as though it is not finding the correct Sage Database from the DSN although if I test the DSN it all appears to be working correctly.

Anyone with any thoughts on this one?