Updating/Apending between two Access DBs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Wrote an asset tracking database, and got an interesting request from
accounting. Bascially they wanted a duplicate database for themselves to
reconsile and update info in ... then when ready I would simply transfer the
data from the "accounting DB" they were working on into the "production DB"
already inplace.

Despite how easy as I assumed it would be, I can't find an easy way to
append/update all the data from one to the other. The import option always
makes duplicate tables.

I'm guessing theres got to be sample VB code out there somehwere I can
cut-paste-and-hack to pull this off, as every table and relationship is
identicle. It may get a little trickier with the joins ... but I imagine
still plausible.

Anyone able to point me in the right direction?
 
hi,
i am sure there is code out there and opinions may vary
but i am thinking macro.
you would need 3 action for each table you need to import
1. tranferDatabase. (to get the data in a table in the
production database)
2. openquery. (write an append query to append the
imported data to the production db
tables)
3. deleteobject (to delete the imported tables whose
data has been appended to the
production db tables and are no longer
needed)
I have used this before but not in a while. i use a
similar technique now to snatch tables from our production
db to my dev db so that i will have real data to play with.
i delete the dev tables and import the production tables.
and it does it pretty quick.
anyway...my suggestion.
regards
Frank
 
Hmm ... never thoguht of the macro route ... good suggestion ...

I'm going to give it a go and see how it works. hoping for quick-and-simple
;)
 
Another snag :(

My joined fields are all indexed and auto-entered. This basically means the
moment I import those joined fields the relationships will be wrong

On my "main table" I have a unique field I can easily use for finding
matches and updating and not appending duplicates ... but thats not one I
used to join tables. The microsoft article I found basically says that it
can't be done if the join is to an auto-entered ID...

Have I shot myself in the foot? (need to make new entries and joins) Or is
it possible to use some kind of criteria during the append to change the
field on the joined table to the new unique ID that is auto-generated?
 
Back
Top