Risk of Duplicates

[Telesphore]

The database was working well. Then this week the secretaries could not make
an inscription for a new student in the table tblStudents. The message says:
"Risk of duplicates...".

Is there an easy way to correct this?

Thank you in advance.

 

[Tom Wickerath]

It sounds like whoever developed this database may have implemented the Soundex function, as a
way of alerting a user to a potential duplicate entry. The secretaries are likely using a form to
enter new data, and code is being run to check for dups.

Another possibility might be code that is designed to alert the user to a potential duplicate
primary key, if the tblStudents table is not using an autonumber type primary key. What is the
data type for the primary key field in this table?

Can you enter the new record directly into the table, bypassing any code that is behind the form?

Tom
________________________________________


The database was working well. Then this week the secretaries could not make
an inscription for a new student in the table tblStudents. The message says:
"Risk of duplicates...".

Is there an easy way to correct this?

Thank you in advance.

 

[Telesphore]

Thank you Tom,

"Tom Wickerath":
It sounds like whoever developed this database may have implemented the
Soundex function, as a way of alerting a user to a potential duplicate
entry.

I did not write a code to alert of potential duplicates.

Another possibility might be code that is designed to alert the user to a
potential duplicate primary key, if the tblStudents table is not using an
autonumber type primary key. What is the data type for the primary key
field in this table?

StudentID and data type is AutoNumber.

Can you enter the new record directly into the table, bypassing any code
that is behind the form?

No, it is not possible to access to a new record at all.

Chance that a copy was saved one month earlier. So the secretaries are
comparing the latter copy with the former.

Anyway, it sounds very strange. Is it possible that one secretary is using
Windows XP and the other Windows 98. And more, the first is using Office XP
and the other Office 2000?

Or maybe they tried to change something in design mode? But what? Who knows!

 

[Tom Wickerath]

I did not write a code to alert of potential duplicates.

So, you are saying that you created this database?

No, it is not possible to access to a new record at all.

It's not possible to even access a new record when opening the table directly? Is tblStudents a
local table or a linked table?

Anyway, it sounds very strange. Is it possible that one secretary is
using Windows XP and the other Windows 98. And more, the first
is using Office XP and the other Office 2000?

Is it possible....? I suppose it is possible that the secretaries in your group are using a
mixed environment. This is certainly not an optimum situation for a multi-user Access database.
However, as far as I know, the message you quoted in the original post: "Risk of duplicates..."
is not an Access or JET error message. It has to be coming from somewhere, but it's not coming
from the product itself. Have you quoted the exact error message correctly? If so, then this
message is most likely coming from a MsgBox statement that is found in an error handler somewhere
in a code module. Try opening the VBA editor (ALT F11) and searching for the word "Risk"
(without the quotes). Select Current Project to search all code modules in this database.

Assuming that you can locate a procedure (subroutine or function) that includes this message,
then you can go from there attempting to track it down. If you developed this database, and you
know that you did not include such a MsgBox statement, then I'd be very interested in seeing a
copy of this database. Are you willing to provide a compacted, zipped copy, where you can
duplicate this error message?

Tom
______________________________________

"Tom Wickerath":
It sounds like whoever developed this database may have implemented the
Soundex function, as a way of alerting a user to a potential duplicate
entry.

I did not write a code to alert of potential duplicates.

Another possibility might be code that is designed to alert the user to a
potential duplicate primary key, if the tblStudents table is not using an
autonumber type primary key. What is the data type for the primary key
field in this table?

StudentID and data type is AutoNumber.

Can you enter the new record directly into the table, bypassing any code
that is behind the form?

No, it is not possible to access to a new record at all.

Chance that a copy was saved one month earlier. So the secretaries are
comparing the latter copy with the former.

Anyway, it sounds very strange. Is it possible that one secretary is using
Windows XP and the other Windows 98. And more, the first is using Office XP
and the other Office 2000?

Or maybe they tried to change something in design mode? But what? Who knows!

 

[Tom Wickerath]

I suppose such a message could also be coming from a MsgBox statement in a macro. Does your
database contain macros? You can try using the built-in database documentor (Tools > Analyze >
Documentor) to try to locate the origins of the error message you indicated. Select all macros
for documentation. Click on the Advanced button and make sure that all three options are checked.
Press OK to start the documentation, after closing the advanced options dialog.

When finished, a report should be displayed on your screen. Click on File > Export... to export
this report. In the Save as Type dropdown, select Text files. Save the file. Then open the text
file with a text editor, such as NotePad or WordPad. Search for the word "Risk" without the
quotes. Do you find it now?

 

[Telesphore]

Thank you Tom,

Here are my answers to your questions:

"Tom Wickerath":

So, you are saying that you created this database?

