Start Autonumber Id to begin at 1706! 2

[696796]

Good afternoon,

I don't know if this can be done ( i sure hope it can!).

The scenario:-

i have a table 'tblIssue'
The primary key is 'IssueId'

this is set to an autonumber, which is fine. I've now just found out that my users need the number to start at 1750. (I said thanks for telling me this at the start!)

What are my options guys and gals???

Thanks for your time and effort, much apreciated,

Alex

 

[lupins46]

Create a copy of the table. In the copy, change the autonumber to number. Add 1 record with number 1749.
Use an append query to append this record to the real table. Delete the record from the real table.
DON'T compact the database until you've added a new record.

 

[696796]

Thanks for the speedy reply!

Sorry to be a bother, but how is an append query done?

Ive got the copy of the table, ready to switch my record (1749) over to the original, but how do i do that?

ps-the new tables called tblappend

 

[696796]

Thanks for the speedy reply!

Sorry to be a bother, but how is an append query done?

Ive got the copy of the table, ready to switch my record (1749) over to the original, but how do i do that?

 

[lupins46]

I assume you have entered a new record with key 1749 in the copy.
Start a new query based on the copy and include all the fields. Click the Query menu and select Append Query.
All the columns should now show the same field name in the top row and the (new) append-to row.

Run the query using the Run button (!).

You should get messages about adding 1 record.

When you've done, look at the main table.
You can now delete the record that's appended. The next new record will be 1750.

 

[696796]

Thanks very much for that, worked just as you said. Got me out of a hot-spot!

 

[lespaul]

now realize that if you are assigning some importance or meaning to an autonumber, you are probably using it incorrectly. Also realize that if records are deleted from this table you will NO LONGER have a sequential order to your autonumber, there will be missing numbers. Also if a record is created and then abandoned, you will also have missing numbers.

If there is a meaning to your use of autonumber, you should rethink using it in this way.

Leslie

 

[JeremyNYC]

Leslie is spot on. For a VERY short write-up, with an illustration of why, check out the Autonumber entry in the Developers' section at

http://www.abcdataworks.com

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[lespaul]

Jeremy! That looks like a title in your signature!!! Got a job??!!!

I still send lots of people to the Fundamentals site, one of the best references around, thanks for keeping your site up!

Les

 

[JeremyNYC]

Leslie,

Yeah, that article is great, and it was really nice of Paul Litwin (the author) to give me permission to keep it up there.

And yes, that is a title, and I did take a job. It's a great non-profit doing great database work. It was exciting enough for me to take a big pay cut and give up all outside consulting work!

The ABCD site will stay up indefinitely--it doesn't cost all that much, and who ever knows what will happen in the future--though I'm not sure that I'll do much updating there. We'll see, though...

Be well.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[KerryL]

lupins46 said:
Create a copy of the table. In the copy, change the autonumber to number. Add 1 record with number 1749.
Use an append query to append this record to the real table. Delete the record from the real table.
DON'T compact the database until you've added a new record.
/end


I tried this but it didn't work. I appended a duplicated table (without the autonumber) onto my original table, which put a "1" in the original table's autonumber key field. But when I then added new records, the autonumber sequence used 7459, the next number from the previously deleted records.

I've tried it 3-4 times but the same thing keeps happening. The autonumber isn't being reset by the appended record. Any ideas?

 

[RoyVidar]

Then reset the autonumber field using something like this in the immediate pane (ctrl+g):

[tt]currentdb.execute "ALTER TABLE TableName ALTER COLUMN AutoNumberFieldName COUNTER(N,1)"[/tt]

Change the table and field names to match your names, replace "N" with your "next number".

Roy-Vidar

 

[KerryL]

Wow, thanks Roy. That worked like a charm!

 

[lupins46]

You didn't say that you had posted records to the table previously. It sounded to me like you were just creating it for the first time.

 

[KerryL]

Roy,

I had to reset the autonumber field again and tried this command in the immediate pane:

currentdb.Execute "ALTER TABLE services ALTER COLUMN ser_id COUNTER(N,30)"

Table = services
ser_id = column I want to change
30 = next number to be used


But I keep getting this error:

Run-time error 3292
Syntax error in field definition


Not sure what I'm doing wrong...

 

[RoyVidar]

You did use numbers and not "N", did you?

The first number is which number the autonumber should start from, the next number is the increment, so by using for instance

[tt]currentdb.Execute "ALTER TABLE services ALTER COLUMN ser_id COUNTER(1,30)"[/tt]

It will start on number 1, and increment by 30 for each new number. Reversing the numbers

[tt]...counter (30,1)"[/tt]

will make it start at 30 and increment by 1.

Roy-Vidar

 

[KerryL]

Duh. I apologize for that obvious blunder. Sheesh.

I tried this line today:

currentdb.Execute "ALTER TABLE services ALTER COLUMN ser_id COUNTER(30,1)"


...and received this error message:

Runtime Error 3720
Cannot change field "ser_id". It is part of one or more relationships.



Do I have to delete the link to another table before it will run properly?

 

[RoyVidar]

Yes, I think so. BUT - rereading this thread causes me to ask you to review the comments by lespaul and JeremyNYC above!

My opinion[ul][li]Autonumbers, when used as primary keys, should not be exposed to the users (or the other way around)[/li]
[li]the numbers are (or should be) meaningless - they shall not have any business meaning[/li]
[li]the autonumber should only be used to maintain rreferential integrity[/li][/ul]

In light of that, I think that resetting autonumbers is more or less meaningless, only it seems like a nice feature when delivering a new system, to let them all start at one. I still think that, even if no one but myself, or someone doing maintenance will actually ever see the numbers.

You seem to be assigning business meaning to the numbers - this will cause headaches, much better would be using for instance the approach from MichaelRed's faq faq700-184.

Roy-Vidar

 

[KerryL]

So if I remove the link, then run the execute command, then relink the table, I should be fine?


BTW, I agree 100% with your comments about the key field autonumbers being important only for referential integrity. I typically don't even look at them.

Though it may look like I'm using them for a business reason here, I'm really not. Well, actually, I'm trying to reset the numbering sequence back to normal. Let me explain.

When I first started working on this DB I thought the services ID (ser_id) numbering scheme would have to be unique because there are multiple copies of this DB in use (at different sites) and the data is consolidated once a month for reporting. I wanted the key field in the services table to be unique for each site so that when all the services tables are consolidated there wouldn't be any identical numbers in the key field.

So one site's ser_id key field started with 360001 and another started with 380001, etc. Later I determined that the site data can be differentiated by another field, so I'm simply trying to reset the autonumbering back to normal.

Does that make sense?