473,326 Members | 2,192 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,326 software developers and data experts.

Autonumber Duplicates Problem

Hi:

I have a table which is indexed on an auto number (supposedly unique for each record). For
reasons I cannot explain, I have started to encounter a problem with duplicate auto
numbers. The numbers went up to 250, and the suddenly the system started to try and
generate numbers starting at 150. Because the auto numbers duplicated, the system now will
accept NO new records!!

Can someone tell me how to fix this, either by giving a new starting base (say 1000) or
some other technique. Also, if a nee starting base HOW do i set it?

Thanks..Im stuck!

JOhn Baker
Nov 12 '05 #1
11 2306
Hi John

This is a known problem with the autonumbers in Access 2000 and later. There
are 2 steps to fixing it.

1. Run the code in this link:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
This reset the Seed of the Autonumbers, which fixes the immediate problem.

2. Download the latest service pack for your version of Office from
support.microsoft.com. This prevents the problems recurring.

--
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.

"John Baker" <Ba******@Verizon.net> wrote in message
news:s4********************************@4ax.com...

I have a table which is indexed on an auto number (supposedly unique for each record). For reasons I cannot explain, I have started to encounter a problem with duplicate auto numbers. The numbers went up to 250, and the suddenly the system started to try and generate numbers starting at 150. Because the auto numbers duplicated, the system now will accept NO new records!!

Can someone tell me how to fix this, either by giving a new starting base (say 1000) or some other technique. Also, if a nee starting base HOW do i set it?

Thanks..Im stuck!

JOhn Baker

Nov 12 '05 #2
Thank you.

I have updated Access and reset the data base with an earlier version. Things appear to be
OK.

I am going to distribute this application to others in the not too distant future. Is it
likely that they will have the same problems (they have 2002 version with XP, I have 2000
with W98), and should I make the effort to update their systems with the patch too?"

Thanks for your help
John Baker

Allen Browne" <Al*********@SeeSig.Invalid> wrote:
Hi John

This is a known problem with the autonumbers in Access 2000 and later. There
are 2 steps to fixing it.

1. Run the code in this link:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
This reset the Seed of the Autonumbers, which fixes the immediate problem.

2. Download the latest service pack for your version of Office from
support.microsoft.com. This prevents the problems recurring.


Nov 12 '05 #3
It would be a very good idea to insist that they have the latest service
pack for their version of Access.

On the Help | About screen in my open Access applications, I always display
the version of Access and of Jet. Then if the user is experiencing a
problem, they can tell me what versions they are using. The two text boxes
have this Control Source:
=fGetProductVersion(SysCmd(9) & "msaccess.exe")
=fGetProductVersion(fReturnSysDir() & "\msjet40.dll")
and the API-call functions can be copied from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm

If by "districute this application" you are considering generating a runtime
installation, there are additional issues with the different versions of
Windows.

--
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.

"John Baker" <Ba******@Verizon.net> wrote in message
news:ql********************************@4ax.com...
Thank you.

I have updated Access and reset the data base with an earlier version. Things appear to be OK.

I am going to distribute this application to others in the not too distant future. Is it likely that they will have the same problems (they have 2002 version with XP, I have 2000 with W98), and should I make the effort to update their systems with the patch too?"
Thanks for your help
John Baker

Allen Browne" <Al*********@SeeSig.Invalid> wrote:
Hi John

This is a known problem with the autonumbers in Access 2000 and later. Thereare 2 steps to fixing it.

1. Run the code in this link:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
This reset the Seed of the Autonumbers, which fixes the immediate problem.
2. Download the latest service pack for your version of Office from
support.microsoft.com. This prevents the problems recurring.

Nov 12 '05 #4
Allen:

I hate to tell you butb the problem has NOT been solved. Do you know som way I can reset
the seed for autonumber? Its still trying to issue autonumbers that duplicate the ones
already in the table!!

Im perplexed because it appeared to work yesterday.

Regards

John
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
It would be a very good idea to insist that they have the latest service
pack for their version of Access.

