Autonumber jumps in Access 97

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?
cwhitbyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Steve BinkConnect With a Mentor Commented:
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
 
pique_techCommented:
Your auto-number field in Access is set to Increment and not Random, right?
0
 
cwhitbyAuthor Commented:
Set to Increment.

It doesnt happen every time, just occasionaly.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
pique_techConnect With a Mentor Commented:
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
 
SidFishesConnect With a Mentor Commented:
"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
 
Steve BinkCommented:
Note that this does have to be because of other users...
                   ^ NOT

sigh...still waking up.
0
 
cwhitbyAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.