Solved

Autonumber jumps in Access 97

Posted on 2004-10-13
8
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

732 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