On the Help | About screen in my open Access applications, I always display
the version of Access and of Jet. Then if the user is experiencing a
problem, they can tell me what versions they are using. The two text boxes
have this Control Source:
=fGetProductVersion(SysCmd(9) & "msaccess.exe")
=fGetProductVersion(fReturnSysDir() & "\msjet40.dll")
and the API-call functions can be copied from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm

If by "districute this application" you are considering generating a runtime
installation, there are additional issues with the different versions of
Windows.


Nov 12 '05 #5
The code in this link illustrates how to set Properties("Seed") of a Column
in a Table of the Catalog:
http://allenbrowne.com/ser-40.html

Running that code after installing the update should solve the problem.

--
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.

"John Baker" <Ba******@Verizon.net> wrote in message
news:31********************************@4ax.com...
Allen:

I hate to tell you but the problem has NOT been solved. Do you know som way I can reset the seed for autonumber? Its still trying to issue autonumbers that duplicate the ones already in the table!!

Im perplexed because it appeared to work yesterday.

Regards

John
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
It would be a very good idea to insist that they have the latest service
pack for their version of Access.

On the Help | About screen in my open Access applications, I always displaythe version of Access and of Jet. Then if the user is experiencing a
problem, they can tell me what versions they are using. The two text boxeshave this Control Source:
=fGetProductVersion(SysCmd(9) & "msaccess.exe")
=fGetProductVersion(fReturnSysDir() & "\msjet40.dll")
and the API-call functions can be copied from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm

If by "districute this application" you are considering generating a runtimeinstallation, there are additional issues with the different versions of
Windows.

Nov 12 '05 #6
Allen:

I ran into the following error at compile time:

Dim Rst As DAO.Recordset
It said it was a user defined varaiable, and not defined.

Any thoughts on this?
Regards

John Baker
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
The code in this link illustrates how to set Properties("Seed") of a Column
in a Table of the Catalog:
http://allenbrowne.com/ser-40.html

Running that code after installing the update should solve the problem.


Nov 12 '05 #7
You need a reference to the DAO library.

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.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.

"John Baker" <Ba******@Verizon.net> wrote in message
news:5q********************************@4ax.com...
Allen:

I ran into the following error at compile time:

Dim Rst As DAO.Recordset
It said it was a user defined varaiable, and not defined.

Any thoughts on this?
Regards

John Baker
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
The code in this link illustrates how to set Properties("Seed") of a Columnin a Table of the Catalog:
http://allenbrowne.com/ser-40.html

Running that code after installing the update should solve the problem.

Nov 12 '05 #8
Allen:

I am totally perplexed. I have made the updates to Access, and run your routine.
Everything works fine until I Close th application, and reboot the system. At that point
the duplicate problem returns. You have been most helpful, and I am totally, utterly,
frustrated and have no idea as to what is happening or what I can do!
Regards

John Baker

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
You need a reference to the DAO library.

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html


Nov 12 '05 #9
John, re-reading this thread, I think you have Access 2000.
You have installed the service pack, so Help | About should show SP3?

Just to verify that the correct version of JET is running, search your hard
disk for msjet40.dll. You will probably find it in \windows\system32, but
are there any other copies of this file on your drive? If there is another
copy in the Office folder or in Windows or somewhere on path, it is possible
that the wrong one is being loaded into memory.

After verifying that there is only one copy, right-click it in the Windows
Explorer and choose Properties. On the Version tab, what version do you see?
It should be 4.0.8015.0. If it is this version, then I'm at a loss to know
what to suggest further.

--
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.
"John Baker" <Ba******@Verizon.net> wrote in message
news:8g********************************@4ax.com...
Allen:

I am totally perplexed. I have made the updates to Access, and run your routine. Everything works fine until I Close th application, and reboot the system. At that point the duplicate problem returns. You have been most helpful, and I am totally, utterly, frustrated and have no idea as to what is happening or what I can do!
Regards

John Baker

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
You need a reference to the DAO library.

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

Nov 12 '05 #10
Allen:

