How to transfer a local Access table into a remote Access database by ADO ?

[Oscar99]

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

[Phil Weber]

The Jet Engine is not a true database server. I'm pretty sure it does not support connections from a remote client the way Oracle and SQL Server do. If you can get file system access to the remote server, however, you should be able to connect to the database on that machine using a UNC path, e.g.: \\servername\sharename\filename.mdb

[Oscar99]

Hi Phill,

I am able to connect to the remote access database. I even was able to update records on the remote. I only couldn't get the table linking working between the two databases.