How to transfer a local Access table into a remote Access database by ADO ?
I am trying to find a way to import a whole access table from a local Access database to a remote access database by ADO and ADOX in VB;
while the following coding works for the case that both databases resides at the same PC it doesn't work for the case that one of the databases is remote e.g. on a webserver. It reports 'runtime error 3265 : Item cannot be found in the collection corresponding to the requested name or ordinal' at the row '.Properties("Jet OLEDB:Link DataSource").Value ='
How can I solve this problem?
this is the coding :
Dim oCat As ADOX.Catalog
Dim oTab As ADOX.Table
LocalDataSourceStr = "D:\Utzm\DBS\LocalDB.mdb;"
Set connLocal = New ADODB.Connection
connLocal.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & LocalDataSourceStr"
RemoteServerStr = "http://xx.xxx.xx.xxx;"
RemoteDataSourceStr = "C:\InternetDB\RemoteDB.mdb;"
Set connRemote = New ADODB.Connection
connRemote.Open "Provider=MS Remote;" & _
"Remote Server = " & RemoteServerStr & _
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & RemoteDataSourceStr
Set oTab = New ADOX.Table
Set oCat = New ADOX.Catalog
Set oCat.ActiveConnection = connRemote
With oTab
.ParentCatalog = oCat
.Name = sSourceTable
.Properties("Jet OLEDB:Link DataSource").Value = connLocal.Properties("Data Source Name")
.Properties("Jet OLEDB:Link Provider String") = "MS Access"
.Properties("Jet OLEDB:Remote Table Name").Value = sSourceTable
.Properties("Jet OLEDB:Create Link").Value = True
End With
oCat.Tables.Append oTab
