By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,965 Members | 2,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,965 IT Pros & Developers. It's quick & easy.

Autonumber Duplicates Problem

P: n/a
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
Share this Question
Share on Google+
11 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.