473,324 Members | 2,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Auto Number

Hello-
I have a database that uses an auto number field type that goes out of
sync periodically. My customer gets a "cannot add record, number already in
use" error message. I dump the records into a new table starting at auto
number 1, and sequentially up to about 20,000. Everything is fine for a
month or two, then the auto number field goes berserk again. Do you have
any ideas what the client may be doing to cause this problem, or is it
something in Access 2000? How many records can you add to an Access 2000
table before needing a better database?
Thanks again for your input.
Mike from Moriches
Nov 13 '05 #1
2 2953
Your client needs to install the free update for JET 4 (Service Patch 8)
from:
http://support.microsoft.com/gp/sp

There was a problem with the msjet40.dll distributed with the original
Access 2000. The problem was fixed long ago, but if your client has no
patches, it could still be the cause of the problem.

Installing the patch fixes the problem for the future, but you may also need
to run some code to fix the database that already has the problem. The code
in this link will let you do that:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike N." <Mi******@optonline.net> wrote in message
news:%1******************@fe08.lga...
Hello-
I have a database that uses an auto number field type that goes out of
sync periodically. My customer gets a "cannot add record, number already
in use" error message. I dump the records into a new table starting at
auto number 1, and sequentially up to about 20,000. Everything is fine
for a month or two, then the auto number field goes berserk again. Do you
have any ideas what the client may be doing to cause this problem, or is
it something in Access 2000? How many records can you add to an Access
2000 table before needing a better database?
Thanks again for your input.
Mike from Moriches

Nov 13 '05 #2
Hello Mike,
I found a similar problem and made a procedure to increment the primary key
each time a record is entered by 1.
This is attached to the data entry form.
The field refno is my primary key and needs to be incremented by 1 each time
a new record is entered. This reference number is then issued to the staff
member. It is very important that each number is sequential and independant
of the record number.

Since I have used this procedure I have had no further problems.
I hope this helps.

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert

'this procedure increments the refno field
'autoincrement not used so that we do not rely on record numbers
Me.refno = Nz(DMax("refno", "tblDailyLog")) + 1

Exit_Form_BeforeInsert:
Exit Sub

Err_Form_BeforeInsert:
MsgBox Err.Description
Resume Exit_Form_BeforeInsert

End Sub
"Mike N." <Mi******@optonline.net> wrote in message
news:%1******************@fe08.lga...
Hello-
I have a database that uses an auto number field type that goes out of
sync periodically. My customer gets a "cannot add record, number already
in use" error message. I dump the records into a new table starting at
auto number 1, and sequentially up to about 20,000. Everything is fine
for a month or two, then the auto number field goes berserk again. Do you
have any ideas what the client may be doing to cause this problem, or is
it something in Access 2000? How many records can you add to an Access
2000 table before needing a better database?
Thanks again for your input.
Mike from Moriches

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ken | last post by:
Need help on the Auto Number or Identity Seed on the Oracle Database I got an Access database that need to be converted to Oracle 9i. Somehow the Trigger we created to simulate the "AUTO NUMBER"...
2
by: Irwinsp | last post by:
Hi All, I have a form with an auto number field displayed. The field looks great except when the user is entering a new record. The field then has the text "auto number" in it. Is there a...
2
by: Tom | last post by:
I am trying to store information into a table that has an auto increment field. There is currently no data in the table. Using the code below I cannot insert data into the table. I get an error...
5
by: Geoff Cayzer | last post by:
At http://www.blueclaw-db.com/tips_tricks.htm I came across a section which is included below and was hoping for some comment on the article. -------------- Almost never use this auto-number...
16
by: John Baker | last post by:
Hi: I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number...
4
by: Shahar | last post by:
Hi I need to get a field name 'ID'(that is an auto-number field) right after I add a new row to table, it's work like that: myCommand.ExecuteNonQuery(); myCommand.CommandText = "SELECT...
13
by: S.Dickson | last post by:
I had an access database that i use as an ordering system. I have a form for entering customer details. When i add a new customer on the form the customer number is an auto number that appears when...
2
by: bubblegirl | last post by:
Hi, I need help in getting this database to work well (user-friendly) by generating auto client ID. The ID is NOT the auto number that Access automatically create when there is no primary key. ...
5
by: David Wright | last post by:
Hello Everyone I would be grateful if someone could help me with the automatic increment of a field on my subform called ‘Test_SrNo’. I am Using Microsoft Office 2000. The auto entry of the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.