I do have 2000, yes.

In fact there are two versions of jet on the system (all else is correct). The Correct
version is in C:Windows\system, however there is an earlier version in a folder called
C:\widows\vcm (whatever that is!). Should I remover the version from VCM and substitute
the correct one, or just remove the one from VCM and let sleeping dogs lay?

Thank you very much for helping on this issue, its very much appreciated.

Regards

JOhn

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
John, re-reading this thread, I think you have Access 2000.
You have installed the service pack, so Help | About should show SP3?

Just to verify that the correct version of JET is running, search your hard
disk for msjet40.dll. You will probably find it in \windows\system32, but
are there any other copies of this file on your drive? If there is another
copy in the Office folder or in Windows or somewhere on path, it is possible
that the wrong one is being loaded into memory.

After verifying that there is only one copy, right-click it in the Windows
Explorer and choose Properties. On the Version tab, what version do you see?
It should be 4.0.8015.0. If it is this version, then I'm at a loss to know
what to suggest further.


Nov 12 '05 #11
Try removing the file from VCM to another disk.
The most likely side effect is that some other piece of software that
expects the file to be there will not work correctly.

--
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.

"John Baker" <Ba******@Verizon.net> wrote in message
news:o7********************************@4ax.com...
Allen:

I do have 2000, yes.

In fact there are two versions of jet on the system (all else is correct). The Correct version is in C:Windows\system, however there is an earlier version in a folder called C:\widows\vcm (whatever that is!). Should I remover the version from VCM and substitute the correct one, or just remove the one from VCM and let sleeping dogs lay?
Thank you very much for helping on this issue, its very much appreciated.

Regards

JOhn

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
John, re-reading this thread, I think you have Access 2000.
You have installed the service pack, so Help | About should show SP3?

Just to verify that the correct version of JET is running, search your harddisk for msjet40.dll. You will probably find it in \windows\system32, but
are there any other copies of this file on your drive? If there is anothercopy in the Office folder or in Windows or somewhere on path, it is possiblethat the wrong one is being loaded into memory.

After verifying that there is only one copy, right-click it in the WindowsExplorer and choose Properties. On the Version tab, what version do you see?It should be 4.0.8015.0. If it is this version, then I'm at a loss to knowwhat to suggest further.

Nov 12 '05 #12

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

Similar topics

2
by: C L Humphreys | last post by:
Hi, I seem to have amassed a few thousand duplicates in an autonumber field. Is there a simple way of updating these to new numbers? Not sure if this is related - but I'm having problems...
5
by: JW | last post by:
Hi, This is really weird... I have a database that has an autonumber field. Records 1-70 were deleted. Various things have changed to the front end and a few extra fields added to the table. ...
1
by: Patrick Fleury | last post by:
We have a database which has recently started driving us nuts. It, and some of its ancestors, have been in use for about 8 years and, until last January, everything seemed fine. However, in the...
1
by: Knut B. | last post by:
I have a problem with a database of concerts, where I recently tried to change the existing database with some new rows/categories and some new names on a few of the existing ones. I have an id...
26
by: jimfortune | last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore, sometimes I use those same fields in orderdetail type tables. So it's important in that case that once an autonumber key value is...
2
by: Materialised | last post by:
Hi All, I am writing a program in C, which will be driving via a MS Access database. I am relitivly new to database design, so if anyone could help me with my issue I would be greatful. I have...
8
by: petebeatty | last post by:
I have created a SQL string the properly inserts a record in the table. However, the insert does not occur at the end of the table. Instead it inserts a record after the last record that I viewed....
2
by: Hiyella2 | last post by:
Hello, I have a problem with a table in my MS Access 2003 table. The primary key of this table is an autonumber field set to a Long Integer, Yes - no duplicates. The autonumber made it to 10252....
9
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field...
4
imrosie
by: imrosie | last post by:
Hello All, (newbie)...I'm working on an Ordering app using the typical tables (customers, orders). I have a Search form to find existing customers prior to entering a new order. On the search form...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.