Very urgent for autoincriment primary key

P

pol

Hi all,

I am using a primary column with long integer autoincriment in MS access
2007. Please let me know what would be the maximum value that can be stored
in this column.

Also please let me know what is the datatype replicationID for auto
incriment column.

With thanks
Pol
 
J

Jerry Whittle

If I remember correctly, about +/- 2 billion. Here it is from Help:

from -2,147,483,648 to 2,147,483,647

Unless you are using replication, you don't need the replicationID data type.
 
J

John Spencer

ReplicationID is a GUID (Globally Unique IDentifier)

FROM Wikipedia Search GUID or UUID for more information:

A globally unique identifier or GUID is a special type of identifier used in
software applications to provide a reference number which is unique in any
context (hence, "globally"), for example, in defining the internal reference
for a type of access point in a software application, or for creating unique
keys in a database. While each generated GUID is not guaranteed to be unique,
the total number of unique keys (2 to 128 power or 3.4 × 10 to the 38th
power) is so large that the probability of the same number being generated
twice is extremely small.

The term GUID usually refers to Microsoft's implementation of the Universally
Unique Identifier (UUID) standard. However, the term is common in applications
not written by Microsoft, or for their operating systems. The GUID is also the
basis of the GUID Partition Table, Intel's replacement for Master Boot Records
under EFI, and of Preboot Execution Environment, Intel's environment to boot
computers using a network interface.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
T

techrat

Just a quick word of caution - there are certain instances where you
will use up "Autonumbers" without ending up with a record for each
autonumber. ie if any records prior to the last record are deleted,
the Autonumber that was assigned to the deleted records are non-
recoverable. If the last record is deleted and followed by a compact
and repair however, that autonumber is recovered.

HTH
 
J

Jeff Boyce

What is your intended use?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
P

PvdG42

techrat said:
Just a quick word of caution - there are certain instances where you
will use up "Autonumbers" without ending up with a record for each
autonumber. ie if any records prior to the last record are deleted,
the Autonumber that was assigned to the deleted records are non-
recoverable. If the last record is deleted and followed by a compact
and repair however, that autonumber is recovered.

HTH

True, however I believe the real issue the OP should think about is that
(considering >=0 values only) the max possible - two billion plus- is far
more rows than would make sense in an Access table to begin with. So, to the
OP, do you plan on (considering possible 25% waste of autonumbers) having a
billion and a half rows in the table??
 
P

pol

Many thanks for the reply.

How I can initialise primary key auto incriment number in MSAccess 2007. I
want to restart from 1 . Please advice me
 
J

JeanPaulo

Many thanks for the reply.

How I can initialise primary key auto incriment number in MSAccess 2007. I
want to restart from 1 . Please advice me
Just 'export' any record as CSV,
then change the number to one
and 'import' the record back.

You can do the same with a query 'add'
 
D

Douglas J. Steele

Why? The only point of an AutoNumber is provide a (practically guaranteed)
value that can be used as a primary key. No meaning whatsoever should ever
be attributed to its value. In fact, it's normal not to even display its
value to the users.
 
T

techrat

If re-initializing the number back to the start is needed because you
have done some testing, used up values and want to reset for more
testing or to go live, you simply need to delete all records in the
table and compact and repair.

If needing to re-initialize your autonumber to 1 is something you
expect to need to do at run-time, I think that you may want to rethink
your design and use a non-autonumber data type and write a function in
VB to increment the value.
 
D

David W. Fenton

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=
Unless you are using replication, you don't need the replicationID
data type.

You don't need it even if you're using Replication.

Many would think that a random long integer would be dangerous if
you're adding lots of records in lots of replicas. But you run the
risk of creating duplicates randomly only if you're not synching
regularly. That is, the random long integer is not truly random --
it's only random chosen from the pool of values that have not yet
been used.

In two independent MDBs, the used-up pools will be different and
thus could result in duplicates fairly easily.

But in replicas from a single replica set, after a synch, any two
replicas will have exactly the same used-up pool of randomly
generated values. Thus, the chances for a collision are very small
as long as the replicas synch relatively frequently (where
"frequently" will determined by the rate at which new records are
being added -- the more that are added in a single day, the sooner
you need to synch with the replica set).

Doug Steele already cited Michael Kaplan's article on the problems
that come with using GUIDs in Access:

http://www.trigeminal.com/usenet/usenet011.asp

It causes more problems in replicated databases than in
non-replicated, since it breaks the built-in conflict resolver and
you then end up have to write your own conflict resolver to replace
it (not a triviality by any means).

The only time I'd ever contemplate using a GUID in an Access app is
if the data were not replicated and if there was an outside
requirement for its use.

Otherwise, it should be avoided.
 
A

Arvin Meyer [MVP]

techrat said:
Just a quick word of caution - there are certain instances where you
will use up "Autonumbers" without ending up with a record for each
autonumber. ie if any records prior to the last record are deleted,
the Autonumber that was assigned to the deleted records are non-
recoverable. If the last record is deleted and followed by a compact
and repair however, that autonumber is recovered.

HTH
 
A

Arvin Meyer [MVP]

techrat said:
Just a quick word of caution - there are certain instances where you
will use up "Autonumbers" without ending up with a record for each
autonumber. ie if any records prior to the last record are deleted,
the Autonumber that was assigned to the deleted records are non-
recoverable. If the last record is deleted and followed by a compact
and repair however, that autonumber is recovered.

That's hasn't been true since Access 97. Starting with Access 2000, the only
way to recover any autonumbers in a table is by deleting ALL the records in
a table, then compacting.
 
A

Arvin Meyer [MVP]

PvdG42 said:
True, however I believe the real issue the OP should think about is that
(considering >=0 values only) the max possible - two billion plus- is far
more rows than would make sense in an Access table to begin with. So, to
the OP, do you plan on (considering possible 25% waste of autonumbers)
having a billion and a half rows in the table??

The largest table that can be in Access is the database size or 2 GBs. Since
an autonumber is a long integer it uses 4 bytes. Therefore, if only the
autonumber was in the table, the maximum number of records allowed would be
512 million. far short of the 2,147,483,647 positive numbers that are in the
long integer. Essentially, one could add and delete well over 1.5 billion
records and still not be able to use all the positive autonumbers remaining.

Anyone building an Access database with much over 1 GB in size is asking for
trouble anyway because of the problems with bloating when adding and
deleting lots of records, and with several users locking records on each
other.
 
T

techrat

If the last record is deleted and followed by a compact
That's hasn't been true since Access 97. Starting with Access 2000, the only
way to recover any autonumbers in a table is by deleting ALL the records in
a table, then compacting.

Thanks for the correction on this and sorry for the mis-information!
I guess that autonoumber recovery is not something that I have had the
need to do since I was working in Access 97.

Thanks -Jeff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top