Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: Parameter in ALTER DDL?

    I have a Microsoft fix to reseed autonumber in primary key column for Access 2003 from http://support.microsoft.com/kb/8841...d=2509&sid=106.
    I am trying to use a parameter so the user can enter the value for reseeding, instead of modifying the sql itself, but Access gives "syntax error in field definition" if we don't use a numeric in the alter table/alter column sql. Is parameter allowed in DDL? If so, got an example? Thanks for help.
    I am new to this forum, so probably posted in wrong place...

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    honestly: no idea at all.

    i have never had a database in access/JET that came close to the 2,xxx,xxx,xxx autonumber limit so i never felt the need to reseed.

    the mere concept of reseeding based on prompted user input frightens the $#1t out of me: WHY? do you want to do this?

    an autonumber should NEVER be reseeded (pace pootle flump who likes to send his applications out to the punters with virgin autonumbers). the autonumber has ZERO meaning unless it is used as foreign key in another table ...in which case, reseeding is fatal to the database!

    are you sure you are trying to do something that needs to be done ????

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2008
    Posts
    4
    I volunteer some help at a church food pantry.
    They use access to track who gets food and how often.
    Somehow, recently, when they wanted to add a new client number that should haver automatically been 4nnn, the autonumber column was corrupted and held a value of 3nnn, which gave them a duplicate key error, so they could not create a new client. We would like to fix this in the least painful way. The MS fix works fine, but I'd like to set up an SQL they could use in case this happens again, but without them modifying the sql itself, hence my thought about a parameter.
    They would know what the next available autonumber should be, just by perusing the table with the corrupted autonumber.
    Last edited by jimmajsterski; 06-27-08 at 15:51.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - i cannot help!

    i really worry about reseeding in a live database: if the "autonumber" is used as foreign (surrogate) key by another table, you will kill your database. if your "autonumber" is used for something else other than surrogate key, the database design is fundamentally wrong.

    i hope someone else here on the site will try to help.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2008
    Posts
    4
    I made the mistake of stating "reseeding".
    What I really want to do is RESET the corrupted autonumber increment field to where we know it should be, not blow away what is already there, because yes I realize relationships would be destroyed if the table primary keys were renumbered. Thanks for your interest, izyrider.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    LOLZ - this is a little too involved for me right now (friday night in blighty) but it is "doable". Izy - I can't believe you remember that.

    One thing is that an autonumber should really be used as a convenient way of implementing a surrogate key (google that if it rings no bells) which should not be displayed to users. However, they can corrupt and reseeding can help.

    If you concatenate the user input into a DDL string and execute via VBA (ADO or DAO) it *should* work. So - what's your exact code dude?

  7. #7
    Join Date
    Jun 2008
    Posts
    4
    No Code.
    The table is updated/queried by Access forms, queried by reports.

    The table is named "Client", the primary key column is named "ContactID", and they do use ContactID for some activities.


    The one line Microsoft fix is:

    Alter table Client alter column ContactID counter(nnnn,1)

    where nnnn is the value we want to plug into the autonumber increment field as the next available new record number. (The new record number is what got corrupted).

    I wanted to use a parameter in the DDL for "nnnn", such as:
    Alter table Client alter column ContactID counter([Enter Next ID],1)
    but Access balks.

    May try a small VBA ADO program unless I hear a better idea. Need to learn how, can't be too tough since I know some VB.

    Thanks for help, guys.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like the age old problem of using an autonumber column to have a meaning in the outside world.

    yes you can reseed, but why would you want to?
    if you have blown the 2g limit then why are you using JET

    I'd consider a "compact and repair" to see if that clears the fault on the autonumber
    I'd consider devising a different process to assign a client number if you must have contiguous numbers
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well it won't work - DDL statements are statements not expressions.

    I didn't realise you had veered away from the MS article - you have the code right there for you in that article. Just implement that.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The only time I've seen autonumbers like 2,343,212,343 is after using MSAccess replication which was a nightmare working with (MSAccess did this to reduce the risk that the same autonumber would be generated in a multi-user environment). I had big problems maintaining a consistent true relationship between the tables with MSAccess generating such large autonumbers (delete a relational record and somehow orphaned records started appearing in other tables.) Of course, this could have been the replication process itself which I wasn't very fond of.

    Compact and Repair is supposed to help with clearing faults on autonumbers but I think once you've gotten to the point where they're intricately involved in your relationships, it's a futile process.

    I am curious on how many records you are actually dealing with in the tables. And how many linked tables. I'm also curious on WHY you would want to do this versus just using your large numbers as a secondary field and a "mimicked" relationship. You might be surprised at how accurate the autonumber is on your joins versus manually creating a join on your larger number field to qc results.

    When I was working on converting the Replication mdb tables to a non-replication status, I created new tables with a new autonumber. It wasn't that problematic as I also kept the large autonumbers - just put them into another "real" (SQL Server) or number (double) type (MSAccess) field and recreated the relationships against the new autonumber fields with a few update queries. Re-Indexing played a role in helping the translation and fixing some of the problems. I wonder if re-indexing some of your fields might help with some of your issues.

    Personally, what I might do, if you must keep those large numbers, but don't make them relationally based but use a simple autonumber field instead. You can keep the large number fields but only use it as a secondary relational field (not a true relationship). Let the autonumber field do it's simple increment and base your relationships on a more simplified smaller autonumber generated by MSAccess. There's nothing worse than an database with orphaned records and you may want to do some in-depth checking to see if you already have orphaned records.

    Make your new tables (keeping the large number as a non-autonumber field) and create a new autonumber field. Once this is done, you can re-create your relationships using the large number fields to link on in queries but use the smaller autonumber field to actually populate the relational values in your relational tables and then set the relationship against the new smaller autonumber field. You'll then be able to utilize your large number fields to do what you need to do but you also have a more reliable linking autonumber field. As healdem stated, keep the true autonumber field meaningless other than to link the tables and use your large manually created number field to do what you need it to do. There are no rules which state that you must always join on an autonumber type field in all your queries.
    Last edited by pkstormy; 06-28-08 at 13:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Personally, from the sound of the application, this issue isn't likely to happen again -- I've never had anything like this happen to me so the chances of this issue occurring again for the same small church is astronomically remote. I wouldn't bother. Just make sure the database is compacted and repaired monthly.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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