Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to allow multiple users to copy data from their Access db to a single target db. i can use docmd.CopyObject ("UNC Path\MyDb.mdb"), "DestinationTableName", acTable, "LocalTableName" but each time i do this, any existing data gets wiped out. Any ideas on how to append data between to dbs?

    TIA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    I'd write an Append Action Query, Call it from VBA {DoCmd.OpenQuery} . Make sure the query locks the destination table while running. Or you could copy the SQL for the Action Query into a SQLString and execute from VBA:

    Code:
       Dim zSQL As Variant
       
      zSQL = "[Your copied SQL string goes here]"
      DoCmd.RunSQL zSQL, 0
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well, after spending a LOT of time on doing tabledefs, appends, etc etc I happened to notice Access has a provision for appending to -- Another Database! -- and it's a one-liner:

    DoCmd.RunSQL ("INSERT INTO <Table on Remote Access DB> IN '\\UNCDrivePath\db\sskelton\XferDB\<RemoteDB>.mdb' SELECT * FROM <myQueryOrTable>;")

    Sheesh!!!

    There is the issue of duplicate submissions or people trying to do this while the targe db is locked, but still, there ya go.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •