700,000 + Autonumber (Access 97)

Topic

New Reply

Is there a way to AutoNumber + 700,000? Please let me know how I would be able to do that if it is possible.

Reply | Quote

Replies

What do you want to do?

Do you want to add 700,000 to an AutoNumber field in a query? In that case, use an expression like [Fieldname]+700000 where Fieldname must be replaced by the actual name of your field.

Or do you want to add 700,000 to the value of an AutoNumber field in the table itself? You can’t do that with an AutoNumber field.

Or do you want to start your AutoNumbers at 700,000? It’s possible, but if you want this, perhaps an AutoNumber is not the right kind of field.

Reply | Quote

To what purpose? Autonumbers are not intended to be data, merely unique identifiers. If you start trying to force them to be something else, you’re better off using something else in the first place.

Reply | Quote

OK guys, I have one for you then. Say I have an autonumber field. I was playing around with some code, ran it and the thing ran wild adding records. There was a problem with my do, loop statement. Anyhow, I have fixed that but now I want the numbers to start back at around 300 where they originally were. How do I make it say 300 instead of 461000……..

Thanks,
Mark

Reply | Quote

If you have deleted the offending records then all you need do is compact the db.

Reply | Quote

I think that works for Access 2000/2002 but it seems to me it doesn’t always work for Access 97 – unfortunately I don’t have it installed anywhere anymore so I can’t test it.

Reply | Quote

I seem to be in a contradictory mood today. I still use Access 97, and after compacting a database, autonumbering always resumes at (highest existing value)+1. (Gaps between existing values are not re-used, which is OK.)

Reply | Quote

Thanks Everyone,
I am running AXP. I deleted the offending records and compacted the DB but I still get a number that is too high.

Thanks,
Mark

Reply | Quote

This propbably means that there is some record that has a very high number.

Anyway, Charlotte is right. Autonumber is not designed for human consumption. It is strictly for Access’s use only to uniquely identify (address) a record. Don’t try to use them for anything else, you’ll be very dissapointed. What difference does it make what the autonumber is?

Reply | Quote

I feel like nitpicking ! I agree that if you care what values the autonumbers are then you are misusing the feature; however they can be useful for human consumption as long as it’s made painfully clear that they’re simply unique identifiers, no more, no less. Displaying and allowing the user to refer to an autonumber can be a convenient way to let a user quickly go to a particular record (e.g. to let him/her quickly copy corrections from a paper copy of a record to the database record).

Kind of like null numeric values (which some people don’t seem to approve of ) – they can be very useful but you need to be very careful with them.

Reply | Quote

I usually let them select from something that is meaningful, like a name or customer code, etc., but I don’t make that the primary key. The autonumber is tucked away out of sight and only Access knows what it is.

Reply | Quote

I guess my point is there may be no single thing (other than the autonumber) that uniquely describes the data you want, so it’s a choice between specifying enough details to get to the record you want or specifying a single “magic number” that you know because it’s already on the printed output you want to correct. If you have a lot of corrections or updates to do, “magic numbers” speed things up.

Reply | Quote

Delete the realtionships with the existing table
copy the table under a new name, tblTemp.
Delete all the records in tblTemp.
Compact the database.
Do an append query selecting all the fields except the ID field from the original table and appending to the temp table.
Delete the original table.
Rename tblTemp to the name the original table had.
Reconstruct the relationships with the new table.

Don’t do this until Wendell or Charlotte have confirmed that it will not destroy other things.

Reply | Quote

DId I read that right? You’re copying the table to tblTemp, deleting all the records in tblTemp and compacting, then deleting the original table? When were you planning to salvage any usable records from the original table?

Reply | Quote

Oops. I have edited the post to say what I meant. Glad I thought an expert should check it. Thanks

Reply | Quote

OK, now I’ll pick on you again. If you don’t append the ID field from the original table, how were you planning to reestablish the relationships? You have to keep the keys that were used to relate the records in the first place or you won’t be able to relate them after you create a whole new set of keys in that one table.

Reply | Quote

OK I give up.

It looks like it is a case of going through the table manually and making sure any records with too-high IDs are deleted. (Having written down the details and deleted all their dependent records) and then compacting and re-entering the records that were deleted.

Reply | Quote

Depending on how many records might be causing the problem, that could be a tall order!

The workaround would be to convert the autonumber field to a long in the original table. Then copy the structure only to a temp table and add a new autonumber to that table while the temp table is empty. Compact the database and then append the records from the original table into the temp table. Append the old autonumber field values (now Longs) to the long field in the temp table and let Access create new autonumbers. That gives you a method for linking back to the original foreign keys in the other tables. At that point, you have to decide how to update the FKs in the other tables, but the safest way it to add a new column to each and update it with the NEW FKs from the temp table. Then you can delete the original table, create new relationships on the new FK fields and the autonumber in the new table and then remove the old FK field from the other tables. Of course, it’s a lot of work, either way.

Reply | Quote

It was 2000 that didn’t always work for. There was a bug corrected in SR-1. You could also run into wierdness in 97 if you were running NT and didn’t have at least SP4.

Reply | Quote