Can 2 of the same dbs with a few AutoNumber columns be merged?

[Tim Cali]

How would you handle this scenario...

A client is currently using a split database. The DB works well, however now
they would like to create a copy of the database (FE/BE) to a disconnected
machine and enter more data there. 2 machines = twice as fast as entering
data.

How can this be done? The database is simple enough in structure so that
there will be no overlapping data *except for* AutoNumber IDs. In other
words, certain business rules will be enforced....however the technical side
of things is a different story. Let's say they set up a DB (empty BE/FE) on
a disconnected machine, and begin entering data. Further let's say that a
table has an AutoNumber column. As both people enter data, both independent
columns will increment their respective AutoNumber IDs in exactly the same
way, therefore creating duplicates that must be resolved when they BE's are
merged together.

One thought is, after the 2nd DB has been updated, I artifically increase
the table's IDs so that there be no duplicates. Since this entire scenario
is a "one-off" situation, this would not be out of the realm of possibility.
HOWEVER, I sure would like to know if there is a better way to do this. In
practicality, there are 2 tables with 3 AutoNumber IDs and I would have to
carefully "sync" up each of them in one of the DBs, and then merge it with
the 2nd DB. I imagine this could be a very tedious process. Is there
another, better/built-in way?

You know...after re-reading this, I am wondering if I can simply
artificially increase the base AutoNumberIDs in the 2nd backend *before* it
is put on the disconnected machine, so that it would be virtually impossible
for IDs to intersect. Then I could double-check the IDs before merging them,
and as long as there are no dupes, continue to merge.

Any input is greatly appreciated.

 

[Paul Overway]

Replication may be warranted in this situation. Your idea to artificially
increase the IDs in one of the databases will eventually be impractical, as
there would be overlap at some point....no getting around it. For more
info, see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/htm
l/dbrepjet.asp

 

[Larry Daugherty]

Tim,

I agree with Paul's assessment and recommendations. To that let me add
that I've been faced with a similar situation wherein two unattached and
unattachable computers running the same application need to share data. In
my case there was a great likelihood that some of the data might be
repetitive. I created Export and Import functions (Nothing to do with the
Access functions of the same name).

On the Export side the user could select from a list of names at the top
of the relevant tree and selectively include anything below that limb. The
selected branches are then copied into copies of tables and included in a
newly created ExIm.mdb.

On the Import side the user could select from all of the candidates it
finds in the ExIm.mdb. Once s/he has selected candidates for import then
s/he must indicate whether the imported branch already exists in the local
database. If it does, the name in the current database is selected and the
lower branches are imported by appending to the child tables with the
correct parentID as the foreign key all the while checking that redundant
entries aren't imported.

It's an eminently achievable thing. It just requires a lot of
forethought and attention to detail.

hth

 

[Van T. Dinh]

It sounds like Replication is the way to go. See the
Microsoft Knowledge Base articls:

<http://support.microsoft.com/?id=164553>

<http://support.microsoft.com/?id=282977>

You don't want 2 disjoint sets of data and you need some
way of synchronise / combine them into 1 set of data
eventually, hence Replication.

If you look at it this way, you have up to 4 billion
possible values for an AutoNumber Fields (which take up 4
byte * 2^32 = 16 GB to store) and an Access database has
max size of 2 GB so you will reach the max size limit
before you run out of values for AutoNumber Field.

HTH
Van T. Dinh
MVP (Access)

 

[Tim Cali]

Thanks Van and Paul.

One more question...I have never worked with replication before, and I need
to turn this request around. The articles seem very involved, and I am
wondering if there is a "canned" set of steps that I can follow that will
enable me to accomplish my goal reasonably quickly. If so, would you be so
kind as to let me know the steps involved? I'm looking for some
hand-holding that will enable me to get a better grasp on this. Perhaps a
simple walkthrough of "setting up your first replica set using a simple DB
as an example." The white paper from http://support.microsoft.com/?id=164553
is great at describing concepts (thx for the article...I'm reading it right
now), but it doesn't offer much in the way of "how to" do it. In addition,
it includes information for Access 97 and Windows NT. Are there any issues
with Access 2000 and Windows 2000 that I need to be aware of?

I agree with you that Replication is the way to go, based on what I have
read so far. Any more information pertaining to the implementation would be
a great help. Thank you.

 

[Van T. Dinh]

Most Access books (except those Idiot / Dummies / In 24 hours / ... ) have
step-by-step instructions on Replication.

Replication is still essentially the same as A97.

 

[Daran Johnson]

I would also like to add that if performance is an issue then now may be a
good time to propose a migration to a SQL Server backend with an ADP
frontend.

-Daran

 

[Arvin Meyer]

What! The man is talking about using 2 computers in a workgroup. Not only
does this comment not address the question, but how can SQL-Server improve
performance in this situation? As a matter of fact, with this level of light
usage, there's an extremely good chance that even a highly optimized
SQL-Server app won't run as fast.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Tim Cali]

Yeah, and most Access books suck.

Most Access books (except those Idiot / Dummies / In 24 hours / ... ) have
step-by-step instructions on Replication.

Replication is still essentially the same as A97.