Solved

Autonumber jumps in Access 97

Posted on 2004-10-13
8
233 Views
Last Modified: 2010-07-27
Im writing some code in excel to append data from a spreadsheet into an acces 97 database.
All is going well except the autonumbering field is jumping quite erratically for no apparent reason. This is quite a big problem and I need to stop it happening.

Here is the part of code that adds the data:

 For i = 1 To UBound(RowList)
JobsRS.AddNew

    If dataArray(i, 1) <> "" Then JobsRS(1) = dataArray(i, 1)
    If dataArray(i, 2) <> "" Then JobsRS(3) = dataArray(i, 2)
    If dataArray(i, 3) <> "" Then JobsRS(4) = dataArray(i, 3)
    If dataArray(i, 4) <> "" Then JobsRS(5) = dataArray(i, 4)
    If dataArray(i, 5) <> "" Then JobsRS(7) = dataArray(i, 5)
    If dataArray(i, 6) <> "" Then JobsRS(8) = dataArray(i, 6)
    If dataArray(i, 7) <> "" Then JobsRS(11) = dataArray(i, 7)
    If dataArray(i, 8) <> "" Then JobsRS(24) = dataArray(i, 8)
    If dataArray(i, 9) <> "" Then JobsRS(42) = dataArray(i, 9)
    If dataArray(i, 10) <> "" Then JobsRS(14) = dataArray(i, 10)
    If dataArray(i, 11) <> "" Then JobsRS(15) = dataArray(i, 11)

JobsRS.Update
Next i

any Ideas?
0
Comment
Question by:cwhitby
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12299410
Your auto-number field in Access is set to Increment and not Random, right?
0
 

Author Comment

by:cwhitby
ID: 12299424
Set to Increment.

It doesnt happen every time, just occasionaly.
0
 
LVL 12

Assisted Solution

by:pique_tech
pique_tech earned 100 total points
ID: 12299475
I don't mean to be pedantic, but I have a history of getting all tripped up over the silliest little things...

Is anyone else doing anything with the same table at the same time that could interfere with the autonumbering?

More fundamentally, what is the importance of the jumps?  I understand wanting to understand the behavior, but it's generally not good to base too much business or process decision making on autonumber fields.  It's better to use record counts or other more controllable things if at all possible.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 100 total points
ID: 12299511
"to base too much business or process decision making on autonumber fields"

i'd agree 100% with that ..all my order/invoice/customer id's are based on formulae
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 300 total points
ID: 12299565
The only thing really consistent about auto-numbering is that it can behave erratically for very little reason.  :)

When you say the autonumber field is jumping, by how much is it jumping?  If it is missing 1 or 2 numbers, your most likely cause is someone/thing adding a record, then canceling the add.  This causes Access to 'reserve'  the auto-number for the newly added record.  Once the record is canceled, that auto-number is retired, and Access will not come back to it unless you reset the seed on the table.  Note that this does have to be because of other users...you could be doing this to yourself as you debug code.  In your example, if you stop the code anywhere between the AddNew and the Update, you will 'lose' your auto-number.  If you can run it straight through twice and get contiguous auto-numbers, this is likely your problem.  This is a 'feature' of Access, meaning Microsoft claims this behavior to be by design.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12299573
Note that this does have to be because of other users...
                   ^ NOT

sigh...still waking up.
0
 

Author Comment

by:cwhitby
ID: 12299733
No one else is adding to the db, its a copy of the main one I have set up for testing purposes. As regards using autonumbering for business practice, I agree, however this is a system thats been running for a number of years, using the autonumer for tracking purposes - that’s why its critical that I stop the big jumps (sometimes over 1000).

The comment about debug mode may have some merit, I have hit a few problems with data types and validation etc. I will investigate this theory further
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now