Merge Identical Databases

[Dadioles]

Hello

I have just developed an Access 2000 database for my company and populated
it with a couple of hundred records. It is not hugely complex as databases
go but there are certainly quite a few tables nested about 4 deep (Panel
with several gases, each gas with several channels, each channel with
several bits of info...)

Now I want to be able to add a lot more records but would find it easier to
do this at home.

The most logical method (I think?) is to create an "empty" copy of the
master database and add the new records at home, then merge the data into
the master database at work.

The question is how best to handle this?

Suggestions please.....

Thank you

 

[Arvin Meyer [MVP]]

If the records don't already exist, it's really easy. Append the tables from
the top down. In other words main tables first, then dependents, then their
dependents, etc. You can link the two databases and append the records or
just connect the queries, like:

INSERT INTO tblTest ( PersonID )
SELECT tblAddress.PersonID, *
FROM [C:\Database\NewData.mdb].tblAddress;

 

[tina]

well, i don't know, hon. if nobody will be updating the work database when
you're not there, i'd just save the whole thing to a flash drive, take it
home and work on it, and then overwrite the original database when you go
back to work.

hth

 

[Dadioles]

Many thanks to you both for you very quick replies. I shall play with the
first "append" method and see how I get on as that would be the most
versatile technique. No records would exist as duplicates so it should be
straight forward.

I have been using the "quick and dirty" method of just copying the database
to a memory stick during the development phase and I must say that it is
pretty foolproof. The only drawback is that now they have gone live with
the database I would have to get into work early!!

I am very tempted to set up a link between home and work so that I can
simply operate the database remotely. I do this already to provide
individual support (using "TeamViewer" which is brilliant) and obviously
that does away with any need to merge.

Now for a bit ot trial and error.

Once again.... thank you.

 

[Dadioles]

I just tried the append query method by first linking the two databases and
then appending the tables "from top down". It was much easier than I had
feared.
Once I knew what to look for, the instructions in the help file "Append
records from one table to another using an append query" made sense.

Thanks again...... happy customer.

 

[tina]

you do have one consideration with the append method. 1) if there are other
users entering new records in one or more tables, and 2) if any of those
tables have primary keys that are not user-assigned (my guess is that would
be most or all of them), and 3) if another user may enter new data *before*
you run your append - then you're likely to run into the problem of
duplicate key values being rejected on the append. that can be handled, but
it's painstaking and tedious work, not to mention time-consuming, especially
if you're dealing with more than one or two tables. you'd want to, at least,
back up the live database before commencing an append operation - just in
case.

remote operation might be your best option, but others can comment on that
better than i, since i've never done it.

hth