append data from one Access db to another

Topic

New Reply

I need to allow multiple users to copy data from their Access db to a single target db. i can use docmd.CopyObject (“UNC PathMyDb.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

Reply | Quote

Replies

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!
Computer Specs

Reply | Quote

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

IN ‘\UNCDrivePathdbsskeltonXferDB.mdb’ SELECT * FROM ;”)

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.

Reply | Quote