Autonum change in linked table (Access97)

Topic

New Reply

I have been trying to increase the autonum number (jump forward) and used the instructions provided in the help. Though, whenever I complete the tasks of deleting the record and re-setting the validation rules/relationships etc., the starting autonum is not where I want it to be (1 after the appended number). What is the correct way to do this? I have tried to un-split, run the append query, and then split again, but to no avail

Reply | Quote

Replies

I don’t know why you are changing validation rules and relationships. All you need to do is run the append query. New autonumbers will then pick-up after the number. New autonumbers will then pick-up after that new number. Don’t delete the dummy record created by the append and don’t compact the database until you have entered some new valid records.

Reply | Quote

I should have mentioned that the database is already in use – past dev. If the validation rules are left the way they are, an append will fail as some fields are set to ‘required’. Perhaps the relationships and removing the PK are not necessary(?)

Reply | Quote

Bad choice to use the autonumber as a key field or as a join field as the autonumber can reset on compact, restore etc… You will have great difficulties continuing with autonumber as is.

If you want to continue as you are then the only thing I can think of is to put a dummy record in with the autonumber set to one before the number you want then delete the dummy record. The autonumbering may then continue from the number you want.

My choice would be to recreate the table with the current autonumber set as just a number field, append your data into it, and put the code into the database to create the number sequencing for you.

It is bad practice to create a Primary Key on an autonumber field, it nearly always ends in problems when you need to restore data especially when rows have been deleted as it will re-number your records.

Reply | Quote

>>It is bad practice to create a Primary Key on an autonumber field, it nearly always ends in problems when you need to restore data especially when rows have been deleted as it will re-number your records.<<

Do you mean it will renumber existing records? I have never seen that before. An autonumber is generally a perfect choice for a PrimaryKey since it can not be changed.

Reply | Quote

Mar,

I did not see any response to your question regarding the use of Autonumber fields as primary keys, and I am MOST curious about it. I agree with your comment that Autonumbered fields are a perfect choice for a Primary Key. If not, then I’m going to have a horrendous mess some time in the not too distant future!

Have you heard anything more about this? It is my understanding that autonumber will not renumber the records when some are deleted or compressed or repaired. I sure hope that is the case.

Reply | Quote

>>then I’m going to have a horrendous mess some time in the not too distant future!<<

Chuck,

You and me both! I haven't heard any more about this, but I figured it might be because of the weekend. However, since I've never heard that comment anywhere before, I'm not too worried. I'm guessing we misinterpreted the statement, and it perhaps had to do with adding an autonumber field to a record.

Reply | Quote

I agree that an autonumber field is a great choice for a primary key. However, I’ve encountered “challenges” with using autoumber primary key fields in the following situation: When I archive records from my “active” table to an “archive” table, I’d like the primary key (the autonumber field) to remain unique across all records in both tables (since it provides the relationship to various other tables). However, once a particular autonumber value is gone from the “active” table, a new record added may be given this value, thus duplicating a primary key in the “archive” table. To avoid this, (based on advice from Charlotte here in Woody’s Lounge), I set up a “master” list of if IDs that are autonumbered. They then relate to an ID field in both the archive and active tables which are not autonumbered. This adds some overhead when adding and deleting records (adding a record to the active table means adding a new record to the master list (with a new and unique-across-both-tables autonumber primary key ID), then actually adding the new record in the active table and copying the master list autonumber value into the field that’s related to the master list ID). Deleting records can be taken care of through referential integrity. It seems like there ought to be a less cumbersome way of doing this — is there???

Regardless, even with this sort of arrangement, I’ve never seen autonumber fields getting re-numbered. Boy, would that be a mess!

Tom

Reply | Quote

Well, you could set-up your autonumber to create a random number rather than a sequential one. The odds that you would re-use an autonumber is very small. This is what is used in replicated databases.

If you never archive the most recent active record (that is, with the highest autonumber) then even a sequential autonumber should not duplicate. Even after a compact, the next autonumber assigned will be 1 more than the current highest number. You could aways do a DLookup to see if it was in archive table.

Reply | Quote

I, too, thought the autonumber-increment method would use the next higher number. But I found duplicates appearing (as described in my previous post) — the newer records were being assigned “old” (archived) numbers even though there were higher active autonumbers — the autonumbering scheme seemed to be “filling holes”. I’ll have to admit that I wasn’t too diligent about repairing/compacting after archiving (removing records from the active table) — that may be part of the problem. Also, and this may be circumstantial, I noticed this problem only after upgrading from Access 97 to 2000.

Autonumber-random is a good idea. With the odds of duplicating an existing record at around 1 in 4 billion, I guess it’s pretty safe (unless you’ve got a fairly big database!). If I were to worry about such odds, I should be buying a lot more Lotto tickets!

Reply | Quote

What you’re describing has always existed with autonumbers, and it’s really more an issue of the way you’re archiving than a problem with autonumbers.

If you delete records (either after archiving or for any other reason), a compact will reset the next sequential autonumber to the next number after the highest autonumber left in the table. If you delete *all* the records, the next highest number is 1, and you would have to keep a “seed” record in the table to avoid stepping on archived numbers. However, a simpler remedy is to have an additional field that will work with the autonumber to provide a unique key, even if the autonumbers are the same.

Reply | Quote

I believe the situation you are describing was fixed with the SR-1 release of Access2000.

Reply | Quote

SR-1 fixed a problem that occurred when you appended records with existing keys to a table with an autonumber field. The next time you tried to add a record the normal way, it would attempt to use the next largest number based on what was in the table before you appended records.

Access has always reset the autonumber on compact so that the next value will be one higher than the highest existing autonumber in the table. Before SR-1, that was broken in Access 2000, but only when you had appended records with autonumber keys to the table. If you added records the usual way, the autonumber worked as expected.

Reply | Quote

Thanks for the clarification. I also understood there was a problem with duplicate autonumbers being assigned? Perhaps it had to do with assigning an autonumber that then duplicated one of the appended records. But I never could figure out how this could happen if the autonumber was primary key?

Reply | Quote

It couldn’t. You only got the *attempt* to create a new record with a duplicate autonumber. Then you would get a duplicate key or index error. The only way you could get actual duplicates was if you didn’t have the autonumber field indexed unique or as the primary key.

Reply | Quote

That’s why I couldn’t understand why people were reporting getting duplicates. It’s like they had an autonumber field but didn’t make it a unique key. Oh well, I suppose they had their reasons!

Reply | Quote

Sorry, but I disagree with your advice on autonumbers. I’ve been using them without problems since Access 1.0. They are *not* suitable if you want an uninterrupted sequence of numbers, but requiring that suggests that the key will have meaning, and that violates the whole idea of the autonumber.

Reply | Quote

Yes, the append will fail if certain required fields don’t have data in them. So you can either remove the Required flag (as you did), or just make sure you are supplying valid data in your append. As for relationships, I forgot that Access balks at changing to an autonumber unless you delete relationships, even if you are changing from a long interger. Of course, you only need to change the relationships that include this field. It is not really necessary to delete the PK, since the same field will remain the PK; but this trivial.

I was assuming you were not going to renumber the existing records; rather you just wanted to start numbering new records from the number specified in your append query.

Reply | Quote

Thanks. Would it make a difference if I made a query in the front end (database is split) or did it to the _be.mdb(back-end)?

Reply | Quote

It makes no difference where you run the query from. You could do the whole thing from the frontend, if you were comfortable opening the backend database in code and deleting relationships, etc. I’m not! So, I would do it all from the backend.

Reply | Quote