ID field issue when combining databases

[Angie M.]

Hi, I have written a database for 6 sales reps. Each rep travels and will
need his/her database stored locally on their laptop. Periodically we will
need to combine all 6 databases into a "Master". I don't have any
flexibility to change this procedure. I have two questions:

1. How can I best set up the ID field (autonumber)? I've been searching
through all the posts and noticed you can use Format the ID field, but I
don't know if this actually transfers with the data or if it's just for looks
(Bob100, Bob101, etc. as the autonumber). Or I thought of starting each
database's autonumber field at the million mark - 1,000,000 for Bob,
2,000,000 for Tom, etc. Any ideas on this?

2. How do I best combine the databases? I would just manually import the
tables and then do an append query or use the "Transfer" or "Link" macros.
Am I on track here?

THANKS FOR YOUR HELP. And thanks for the many things I have learned by
reading other posts.

 

[Larry Daugherty]

Look in Help for "Replication". see also

microsoft.public.access.replication

HTH

 

[Angie M.]

Thanks Larry. After researching Replication and looking at our own
disfunctional work environment it was decided that we would not be able to do
that.

What other options do I have? Thanks

 

[John Vinson]

Thanks Larry. After researching Replication and looking at our own
disfunctional work environment it was decided that we would not be able to do
that.

What other options do I have? Thanks

If you really can't use replication - can you be sure that a "design
your own" system will be any better!?

About the best you can do is to avoid autonumbers entirely; use a
two-field primary key consisting of one field identifying the
salesperson (this can be defaulted to the person's ID in each
database) and a "custom counter". Use a Long Integer field and put
code in the Form's BeforeInsert event to increment and insert the
value:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[tablename]", _
"[PersonID] = " & Me.PersonID)) + 1
End Sub


John W. Vinson[MVP]

 

[Larry Daugherty]

A couple of notes about your application and Replication:

1. Only one person has to know the whole process and that person
can give directions to others as to what they must do so that you can
do the rest.

2. If your environment is too dysfunctional for Replication then
it's likely to be too dysfunctional for anything else. Whether a home
brewed solution or Replication there are processes that must be
followed in order for things to work.

I really have no axe to grind as far as Replication is concerned.

John is awesome. He has a solution or work around for just about
everything. IMHO you can absolutely, positively trust what he
proposes.

Good luck whatever the path you take.

HTH
--
-Larry-
--

Thanks Larry. After researching Replication and looking at our own
disfunctional work environment it was decided that we would not be able to do
that.

What other options do I have? Thanks

If you really can't use replication - can you be sure that a "design
your own" system will be any better!?

About the best you can do is to avoid autonumbers entirely; use a
two-field primary key consisting of one field identifying the
salesperson (this can be defaulted to the person's ID in each
database) and a "custom counter". Use a Long Integer field and put
code in the Form's BeforeInsert event to increment and insert the
value:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[tablename]", _
"[PersonID] = " & Me.PersonID)) + 1
End Sub


John W. Vinson[MVP]

 

[Angie M.]

Thank you Larry and John. Thanks for the work around, I'm also going to take
another look at replication.

A couple of notes about your application and Replication:

1. Only one person has to know the whole process and that person
can give directions to others as to what they must do so that you can
do the rest.

2. If your environment is too dysfunctional for Replication then
it's likely to be too dysfunctional for anything else. Whether a home
brewed solution or Replication there are processes that must be
followed in order for things to work.

I really have no axe to grind as far as Replication is concerned.

John is awesome. He has a solution or work around for just about
everything. IMHO you can absolutely, positively trust what he
proposes.

Good luck whatever the path you take.

HTH
--
-Larry-
--

Thanks Larry. After researching Replication and looking at our own
disfunctional work environment it was decided that we would not be able to do
that.

What other options do I have? Thanks

If you really can't use replication - can you be sure that a "design
your own" system will be any better!?

About the best you can do is to avoid autonumbers entirely; use a
two-field primary key consisting of one field identifying the
salesperson (this can be defaulted to the person's ID in each
database) and a "custom counter". Use a Long Integer field and put
code in the Form's BeforeInsert event to increment and insert the
value:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[tablename]", _
"[PersonID] = " & Me.PersonID)) + 1
End Sub


John W. Vinson[MVP]