DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

  1. #1
    Join Date
    Jul 2006

    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 = ";"
    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

  2. #2
    Join Date
    Nov 2003
    Portland, OR
    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
    Phil Weber

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jul 2006
    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.

Similar Threads

  1. How to delete table from Access 97 database table
    By software_develo in forum Database
    Replies: 1
    Last Post: 01-31-2006, 11:58 PM
  2. Replies: 4
    Last Post: 07-16-2002, 08:47 PM
  3. Replies: 5
    Last Post: 08-20-2001, 08:04 AM
  4. Temporary table in Access database
    By Marcus in forum VB Classic
    Replies: 1
    Last Post: 01-06-2001, 10:48 AM
  5. ADO Foxpro Free Table to Access Table
    By Paul Webster in forum VB Classic
    Replies: 0
    Last Post: 12-09-2000, 10:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center