Yes, use the Alter statement. However you should not be concerened what that number is other than it links records correctly.It’s been a while since I've used Access (360), so I'm very rusty. Is it possible to have the autoonumber primary key start from 1000?
If so, how?
Cheers,
Jake
Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub
The append query is the method I know will work, but I forgot how to create the append query without using the query item from the menu. If I use a starting number of 1000, the query comes up with no records.Once you have data in a table, you cannot change a field from long integer to autonumber.
The method is to use an append query. The append query adds a record with 999 as the value for the autonumber field. You will need to include values for any column that is required. Once the row has been added, that resets the autonumber seed so that the next row added will be 999 +1 or 1000. Once that record has been added, you can delete the dummy 999 record.
The ONLY way you can add rows to a table and include an autonumber value is by using an append query. You cannot update an existing record or add any record using any method other than an append query. If you couldn't add old PK values using an append query, conversions and restores would be a nightmare.
Keep in mind that the singular purpose of an autonumber is to provide a unique ID for a given row. If you are going to display the autonumber and use it as an "invoice" number for example, most people don't want it to start at 1. No one wants to send a customer invoice # 1. It looks better if the invoice is # 1000. If this is your situation, I would probably make it start at a larger number and not an even one. So I would insert 610023, for example as the start. Then the first invoice will be 610024 which no customer would regard as strange.
I'm attaching a link to a sample that shows how to generate custom sequence numbers if you want something more exotic.
Custom Sequence Numbers
Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See belowwww.access-programmers.co.uk
So try the Alter method without the Delete command ?The append query is the method I know will work, but I forgot how to create the append query without using the query item from the menu. If I use a starting number of 1000, the query comes up with no records.
Fyii, I am trying to modify a table I made a few years ago.
Jake
INSERT INTO YourTable fldPK, fldDT VALUES (1000, #05/28/2023#)
Do you mean that obtaining a next number from a [nextnumber] field can leave gaps. It depends on the timing. I only do the read when the new record is completed and accepted, immediately before the final update. If you want to be completely secure you can lock the nextnumber table first before obtaining the new number.Dave, this is exactly the method used by the autonumber and exactly the reason why gaps can exist. In a multi-user environment, multiple people can be adding rows at the same time. If userA gets a new sequence too early in a process, there will be time for userB to obtain the next available number. Then if userA's append fails, the generated number never gets used, leaving a gap. Access generates the autonumber as soon as the record is dirtied so it is far more susceptible to causing gaps than a custom solution which doesn't acquire the new sequence number until immediately prior to exiting the Form's beforeUpdate event. This leaves only a miniscule time for another user to sneak in. BUT, if there is some error and userA's record does not actually get saved, you can still end up with a gap.
There are ways around this by using Transaction processing and unbound forms but not for the faint of heart. Using an unbound form lets you use a transaction. The transaction locks BOTH tables and only releases the lock when the transaction completes successfully or fails and the updates for both tables are backed out. Locking multiple tables has the potential to result in a deadly embrace as it is called. For example, if the process used by userA locks tbl2 and then tbl1 but the process being used by userB locks tbl1 and then tbl2, that results in a conflict since neither can succeed. Therefore, when you are using transactions and multiple tables, it is imperative that table locks are always acquired by all processes in the same order. In most cases, it doesn't matter which lock is acquired first so just do them in alphabetical order.
In applications where gaps are problematic, it is not the missing records so much as an explanation for why a sequence number is missing. Checks are a primary issue. You simply can't have a missing check number but we always have the possibility of voiding a check. So, the sequence number table in its simple form works fine for this. You generate the number and save it. If the save for the check table fails, you update the sequence number table with a note that check 23445 was cancelled because "x". This does not require unbound forms. It simply accounts for why the gap exists.
It is the large gaps caused by append queries that get cancelled that are most problematic for autonumbers. Those don't happen when using the two table method because the checks would be generated using VBA rather than an append query.
I use the solution you explained in #11 and obtain the next number immediately when the new record is dirty.I didn't mean you should obtain the next number immediately upon dirtying the new record.
I know you are a skilled developer, but if the number that was displayed might be changed, I can hardly see the point in displaying the number at all until you get to the point that it won't be changed.I use the solution you explained in #11 and obtain the next number immediately when the new record is dirty.
I want the user see the number.
But in before update of the form, I check to see if the number is still available and not used by another user.
If it's still available, then the record is saved.
If not, the number is updated to the next available number, record get saved and the user receives a messagebox that the number was updated to xxxx.
It's very nice of you to think so, but honestly I'm not even close to being a skilled developer,I know you are a skilled developer,
You're absolutely correct. But it's really very rare that it change. We don't have enough designers to work on different projects for the same customer at the same time. In fact I've never seen it's been changed in before update. It's only a safety check.I can hardly see the point in displaying the number at all until you get to the point that it won't be changed.
That's what I do, Pat. The user clicks a button saying Accept order, or whatever, the order number gets read, and the table incremented. The only issue might be an unexpected run time error preventing the record saving.Unless you have an extremely busy system, it is unlikely that the number will change. @KitaYama is generating the number immediately but not saving it and so that requires checking just before saving to ensure that the number hasn't been used. This method should also be coded to handle a database error in case the timing is such that no one slipped in between the check and the final save.
The autonumber is generated and saved immediately when the record is dirtied. This means that no one can sneak in and use that number. It also leads to gaps when the record that generated the number isn't saved.
Using your own table is just like the autonumber. You lock the number immediately so no one can use it But, the method allows for gaps. Not the huge gaps you get with autonumbers but gaps nevertheless because you have to commit the ID to the ID table prior to committing the record that will use it. There are times when the second record might not get saved. That is what would cause the gap. If I were to use this method, I would do a lot of validation in the form's BeforeUpdate event to minimize the chance that the database engine will throw an error and not sve the record, AND I would generate the ID and save it as the last step in the BeforeUpdate event to minimize the possibility of a gap.
I'm not saying what method is best. All three have their issues so you have to decide what you prefer. The first method won't leave gaps but rather than checking before the save, I would capture the error and loop back to the save after generating another ID.