|
|
|
|
Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
news:Xns992575F5875E0atomicstorm27hotmail[ at ]207.46.248.16:
[Quoted Text] > Is it possible?
Yes.
Now, ask your real question.
And: what does this have to do with replication?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
"David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
news:Xns9925AF5743313f99a49ed1d0c49c5bbb2[ at ]127.0.0.1:
[Quoted Text] > Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
> news:Xns992575F5875E0atomicstorm27hotmail[ at ]207.46.248.16:
>
>> Is it possible?
>
> Yes.
>
> Now, ask your real question.
>
> And: what does this have to do with replication?
>
Well, it has to do with replication because when I created a replica, all
of my records got renumbered - I had the table organized by vendor,
product, type of application, etc. and now they are all mixed up.
I posted this in replication because I would like to know why, when a
database is replicated, the records get renumbered AND how to fix it.
I even went through the trouble of recreating one of my smaller tables to
see if it would do the same thing and it did. WHY?! So frustrating...
|
|
Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
news:Xns99264A1AD4214atomicstorm27hotmail[ at ]207.46.248.16:
[Quoted Text] > "David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
> news:Xns9925AF5743313f99a49ed1d0c49c5bbb2[ at ]127.0.0.1:
>
>> Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
>> news:Xns992575F5875E0atomicstorm27hotmail[ at ]207.46.248.16:
>>
>>> Is it possible?
>>
>> Yes.
>>
>> Now, ask your real question.
>>
>> And: what does this have to do with replication?
>
> Well, it has to do with replication because when I created a
> replica, all of my records got renumbered - I had the table
> organized by vendor, product, type of application, etc. and now
> they are all mixed up.
No, existing records do not get renumbered, but any Autonumber field
is converted from sequential to random. This is a necessity for
replication -- otherwise the simplest record additions would create
primary key value collisions between replicas.
> I posted this in replication because I would like to know why,
> when a database is replicated, the records get renumbered AND how
> to fix it.
Existing data does not change. If you have 100 records in a table
with an Autonumber value running in sequence from 1 to 100,
replicating does *not* alter those values. But the next record added
will be randomly generated from the billions of possible values
storable in an Autonumber field (which is of type Long Integer).
> I even went through the trouble of recreating one of my smaller
> tables to see if it would do the same thing and it did. WHY?! So
> frustrating...
Consider why this is the case -- if replication did not use random
autonumbers, here's what would happen:
1. User at replica A adds a record to Table A. It is given number
101.
2. User at replia B adds a record to Table A. It is given number
101.
3. the two users synchronize their data additions, and there are two
records with number 101, which can't be, because it's the primary
key and has to be unique.
If the numbers are randomly chosen, it would take an extremely large
number of additions to produce a collision between two replicas.
I've been doing replicated databases since 1997 and have never once
seen a random autonumber collision.
Now, another issue is that you may be wrongly exposing the
autonumber values to users. You should not be doing this, as the
only purpose of an Autonumber primary key is to join together
related records. It's not a real value. It doesn't matter to the
database if the next value is 101 or -103986746. If it matters in
your application, then you have a design flaw.
That said, there are cases in which one needs to maintain a unique
number sequence across all replicas. In that case, you have three
choices:
1. pre-allocate blocks of the sequence to each replica.
2. use a two-column sequence instead, with one column indicating the
source replica, and the other the next sequential value.
3. assign temporary values in the replicas and then assign the
permanent values in a single administrative replica.
Each of these choices has its problems, but the problems are not
caused by the implementation of Jet Replication, but by the fact
that you are using a replicated database, where records can be added
to a table in multiple locations.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
"David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
news:Xns9926BBC9B99D0f99a49ed1d0c49c5bbb2[ at ]127.0.0.1 on Fri 04 May 2007
03:27:37p:
[Quoted Text] > Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
> news:Xns99264A1AD4214atomicstorm27hotmail[ at ]207.46.248.16:
>
>> "David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
>> news:Xns9925AF5743313f99a49ed1d0c49c5bbb2[ at ]127.0.0.1:
>>
>>> Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
>>> news:Xns992575F5875E0atomicstorm27hotmail[ at ]207.46.248.16:
>>>
>>>> Is it possible?
>>>
>>> Yes.
>>>
>>> Now, ask your real question.
>>>
>>> And: what does this have to do with replication?
>>
>> Well, it has to do with replication because when I created a
>> replica, all of my records got renumbered - I had the table
>> organized by vendor, product, type of application, etc. and now
>> they are all mixed up.
>
> No, existing records do not get renumbered, but any Autonumber field
> is converted from sequential to random. This is a necessity for
> replication -- otherwise the simplest record additions would create
> primary key value collisions between replicas.
>
>> I posted this in replication because I would like to know why,
>> when a database is replicated, the records get renumbered AND how
>> to fix it.
>
> Existing data does not change. If you have 100 records in a table
> with an Autonumber value running in sequence from 1 to 100,
> replicating does *not* alter those values. But the next record added
> will be randomly generated from the billions of possible values
> storable in an Autonumber field (which is of type Long Integer).
>
>> I even went through the trouble of recreating one of my smaller
>> tables to see if it would do the same thing and it did. WHY?! So
>> frustrating...
>
> Consider why this is the case -- if replication did not use random
> autonumbers, here's what would happen:
>
> 1. User at replica A adds a record to Table A. It is given number
> 101.
>
> 2. User at replia B adds a record to Table A. It is given number
> 101.
>
> 3. the two users synchronize their data additions, and there are two
> records with number 101, which can't be, because it's the primary
> key and has to be unique.
>
> If the numbers are randomly chosen, it would take an extremely large
> number of additions to produce a collision between two replicas.
> I've been doing replicated databases since 1997 and have never once
> seen a random autonumber collision.
>
> Now, another issue is that you may be wrongly exposing the
> autonumber values to users. You should not be doing this, as the
> only purpose of an Autonumber primary key is to join together
> related records. It's not a real value. It doesn't matter to the
> database if the next value is 101 or -103986746. If it matters in
> your application, then you have a design flaw.
>
> That said, there are cases in which one needs to maintain a unique
> number sequence across all replicas. In that case, you have three
> choices:
>
> 1. pre-allocate blocks of the sequence to each replica.
>
> 2. use a two-column sequence instead, with one column indicating the
> source replica, and the other the next sequential value.
>
> 3. assign temporary values in the replicas and then assign the
> permanent values in a single administrative replica.
>
> Each of these choices has its problems, but the problems are not
> caused by the implementation of Jet Replication, but by the fact
> that you are using a replicated database, where records can be added
> to a table in multiple locations.
>
Oof, ok... so I could:
1 - Get rid of replication (which would complicate matters further)
- or -
2 - Sort the records through a complex query
Is there a timestamp or something I could use in a "hidden" field to sort
the records? Something invisible to the user?
Here's the deal - I have an Excel userform that's pulling the data from the
database. But because my sales people are moble I need to keep the mdb's
sync'd. Maybe I'm not going about this the right way, but I was planning to
have the Excel userform (when loaded) send an update query to the local mdb
which will (hopefully) cause the local mdb to update itself with the DM on
the server. I would like to keep the DM sorted a certain way because we
work with a dozen different vendors and they all have hundreds of... you
know... nevermind. I think I can keep it sorted through a form in access
and, when I'm inputting new products, have it check for duplicates...
Ok - Thanks for the explaination Mr. Fenton.
|
|
Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
news:Xns992955D8BFF83jgwinstrommicrosoft[ at ]207.46.248.16:
[Quoted Text] > 1 - Get rid of replication (which would complicate matters
> further) - or -
> 2 - Sort the records through a complex query
You mean you were using the Autonumber to sort the records? That is
inadvisable even in a non-replicated database, as there's no
guarantee that it will reflect the order of addition. An Autonumber
is just a special kind of default value, and you can append a value
to an Autonumber field if it doesn't collide with existing values.
Thus, if you had a gap in the sequence, you could append a record
with the missing value in the Autonumber column and fill it in. Then
the Autonumber values would no longer represent the sequence in
which the records were added.
> Is there a timestamp or something I could use in a "hidden" field
> to sort the records? Something invisible to the user?
There's nothing invisible, but you can add a Created field and set
the default value to Now(). However, that's going to work only if
you have all the clocks set to exactly the same time.
> Here's the deal - I have an Excel userform that's pulling the data
> from the database. But because my sales people are moble I need to
> keep the mdb's sync'd. Maybe I'm not going about this the right
> way, but I was planning to have the Excel userform (when loaded)
> send an update query to the local mdb which will (hopefully) cause
> the local mdb to update itself with the DM on the server.
Hold on! The Design Master should not be participating in regular
editing and sychronizing activity. The purpose of the DM is to make
DESIGN CHANGES. It should be squirrelled away somewhere safe and
used only when you're implementing a change to the table structures.
You do have to synch with it often enough to keep it from expiring,
but the default retention period is 1000 days, so that's not very
often.
Now, in place of the DM you should have a hub database that is your
central repository. That would be a regular replica, *not* the DM.
I also do not understand why you need to involve Excel in this --
that makes it *harder* to design proper data editing forms, and it's
also harder to initiate synchs between replicas (I assume it can be
done but wouldn't have a clue how it's done, as I don't program in
Excel).
> I would like to keep the DM sorted a certain way because we
> work with a dozen different vendors and they all have hundreds
> of... you know... nevermind. I think I can keep it sorted through
> a form in access and, when I'm inputting new products, have it
> check for duplicates...
Data in tables is not sorted no matter what you may think. The only
order that is ever there is one that is imposed upon it. If there is
no logical sort order for the data based on the fields in your
table, then there is no logical sort order for the table at all.
It's not clear to me what you want or need here. You seem to have
been depending on a sorting method that was invalid to begin with,
and now you see *why* it was invalid. You haven't explained why it's
important to the users that the records be presented in the order
they were added, so it's hard to say what the best way to keep them
in that order would be.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
"David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
news:Xns9929B365C3769f99a49ed1d0c49c5bbb2[ at ]127.0.0.1 on Mon 07 May 2007
02:38:08p:
[Quoted Text] > You mean you were using the Autonumber to sort the records? That is
> inadvisable even in a non-replicated database, as there's no
> guarantee that it will reflect the order of addition. An Autonumber
> is just a special kind of default value, and you can append a value
> to an Autonumber field if it doesn't collide with existing values.
> Thus, if you had a gap in the sequence, you could append a record
> with the missing value in the Autonumber column and fill it in. Then
> the Autonumber values would no longer represent the sequence in
> which the records were added.
Nope, I wasn't using Autonumber for sorting. I was entering products from
manufacturers' literature that was in a certain order. I wanted to maintain
that order and I thought the database would do that... and it did until I
replicated it.
> There's nothing invisible, but you can add a Created field and set
> the default value to Now(). However, that's going to work only if
> you have all the clocks set to exactly the same time.
Seeing as how it's only going to be me or one other person doing updates to
this database, I think that would work...
> Hold on! The Design Master should not be participating in regular
> editing and sychronizing activity. The purpose of the DM is to make
> DESIGN CHANGES. It should be squirrelled away somewhere safe and
> used only when you're implementing a change to the table structures.
> You do have to synch with it often enough to keep it from expiring,
> but the default retention period is 1000 days, so that's not very
> often.
>
> Now, in place of the DM you should have a hub database that is your
> central repository. That would be a regular replica, *not* the DM.
The only database in existence, other than the DM, is the db on my laptop
(for now). Are you saying I should be adding records etc to the db on my
laptop instead of the DM unless it involves a design change? Why, what's
the difference? (sorry I'm *really* new to this)
> I also do not understand why you need to involve Excel in this --
> that makes it *harder* to design proper data editing forms, and it's
> also harder to initiate synchs between replicas (I assume it can be
> done but wouldn't have a clue how it's done, as I don't program in
> Excel).
Excel is involved because I need the spreadsheets for my estimators. They
input all the details from the job, the materials and material pricing are
loaded from the database and formulas in the Excel spreadsheet calculates
my job and material costing. Besides, all of our people have Excel; only a
few of us have Access - don't ask me why! :)
> Data in tables is not sorted no matter what you may think. The only
> order that is ever there is one that is imposed upon it. If there is
> no logical sort order for the data based on the fields in your
> table, then there is no logical sort order for the table at all.
So could I add a field (ie ItemNo) and use my own numerical values to keep
my data sorted?
> It's not clear to me what you want or need here. You seem to have
> been depending on a sorting method that was invalid to begin with,
> and now you see *why* it was invalid. You haven't explained why it's
> important to the users that the records be presented in the order
> they were added, so it's hard to say what the best way to keep them
> in that order would be.
What I want/need is a visible, sorted order of material records so I don't
run into duplication of materials, manufacturers, etc.
I appologize for this seeming so complicated. Maybe because I'm new to
Access, I'm looking for something I can't get from Access.
Thank you for your patience, though. I really do appreciate the help on
this. :)
|
|
Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
news:Xns992A54E8B8FD9jgwinstrommicrosoft[ at ]207.46.248.16:
[Quoted Text] > "David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
> news:Xns9929B365C3769f99a49ed1d0c49c5bbb2[ at ]127.0.0.1 on Mon 07 May
> 2007 02:38:08p:
>
>> You mean you were using the Autonumber to sort the records? That
>> is inadvisable even in a non-replicated database, as there's no
>> guarantee that it will reflect the order of addition. An
>> Autonumber is just a special kind of default value, and you can
>> append a value to an Autonumber field if it doesn't collide with
>> existing values. Thus, if you had a gap in the sequence, you
>> could append a record with the missing value in the Autonumber
>> column and fill it in. Then the Autonumber values would no longer
>> represent the sequence in which the records were added.
>
> Nope, I wasn't using Autonumber for sorting. I was entering
> products from manufacturers' literature that was in a certain
> order. I wanted to maintain that order and I thought the database
> would do that... and it did until I replicated it.
No, it really didn't. It's just that a datasheet will very often
default to using the first column for its sort, and if that first
column is a sequential Autonumber, then it will be in Autohumber
order.
But you can't actually rely on that happening as there are a number
of factors involved. And it applies only to table datasheets. Once
you display a datasheet in a form, anything can happen. In a report,
same thing.
[]
>> Hold on! The Design Master should not be participating in regular
>> editing and sychronizing activity. The purpose of the DM is to
>> make DESIGN CHANGES. It should be squirrelled away somewhere safe
>> and used only when you're implementing a change to the table
>> structures. You do have to synch with it often enough to keep it
>> from expiring, but the default retention period is 1000 days, so
>> that's not very often.
>>
>> Now, in place of the DM you should have a hub database that is
>> your central repository. That would be a regular replica, *not*
>> the DM.
>
> The only database in existence, other than the DM, is the db on my
> laptop (for now). Are you saying I should be adding records etc to
> the db on my laptop instead of the DM unless it involves a design
> change? Why, what's the difference? (sorry I'm *really* new to
> this)
If you need two people editing data in different locations, there
need to be two replicas in addition to the DM.
[]
>> Data in tables is not sorted no matter what you may think. The
>> only order that is ever there is one that is imposed upon it. If
>> there is no logical sort order for the data based on the fields
>> in your table, then there is no logical sort order for the table
>> at all.
>
> So could I add a field (ie ItemNo) and use my own numerical values
> to keep my data sorted?
Yes, but if you do that, you have the problem of maintaining a
numeric sequence in replicated databases. It's the same problem in a
different guise. I gave 3 solutions to that problem a couple of
posts back.
>> It's not clear to me what you want or need here. You seem to have
>> been depending on a sorting method that was invalid to begin
>> with, and now you see *why* it was invalid. You haven't explained
>> why it's important to the users that the records be presented in
>> the order they were added, so it's hard to say what the best way
>> to keep them in that order would be.
>
> What I want/need is a visible, sorted order of material records so
> I don't run into duplication of materials, manufacturers, etc.
Duplication should be prevented by the indexes in the data tables,
not by a human being scanning a list in a particular order.
> I appologize for this seeming so complicated. Maybe because I'm
> new to Access, I'm looking for something I can't get from Access.
You're making the same mistakes that many people make in regard to
basic database concepts.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
"David W. Fenton" <XXXusenet[ at ]dfenton.com.invalid> wrote in
news:Xns992AA9EBF28F1f99a49ed1d0c49c5bbb2[ at ]127.0.0.1 on Tue 08 May 2007
01:42:15p:
[Quoted Text] >>
>> Nope, I wasn't using Autonumber for sorting. I was entering
>> products from manufacturers' literature that was in a certain
>> order. I wanted to maintain that order and I thought the database
>> would do that... and it did until I replicated it.
>
> No, it really didn't. It's just that a datasheet will very often
> default to using the first column for its sort, and if that first
> column is a sequential Autonumber, then it will be in Autohumber
> order.
>
> But you can't actually rely on that happening as there are a number
> of factors involved. And it applies only to table datasheets. Once
> you display a datasheet in a form, anything can happen. In a report,
> same thing.
>
>
>> The only database in existence, other than the DM, is the db on my
>> laptop (for now). Are you saying I should be adding records etc to
>> the db on my laptop instead of the DM unless it involves a design
>> change? Why, what's the difference? (sorry I'm *really* new to
>> this)
>
> If you need two people editing data in different locations, there
> need to be two replicas in addition to the DM.
>
>>
>> So could I add a field (ie ItemNo) and use my own numerical values
>> to keep my data sorted?
>
> Yes, but if you do that, you have the problem of maintaining a
> numeric sequence in replicated databases. It's the same problem in a
> different guise. I gave 3 solutions to that problem a couple of
> posts back.
>
>>
>> What I want/need is a visible, sorted order of material records so
>> I don't run into duplication of materials, manufacturers, etc.
>
> Duplication should be prevented by the indexes in the data tables,
> not by a human being scanning a list in a particular order.
>
>> I appologize for this seeming so complicated. Maybe because I'm
>> new to Access, I'm looking for something I can't get from Access.
>
> You're making the same mistakes that many people make in regard to
> basic database concepts.
>
I don't really understand this so I'm going to just let it go for now. I
need to do some more reading on Access and get more familiar with the
concepts behind the program and its features before I start asking
questions.
Thanks for your help David.
|
|
Atomic Storm <jswinstrom[ at ]microsoft.com> wrote in
news:Xns992A91DBA9058jgwinstrommicrosoft[ at ]207.46.248.16:
[Quoted Text] > I don't really understand this so I'm going to just let it go for
> now. I need to do some more reading on Access and get more
> familiar with the concepts behind the program and its features
> before I start asking questions.
What parts do you not understand?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
Yes. reset the numbering, Just remove the autonumber field, close the table,
then reopen tha table and re-enter the field. it will then beging to renumber
the records. make sure your starting numbers are what you want.
"Atomic Storm" wrote:
[Quoted Text] > Is it possible?
>
|
|
rtviper <rtviper[ at ]discussions.microsoft.com> wrote in
news:04903550-6B69-430B-B206-7E37CBB1C2A8[ at ]microsoft.com:
[Quoted Text] > "Atomic Storm" wrote:
>
>> Is it possible?
>
> Yes. reset the numbering, Just remove the autonumber field, close
> the table, then reopen tha table and re-enter the field. it will
> then beging to renumber the records. make sure your starting
> numbers are what you want.
This is not a replication question. Why is it here?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
|