'runtime error 3265 : Item cannot be found in the collection corresponding to the requested name or ordinal' when importing table

I am trying to import a complete table from a local Access Database to a
remote Access Database by ADO and ADOX. There is no problem with the
connection as I am able to copy each record one by one to the remote
database. Now I want to import the complete table since there is an
autonumbering ID-field which has to remain the same value after appending to
the table. This is my coding :

LocalDataSourceStr = "D:\LocalDB\Testdatabase.mdb;"
RemoteServerStr = "http://xxx.xxx.xx.xxx;";
RemoteDataSourceStr = "C:\InternetDB\RemoteDatabase.mdb;"

Set connLocal = New ADODB.Connection

connLocal.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & LocalDataSourceStr & _
"Jet OLEDB:Database Password=xxxxxxxx"


Set connRemote = New ADODB.Connection

connRemote.Open "Provider=MS Remote;" & _
"Remote Server = " & RemoteServerStr & _
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & RemoteDataSourceStr & _
"Jet OLEDB:Database Password=xxxxxxxxx"


CreateLinkedTable connLocal, connRemote,"tblEmployee"


Private Sub CreateLinkedTable(cnSource As ADODB.Connection, _
cnDestination As ADODB.Connection, _
sSourceTable As String)

Dim oCat As ADOX.Catalog
Dim oTab As ADOX.Table

Set oTab = New ADOX.Table
Set oCat = New ADOX.Catalog

Set oCat.ActiveConnection = cnDestination

With oTab
..ParentCatalog = oCat
..Name = "lnk" & sSourceTable
..Properties("Jet OLEDB:Remote Table Name").Value _
= sSourceTable
..Properties("Jet OLEDB:Link DataSource").Value _
= cnSource.Properties("Data Source Name")
..Properties("Jet OLEDB:Create Link").Value = True
End With

oCat.Tables.Append oTab


When debugging, VB fires an error at the row .Properties("Jet OLEDB:Remote
Table Name").Value = sSourceTable
it reports 'runtime error 3265 : Item cannot be found in the collection
corresponding to the requested name or ordinal' thereby pointing to part
..Properties("Jet OLEDB:Remote Table Name").

How should I solve this problem ?

regards,
Oscar




.

Relevant Pages

  • Re: Accessing remote MS Access DB thru IIS App
    ... It uses the ADO 2.5 library to give users OLEDB and ODBC access from ... your IIS Application" ... ... "Do not use an Access database on a remote ... Access database from a compiled IIS Application? ...
    (microsoft.public.inetserver.asp.components)
  • Drag and Drop MS Access Database on Remote Server?
    ... The project has been copied to a commercial hosting service's remote server. ... for the Access database was different on my notebook than on the remote ... the script name and append the name of the Access Database. ... This the connection string generated by Visual Studio! ...
    (microsoft.public.vsnet.debugging)
  • Re: How to access...
    ... MDB is an MS Access database. ... native Foxpro format. ... Your easiest entry point will likely be a remote view from VFP into your ... There is a TON of information in the help file about using ...
    (microsoft.public.fox.programmer.exchange)
  • RE: Remote Login, using dbi:ODBC
    ... Subject: Remote Login, using dbi:ODBC ... I login to my remote mysql databases, using perl, this way: ... sub ConnectToDB ... So, to connect to a Remote Access Database, can I use this: ...
    (perl.dbi.users)