2 copies of one database with different data in each

[Guest]

Hi,

I'm new to the board and I need help fast with MS Access at work. We are in
security and have a database that is shared by two dept. I made a copy of
our database in my dept. to a different folder on the same drive but it is
linked to a copy in a different dept. I didn't create any links and I didn't
delete the original. So now we have 2 copies of the same database with
different data because some people update one copy and some updated the other
copy. How can I combine the data back into the original and get rid of the
copy I made? How can I find out where the data starts that is different in
both our copies. Does this make sense?? Please Help! Thanks

 

[Larry Linson]

I'm new to the board and I need help fast with
MS Access at work. We are in security and have
a database that is shared by two dept. I made a
copy of our database in my dept. to a different
folder on the same drive but it is linked to a copy
in a different dept. I didn't create any links and I
didn't delete the original. So now we have 2
copies of the same database with different data
because some people update one copy and some
updated the other copy.

One of my colleagues at a major corporation had a favorite saying for cases
where someone had similarly gotten themselves into a mess such as this. He'd
shake his head and say, "You've gotta know what you're doing." By just
making a copy and having two copies with data updated, without "knowing what
you were doing" you may or may not have gotten yourself into a difficult
situation. What will determine how easy or how difficult it is to recover
will be how your database was designed and is implemented.

I hope you were not expecting just a few lines of reply that would solve
your problem and let the story be ended with "and lived happily ever after."

How can I combine the data back into the original and
get rid of the copy I made?

Without knowing details of what you have, how the data is laid out in
tables, how various data is identified, no one here can give you a valid
answer to this question.

If you are lucky and there is sufficient information in your data records,
it may be as simple as linking the tables in one database, running a few
queries, then running an append query to add the information from the copy
to the master. What are the chances that you'll be so lucky? That's
impossible for me to guess from the information provided.

If you are not lucky, you'll have to understand the information, understand
the process the DB is intended to support, figure out how to determine the
common part as a start, and then how to append the separate updates to that
table. It may well require some redesign of the tables.

How can I find out where the data starts that is different
in both our copies.

Queries are your friend. There are queries, some that can be created with
the wizard, to find duplicates, or find differences. A query that returns
the different records can be used as a data source for a query to append the
data -- provided there aren't identification conflicts between the records.

Does this make sense?

I rarely, very rarely, just suggest hiring a professional, but from the
level of your questions, and my assumption that the DB contains vital
business data, I suspect that would be your best approach... contract with
someone who's very experienced with Access. Have that person review and
document the requirements, analyze the database, and plan and implement the
merged data. I'd also make certain that he/she splits the database into a
shared "back end" (tables, relationships, and data) and a separate "front
end" for each user (queries, forms, reports, macros, and modules, with
linked tables to the back end) -- had you had the database configured this
way, you might well have not faced the problems you do.

Larry Linson
Microsoft Access MVP

 

[Guest]

This is the first time I used this board and I was very impressed with the
quick and informative response that I received. I knew just enough to know
that I was in trouble and should have removed the copy I made but I was
interrupted and then left it to be used.

In the part that I asked about how can I tell where the data is different,
you said "provided there aren't identification conflicts between the
records." I'm not sure what you meant by this statement. Thanks again for
your help!

 

[Larry Linson]

This is the first time I used this board and I was very
impressed with the quick and informative response that
I received. I knew just enough to know that I was in
trouble and should have removed the copy I made but
I was interrupted and then left it to be used.

In the part that I asked about how can I tell where the
data is different, you said "provided there aren't
identification conflicts between the records." I'm not
sure what you meant by this statement. Thanks again for
your help!

Many tables are designed so that the records have a unique identification
field that is of the AutoNumber type (an AutoNumber is normally a Long
Integer that automatically increments between records -- but you can't count
on it being truly sequential because there are circumstances in which a
number or series of numbers may be skipped; in another form, the AutoNumber
can be specified as a replication ID or a Globally Unique ID -- GUID, but
you don't want to 'go there').

If the records added to the separate databases have an AutoNumber type
unique identifier field, your work will be complicated because both users
AutoNumber would have begun where the original database records stopped, and
you will have _duplicates_ of the same key, but identifying different
records. As these are the fields that are usually used to provide the join
between related tables, that can quickly get complicated.

If you can determine where that original database's records stopped, add an
additional field identifying the group to the main table and all related
tables in the original and the separat copies, make it part of the key of
the main table, set it with the identity of the person / group which
initiated the new records (e.g., maybe something as simple "E" for "Eastern
Division" and "W" for "Western Division" -- it will only be for the purpose
of disambiguating references to the records) then copy the original records,
and then append the records from each group to that, you may be able to do
this without quite as much work.

If, as is good practice, you made a backup copy and saved it in your
archives when you made two copies of the DB, then that would let you easily
determine where the copies' autonumbers began.

Look at the main table in design view, look for the field that identifies
the record, look at its type, to see if they are AutoNumber. Then you'll
need to determine the related tables -- those may be evident if you look at
the Relationships, but one doesn't HAVE to define relationships in Access
and some developers do not -- they just use the tables in Queries with
Joins.

Or, if the unique identification of the records is not AutoNumber, but a
number entered by the user, you may just be able to link and do a merge with
APPEND Queries.

In any case, whoever analyzes and tries to restructure/reorganize the
database has to understand what data is there, how it is stored, and work
very carefully (on a copy, repeat, ON A COPY) to try to remedy the
situation. If you begin it this probject on your own as a novice, you won't
likely remain in that status, not unless you run 'screaming into the night'.

Larry Linson
Microsoft Access MVP