Yes, I created it using the John Viescas book "Running Microsoft Access
2000" and adapting the sample file "Books.mdb" to our needs.

Is tblStudents a local table or a linked table?

tblStudents is a linked table as the other tables. They are in a computer in
the network, so that the other users have only the queries, forms, reports,
macros and modules.

However, as far as I know, the message you quoted in the original post:
"Risk of duplicates..." is not an Access or JET error message. It has to
be coming from somewhere, but it's not coming from the product itself.
Have you quoted the exact error message correctly?

They are using Access in French. So the message "Risk of..." was a
translation of mine. Belonging to the product, and not from different codes
that I copied, it should read:

"The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again. (Error 3022)"

 

[Telesphore]

Since it is a system error message (Error 3022), I didn't need to locate
this error message in the macros.

Thank you again.

 

[Tom Wickerath]

Okay, so, if I understand you correctly, your users are getting error 3022 with an autonumber
primary key, right?

First, make a back-up copy of the back-end database (the database that includes the tblStudents
table). Then do a compact and repair of this database. You'll need to open the database in
exclusive mode to do this (ie. no other users have it open at the same time). Then click on Tools

Database Utilities > Compact and Repair.

Verify that you do not have any other fields indexed in this table, where the index type
displayed in the lower window is "Yes (No Duplicates)". Open the table in design view (again,
exclusive access required) and then click on View > Indexes. Select the first Index Name
displayed. Inspect the Unique property for this index displayed in the lower portion of the
window, then select the next index name, etc. until you've looked at all defined indexes. The
primary key should be unique, of course. If any other fields, or combinations of fields for a
multi-field index, are defined as unique then make sure that the new record is not attempting to
add duplicate information that the index specifically prohibits.

If you cannot find any unique indexes on fields other than your autonumber primary key that might
explain this error message, then you might possibly (?) have an issue with the JET database
engine attempting to assign a duplicate PK value. You might also have a corrupted database.
Question: Are all of your users using the latest JET service pack? See the following Knowledge
Base article for instructions on how to determine the current SP level for each user's
installation of JET:

How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine
http://support.microsoft.com/default.aspx?id=239114

Also, just for good measure, are all users using the latest service pack for the version of
Access that they are running?

You may need to try importing all of the tables in the back-end database into a brand new
database, in case this issue is corruption related.

Tom
________________________________________

However, as far as I know, the message you quoted in the original post:
"Risk of duplicates..." is not an Access or JET error message. It has to
be coming from somewhere, but it's not coming from the product itself.
Have you quoted the exact error message correctly?

They are using Access in French. So the message "Risk of..." was a
translation of mine. Belonging to the product, and not from different codes
that I copied, it should read:

"The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again. (Error 3022)"

 

[Telesphore]

Thank you Tom,

"Tom Wickerath" :.

Okay, so, if I understand you correctly, your users are getting error 3022
with an autonumber primary key, right?
Yes

First, make a back-up copy of the back-end database (the database that
includes the tblStudents table). Then do a compact and repair of this
database.

It didn't work

Open the table in design view...
The primary key should be unique, of course.

I left the primary key unique and change to "no" the index all the other
fields and it didn'work either.

you might possibly (?) have an issue with the JET database

I didn't check it yet

You may need to try importing all of the tables in the back-end >database
into a brand new database, in case this issue is corruption >related.

I did this last solution and it worked. I created a brand new database,
imported the tables of the back-end and rename the new base with the name of
the deficient one "IFTM_Princip"

So we can suppose that the base was corrupted. I check it with some forms
and report and it works.

Thank you very much. This last solution was the simplest! Sometimes the
easiest means are the best.

Thanks again

Telesphore

 

[Tom Wickerath]

Glad to hear that you got this issue solved!
Note that it really helps others when you can include the actual error number. If you do provide
a translation of an error message, it should be clearly indicated that its a translation.

Tom
_______________________________________


Thank you Tom,

"Tom Wickerath" :.

Okay, so, if I understand you correctly, your users are getting error 3022
with an autonumber primary key, right?
Yes

First, make a back-up copy of the back-end database (the database that
includes the tblStudents table). Then do a compact and repair of this
database.

It didn't work

Open the table in design view...
The primary key should be unique, of course.

I left the primary key unique and change to "no" the index all the other
fields and it didn'work either.

you might possibly (?) have an issue with the JET database

I didn't check it yet

You may need to try importing all of the tables in the back-end >database
into a brand new database, in case this issue is corruption >related.

I did this last solution and it worked. I created a brand new database,
imported the tables of the back-end and rename the new base with the name of
the deficient one "IFTM_Princip"

So we can suppose that the base was corrupted. I check it with some forms
and report and it works.

Thank you very much. This last solution was the simplest! Sometimes the
easiest means are the best.

Thanks again

Telesphore