Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2003
    Location
    Minneapolis
    Posts
    21

    Unanswered: Can you start autonumber over?

    I have started a case dbase for my office to keep track of individual cases. This dbase autonumbers as we add to it. With the new year coming, I will be changing the default of 04 to 05 and am wondering if it is possible to get the autonumber to start at 1 again without losing my ability to perform searches against the old records in the system? Any ideas??? I don't vba, Sorry...

  2. #2
    Join Date
    Dec 2004
    Posts
    277
    will you be using the same table? or backup the table and start from fresh?

  3. #3
    Join Date
    Dec 2003
    Location
    Minneapolis
    Posts
    21
    I search against the table constantly so I wiould like the same table but am open to suggestions...

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Chopper4116
    I have started a case dbase for my office to keep track of individual cases. This dbase autonumbers as we add to it. With the new year coming, I will be changing the default of 04 to 05 and am wondering if it is possible to get the autonumber to start at 1 again without losing my ability to perform searches against the old records in the system? Any ideas??? I don't vba, Sorry...
    Hi Chopper,

    What exactly are you using the AutoNumber for? It really should be restricted to being used as a UniqueRecordIdentifier only. The default 04, is that a DateField? If you need a field that will automatically increment that you can change as you like, it would be best to create such a field and have it to Add +1 to the last existing number or use Max(SomeNumber) or GetLast(SomeNumber). There have been a few posts on using such a field.
    Of course, you CAN use the AutoNumber field for whatever you choose, but it is not a good thing to do. Also, IF you ever DELETE that number, you Can Not recover it. That number will be blank from that point on.
    Check the Access OnLine help. You can create any such number you like for the AutoNumber Field and modify it when you choose, which might kinda be what you want. Look into that and see how that works out.

    have a nice one,
    BUD

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The abridged version of the above is: primary keys are arbitrary. It should never matter to the user (or you) what the value of the primary key is.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by Teddy
    The abridged version of the above is: primary keys are arbitrary. It should never matter to the user (or you) what the value of the primary key is.
    So the overall answer to this persons question would be what? Can they re-start the AutoNumber Field at 1 again and still retain the previous values created in that field?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    no! autonumber CANNOT "start over"

    autonumbers are guaranteed unique... they cannot be unique if they "start over"!

    autonumber cannot be applied to a field that already contains data... a field must be empty in all rows to be set to autonumber. autonumber (long, sequential) applied to an empty field starts at 1 and stops at 2-point-something billion

    see Teddy's comments about "meaningless keys". to remove all temptation to suck meaning from keys switch your autonumber PK to random.

    if you want a repeating (or a seeded or a no-gap) sequence you must build it yourself. when you build your own sequence you must be prepared to handle multiple users requesting the next sequence number at the same moment.

    M Owen has a one-row-table sequence generator illustrated here. i don't see his optimistic lock being applied early enough in the process to be multi-user proof (but then i don't know ADO). my way of doing such things is via SQL to get the next value and checking .RowsAffected after
    UPDATE tblOwen SET fldSeq = wasNext + 1 WHERE fldSeq = wasNext
    to see if another user "stole" my sequence number before my update.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Bud
    So the overall answer to this persons question would be what? Can they re-start the AutoNumber Field at 1 again and still retain the previous values created in that field?
    N O. There are a few ways to "reset" autonumbers ... Duplicate a table structure and rename, Empty a table and run a Compact & Repair, and create a new table ... You see the pattern? EMPTY TABLE

    By definition, if you have rows in a table with an autonumber column, at "reset" the autonumber will take on the next highest # ...

    A solution to this problem would be to set change the autonumber column to just a long and make an external process the assign the next sequence # ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Allthough it probably defeats every purpose of the Autonumber, you can alter both the seed and increment of it anytime you like thru for instance:

    dim strsql as string
    strsql = "alter table YourTable alter column YourAutonumberField counter(1,1)"
    currentdb.execute strsql

    Or just run

    currentdb.execute "alter table YourTable alter column YourAutonumberField counter(1,1)"

    in the immediate pane (ctrl+g)

    Which will make the autonumber field start at 1, and increment by 1 per each record (creating "duplicate autonumbers", if you like).

    This is documented by Microsoft, because there is a bug/flaw in Access 2003
    http://support.microsoft.com/?scid=k...509&sid=global

    Note hovewer that using such, the autonumber field cannot be primary key, nor indexed with dupes = no. Else, I do agree that if an Autonumber is used as Primary Key, one is probably better off not exposing the field to the users.
    Roy-Vidar

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    autonumbers are guaranteed unique... they cannot be unique if they "start over"!

    autonumber cannot be applied to a field that already contains data... a field must be empty in all rows to be set to autonumber. autonumber (long, sequential) applied to an empty field starts at 1 and stops at 2-point-something billion

    see Teddy's comments about "meaningless keys". to remove all temptation to suck meaning from keys switch your autonumber PK to random.

    if you want a repeating (or a seeded or a no-gap) sequence you must build it yourself. when you build your own sequence you must be prepared to handle multiple users requesting the next sequence number at the same moment.

    M Owen has a one-row-table sequence generator illustrated here. i don't see his optimistic lock being applied early enough in the process to be multi-user proof (but then i don't know ADO). my way of doing such things is via SQL to get the next value and checking .RowsAffected after
    UPDATE tblOwen SET fldSeq = wasNext + 1 WHERE fldSeq = wasNext
    to see if another user "stole" my sequence number before my update.

    izy
    Izy,

    That code does optimistic locking (check the recordset LockType) ... It relies upon speed. Quickly opening, accessing, locking, updating, and releasing the row. It has not failed me yet in over 7 years under 4 languages ...

    EDIT:LockTypeEnum


    Specifies the type of lock placed on records during editing.

    Constant Value Description

    adLockOptimistic 3 Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
    Last edited by M Owen; 12-29-04 at 08:06.
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    M Owen

    sure - i saw (and mentioned) your optimistic lock.

    ...but the optimistic lock is not set until you issue the .update and (OK, theoretically) you can issue the same seq# to two users. pessimistic locking won't do much better either.

    UPDATE WHERE oldValue
    plus .recordsaffected makes it bulletproof.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    M Owen

    sure - i saw (and mentioned) your optimistic lock.

    ...but the optimistic lock is not set until you issue the .update and (OK, theoretically) you can issue the same seq# to two users. pessimistic locking won't do much better either.

    UPDATE WHERE oldValue
    plus .recordsaffected makes it bulletproof.

    izy
    Izy,

    HOW? I had to deal with this specific issue 7 years ago ... THink about the probability of 2 pc's requesting an ID at the EXACT same time accross the network ... Can't happen. Even the network itself is analog in that aspect so, one of the requests HAS TO GO 1st ... I've had people beat on this thing FOREVER ... And even if you overcome that, you still have to deal with the driver. The driver queues up the requests also ...

    - Mike
    Last edited by M Owen; 12-29-04 at 08:44.
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Mike

    i use belt & braces: it costs me a couple of lines...
    ...but saves me having to think about how likely are simultaneous requests or how SQL-Server receives & schedules a mixed bunch of SELECT, UPDATE, INSERT coming from four different continents or how "my" queries get inter-scheduled with other stuff for other instances running on the server.

    probably you are right and i'm fussing over nothing. as you said: it works for you.

    ?? does your mechanism throw an err (?? -2147217885 ??) if another user does an update after your read but before your update ??

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    Mike

    i use belt & braces: it costs me a couple of lines...
    ...but saves me having to think about how likely are simultaneous requests or how SQL-Server receives & schedules a mixed bunch of SELECT, UPDATE, INSERT coming from four different continents or how "my" queries get inter-scheduled with other stuff for other instances running on the server.

    probably you are right and i'm fussing over nothing. as you said: it works for you.

    ?? does your mechanism throw an err (?? -2147217885 ??) if another user does an update after your read but before your update ??

    izy
    Izy,

    Absolutely. It would ... It hasn't yet. That is the problem and the solution: Yes, theoretically a collision like this COULD occur ... But it hasn't. Is it because of the efficiency of the code and the relative speed of the network or just blind luck? I will say this: This is good only for within Access. I would perform exhaustive tests if the DB where switched to SQL Server, or MySQL, or some other backend because all bets are off as to the reliability of my sequence # code within those environments using those drivers.
    Back to Access ... ADO is not the way to go for speed ...

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    We took a different approach to the record locking issue as we ABSOLUTELY HAD to ensure a collision free environment. We would bounce an insert statement into the target table to actually create the key we wanted, then use updates after the user is finished with entry. The only issue with this method is if the user cancels entry, then you're left with either an empty record, or you have to go another step and delete the entry (which is what we did).
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •