2 databases using the same autoumbers, how can I merge them into 1

I have two databases that have identical structures. Some of the records are
unique in terms of their actual content, but across the two databases in
certain tables mentioned below they are using the same AutoID (PK) numbers.

My question is, if I cut and paste a parent table's data (minus the
Autonumber), which would create a new autoumber for each pasted record, then
when I paste child table, will the records synch?. If not, How can I tackle
this problem?. I don’t mind if any of the ID numbers change, except
[Point_ID].


These are the 3 tables that are linked; the only PK that cannot change is
[Point_ID] in the main master table, tbl_points.

tbl_points (Master table to ‘tbl_Getrounds’)
[Point_ID] (PK)

tbl_Getrounds (Master table to ‘Tbl_Getround_Detail’)
[GetRound_ID[ (PK)

Tbl_Getround_Detail (child table to ‘tbl_Getrounds’)
[GetRound_Detail_ID] (PK)
[GetRound_ID] (child link to: tbl_Getrounds. GetRound_ID

On Sat, 30 May 2009 14:17:01 -0700, efandango
wrote:

I have two databases that have identical structures. Some of the records are
unique in terms of their actual content, but across the two databases in
certain tables mentioned below they are using the same AutoID (PK) numbers.

My question is, if I cut and paste a parent table's data (minus the
Autonumber), which would create a new autoumber for each pasted record, then
when I paste child table, will the records synch?. If not, How can I tackle
this problem?. I don’t mind if any of the ID numbers change, except
[Point_ID].


These are the 3 tables that are linked; the only PK that cannot change is
[Point_ID] in the main master table, tbl_points.

tbl_points (Master table to ‘tbl_Getrounds’)
[Point_ID] (PK)

tbl_Getrounds (Master table to ‘Tbl_Getround_Detail’)
[GetRound_ID[ (PK)

Tbl_Getround_Detail (child table to ‘tbl_Getrounds’)
[GetRound_Detail_ID] (PK)
[GetRound_ID] (child link to: tbl_Getrounds. GetRound_ID

I would not recommend using copy and paste - an Append query is a more
"Access-ish" solution!

The fact that you have related records in child tables makes life a lot more
complicated. No, they will NOT synch; the foreign key field in
Tbl_GetRound_Detail will retain whatever value it had in the source table, and
will NOT automagically figure out the corresponding record's new ID. This may
require temporarily adding a NewPointID and/or NewGetRoundID field
(autonumber) while retaining the old one, and running some Update queries to
synch up the foreign keys in related tables.
--

John W. Vinson [MVP]

John,

thanks for your reply. It is as I feared... I can temporarily add additional
ID fields, you mean that when I do the append it will create new autonumbers,
and then I update against the old index numbers relating to each other in the
relevant tables, right?

on the same note, I will be getting this problem from time to time. There
are two databases in existance at two seperate sites; thinking longer term,
is there a concept/system that I can utilise to make this process less
painful in future?





"John W. Vinson" wrote:

On Sat, 30 May 2009 14:17:01 -0700, efandango
wrote:

I have two databases that have identical structures. Some of the records are
unique in terms of their actual content, but across the two databases in
certain tables mentioned below they are using the same AutoID (PK) numbers.

My question is, if I cut and paste a parent table's data (minus the
Autonumber), which would create a new autoumber for each pasted record, then
when I paste child table, will the records synch?. If not, How can I tackle
this problem?. I don’t mind if any of the ID numbers change, except
[Point_ID].


These are the 3 tables that are linked; the only PK that cannot change is
[Point_ID] in the main master table, tbl_points.

tbl_points (Master table to ‘tbl_Getrounds’)
[Point_ID] (PK)

tbl_Getrounds (Master table to ‘Tbl_Getround_Detail’)
[GetRound_ID[ (PK)

Tbl_Getround_Detail (child table to ‘tbl_Getrounds’)
[GetRound_Detail_ID] (PK)
[GetRound_ID] (child link to: tbl_Getrounds. GetRound_ID

I would not recommend using copy and paste - an Append query is a more
"Access-ish" solution!

The fact that you have related records in child tables makes life a lot more
complicated. No, they will NOT synch; the foreign key field in
Tbl_GetRound_Detail will retain whatever value it had in the source table, and
will NOT automagically figure out the corresponding record's new ID. This may
require temporarily adding a NewPointID and/or NewGetRoundID field
(autonumber) while retaining the old one, and running some Update queries to
synch up the foreign keys in related tables.
--

John W. Vinson [MVP]

On Sun, 31 May 2009 05:51:01 -0700, efandango
wrote:

John,

thanks for your reply. It is as I feared... I can temporarily add additional
ID fields, you mean that when I do the append it will create new autonumbers,
and then I update against the old index numbers relating to each other in the
relevant tables, right?

If you run an APPEND query to a table containing an autonumber field, and do
not include that field in the query, then the autonumber will be incremented
with new values. And yes, you can then run update queries to update the
relevant foreign key values.

on the same note, I will be getting this problem from time to time. There
are two databases in existance at two seperate sites; thinking longer term,
is there a concept/system that I can utilise to make this process less
painful in future?

Several solutions, none of them ideal or all that easy:

- use a common SQL/Server backend accessible from both sites
- Use Access Replication with direct or internet synchronization
- Use a joint two-field primary key consisting of the site and an
programmatically incremented custom counter, so you can combine the tables
(and as a bonus identify which site entered the record)
- use a Random autonumber and hope that you don't get collisions (Replication
will do this for you)
--

John W. Vinson [MVP]

John,

Having recovered from the shock of realising that I have created a potential
monster... I have thought about the options available to me.

1. SQL Server is beyond my means and abiliites

2. Replication seems too complex for my abilities and may mess up my Db with
its myriad references on combo boxes, and VB stuff.

3. ditto random autonumbers (replication)

4. A joint two-field Primary Key could have possibliites for me. How is it
implemented?, and how easy/difficult would it be to adapt my existing
database?

do you know of any sample access databases online that use it?, that maybe I
could take a look at?



"John W. Vinson" wrote:

On Sun, 31 May 2009 05:51:01 -0700, efandango
wrote:

John,

thanks for your reply. It is as I feared... I can temporarily add additional
ID fields, you mean that when I do the append it will create new autonumbers,
and then I update against the old index numbers relating to each other in the
relevant tables, right?

If you run an APPEND query to a table containing an autonumber field, and do
not include that field in the query, then the autonumber will be incremented
with new values. And yes, you can then run update queries to update the
relevant foreign key values.

on the same note, I will be getting this problem from time to time. There
are two databases in existance at two seperate sites; thinking longer term,
is there a concept/system that I can utilise to make this process less
painful in future?

Several solutions, none of them ideal or all that easy:

- use a common SQL/Server backend accessible from both sites
- Use Access Replication with direct or internet synchronization
- Use a joint two-field primary key consisting of the site and an
programmatically incremented custom counter, so you can combine the tables
(and as a bonus identify which site entered the record)
- use a Random autonumber and hope that you don't get collisions (Replication
will do this for you)
--

John W. Vinson [MVP]

On Mon, 1 Jun 2009 16:21:01 -0700, efandango
wrote:

John,

Having recovered from the shock of realising that I have created a potential
monster... I have thought about the options available to me.

1. SQL Server is beyond my means and abiliites

2. Replication seems too complex for my abilities and may mess up my Db with
its myriad references on combo boxes, and VB stuff.

3. ditto random autonumbers (replication)

4. A joint two-field Primary Key could have possibliites for me. How is it
implemented?, and how easy/difficult would it be to adapt my existing
database?

do you know of any sample access databases online that use it?, that maybe I
could take a look at?

Well, I don't know for sure where you're starting from but it's not all that
hard. Try it on a copy of the database first!

Open the table in question in design view and change its Autonumber to
Number... Long Integer. Add a new field Location (or of course whatever name
makes sense); I'd suggest using it as a Text field so if you have offices in
Dallas, St. Louis and New York City you'ld allow for those entries.

Ctrl-click the ID (what used to be the autonumber) and the Location field so
they're both highlighted and click the Key icon to make the two fields a
joint, two-field primary key.

Set the DefaultValue property of the Location field to the name of the
location for that user - you'll end up with three databases with different
defaults.

In whatever Form you use to enter data into this table put code in its
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!IDfield = NZ(DMax("[IDfield]", "[tablename]",
"[Location] = '" & Me!Location & "'")) + 1
End Sub

You should have a "master" copy of the database; your remote users will need
to either email or otherwise send (on CD, thumb drive, FTP, ...) their
database. You can link to the table in their database and run an Append query
to migrate the data into the "master" table - with the joint primary key, you
can have an ID number 12345 three times, just so the three records all have
different locations.

You will of course need to modify your forms, probably your "find record"
combo boxes, etc. - this won't be trivial but it's very much doable.
--

John W. Vinson [MVP]