Solved

Autonumber jumps in Access 97

Posted on 2004-10-13
8
238 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

821 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