473,218 Members | 1,445 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,218 software developers and data experts.

error 3022 when there are no dupes

Jan
Hi:

When I searched the newsgroup for this problem, I saw two or three
instances of the question being asked, but it was never answered. Not
too promising, but here goes:

I have a form with four subforms, and bit of code that cycles through
the data in the subform (bound to a local temp table) and writes it to a
table on the server. This code has run at the client with no problems
for over a year.

Now,
in the past few weeks, they get an error 3022 (duplicate value in index)
on many
(but not all) instances of running this code. I have stepped through
the code and can verify that there are no duplicate records in the
system when the error occurs.

Testing:
- I can't reproduce the error on my stand-alone system
- I have not gotten the error when I logged in remotely to the client; I
tend to do this either early am or on weekends, when no one else is on
their system.
- I did get the error when I logged on remotely to another system (an
intermediate player in this client relationship); in this case it was
mid-day when their system was busy.

I run Access 2003; the client runs A2003 on some machines, A2002 or maybe
even A2000 on others.

I wonder whether this error has to do with stand-alone vs network, or
what? The prior cases I found in the archives also mentioned not
getting the error on a stand-alone machine but getting it when logging
into a network.

The code is below

Set rsSource = Forms!comps_frm!UtilitiesSub.Form.RecordsetClone
If rsSource.RecordCount 0 Then
Set rsTarget = db.OpenRecordset("select * from
[TestUnitUtilities for forms]", _
dbOpenDynaset, dbSeeChanges)
rsSource.MoveFirst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget!TestUnitID = newID
rsTarget!UtilityID = rsSource!UtilityID
==>error 3022 here rsTarget.Update
rsSource.MoveNext
Loop
End If

Any ideas welcome!
Aug 14 '07 #1
7 7742
Hi Jan,

When you say that you are writing to the server -- do you mean you are
writing the data to a Sql Server table? Or is it a backend mdb that
resides on the server?

Well, it looks like your problem is occurring before you get to the
server part.

For starters I would not use RecordsetClone. Use DAO or ADO to set your
Recorset object

Change this

Set rsSource = Forms!comps_frm!UtilitiesSub.Form.RecordsetClone

to

Dim DB As DAO.Database, rsSource As DAO.Recordset
Set DB = CurrentDB
Set rsSource = DB.OpenRecordset("Select t1.* From tbl1 t1 Join tbl2 t2
On t1.someID = t2.SomeID Where somefield = 'something'")
...

Or if you are going to eventually write to a sql server table -- use ADO
and the ADO command Object.
to the

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 14 '07 #2
Jan
Hi, Rich:

It's an Access back end.

And I've happily and successfully used recordsetclone for a long time,
including in this application. So unless there's a reason related to
the error message I'm getting that would preclude its use, I'm inclined
to keep it.

Rich P wrote:
Hi Jan,

When you say that you are writing to the server -- do you mean you are
writing the data to a Sql Server table? Or is it a backend mdb that
resides on the server?

Well, it looks like your problem is occurring before you get to the
server part.

For starters I would not use RecordsetClone. Use DAO or ADO to set your
Recorset object

Change this

Set rsSource = Forms!comps_frm!UtilitiesSub.Form.RecordsetClone

to

Dim DB As DAO.Database, rsSource As DAO.Recordset
Set DB = CurrentDB
Set rsSource = DB.OpenRecordset("Select t1.* From tbl1 t1 Join tbl2 t2
On t1.someID = t2.SomeID Where somefield = 'something'")
..

Or if you are going to eventually write to a sql server table -- use ADO
and the ADO command Object.
to the

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 14 '07 #3
On Aug 14, 11:55 am, Jan <j...@dontspamme.comwrote:
Hi:

When I searched the newsgroup for this problem, I saw two or three
instances of the question being asked, but it was never answered. Not
too promising, but here goes:

I have a form with four subforms, and bit of code that cycles through
the data in the subform (bound to a local temp table) and writes it to a
table on the server. This code has run at the client with no problems
for over a year.

Now,
in the past few weeks, they get an error 3022 (duplicate value in index)
on many
(but not all) instances of running this code. I have stepped through
the code and can verify that there are no duplicate records in the
system when the error occurs.

Testing:
- I can't reproduce the error on my stand-alone system
- I have not gotten the error when I logged in remotely to the client; I
tend to do this either early am or on weekends, when no one else is on
their system.
- I did get the error when I logged on remotely to another system (an
intermediate player in this client relationship); in this case it was
mid-day when their system was busy.

I run Access 2003; the client runs A2003 on some machines, A2002 or maybe
even A2000 on others.

I wonder whether this error has to do with stand-alone vs network, or
what? The prior cases I found in the archives also mentioned not
getting the error on a stand-alone machine but getting it when logging
into a network.

The code is below

Set rsSource = Forms!comps_frm!UtilitiesSub.Form.RecordsetClone
If rsSource.RecordCount 0 Then
Set rsTarget = db.OpenRecordset("select * from
[TestUnitUtilities for forms]", _
dbOpenDynaset, dbSeeChanges)
rsSource.MoveFirst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget!TestUnitID = newID
rsTarget!UtilityID = rsSource!UtilityID
==>error 3022 here rsTarget.Update
rsSource.MoveNext
Loop
End If

Any ideas welcome!
You're editing a local table through a bound subform and obtaining
some concurrency relief, but not total concurrency relief. When two
such systems open a recordset with dbOpenDynaset to get the changes to
the server and try an AddNew at nearly the same time, you can still
get a collision. You might want to see if the TestUnitID exists
before doing the (dot)Update. If it does you might want to utilize
the following from the A97 help file (AddNew Method):

"Caution If you issue an AddNew and then perform any operation that
moves to another record, but without using Update, your changes are
lost without warning. In addition, if you close the Recordset or end
the procedure that declares the Recordset or its Database object, the
new record is discarded without warning."

The fact that you get the error when the network is busy suggests
nearly simultaneous Update's by two or more users. Note that even the
addition of that check does not guarantee completely that you will not
get a collision. But it may significantly reduce the number of
collisions you get or allow for a few more simultaneous users.

The frequency of collisions is also due, in part, to the amount of
time it takes to perform the Update. I had the a similar thing happen
when A2K3 versions of Access performed updates slower than the A97
versions. The customer opted for upgrading network hardware although
I think Samba on Linux would have worked at least as well. Also check
to be sure that Access A2K3 doesn't have any settings that would slow
down an Update. Note: I'm not aware of the effect of dbSeeChanges on
the Update speed.

James A. Fortune
CD********@FortuneJames.com

Aug 14 '07 #4
Jan
Hi, James:

Thanks for your reply.

First, the testunitID does exist; the parent record is saved before we
get to this step (we're adding records to the related table).

Second, your comment
The fact that you get the error when the network is busy suggests
nearly simultaneous Update's by two or more users. Note that even
the addition of that check does not guarantee completely that you
will not get a collision. But it may significantly reduce the number
of collisions you get or allow for a few more simultaneous users.
....makes sense when we're talking about the client's experience during
actual use; they do have quite a few clerks on the system at once. But
yesterday I was trying it when logged into another system (a third
party, related to the others), and although others were on their
network, I was the only one in the database. And I still got the same
error. So I don't see how it can be a two-or-more user issue.

Any other ideas? Or variations?

Jan
CD********@FortuneJames.com wrote:
On Aug 14, 11:55 am, Jan <j...@dontspamme.comwrote:
>Hi:

When I searched the newsgroup for this problem, I saw two or three
instances of the question being asked, but it was never answered.
Not too promising, but here goes:

I have a form with four subforms, and bit of code that cycles
through the data in the subform (bound to a local temp table) and
writes it to a table on the server. This code has run at the
client with no problems for over a year.

Now, in the past few weeks, they get an error 3022 (duplicate value
in index) on many (but not all) instances of running this code. I
have stepped through the code and can verify that there are no
duplicate records in the system when the error occurs.

Testing: - I can't reproduce the error on my stand-alone system - I
have not gotten the error when I logged in remotely to the client;
I tend to do this either early am or on weekends, when no one else
is on their system. - I did get the error when I logged on remotely
to another system (an intermediate player in this client
relationship); in this case it was mid-day when their system was
busy.

I run Access 2003; the client runs A2003 on some machines, A2002 or
maybe even A2000 on others.

I wonder whether this error has to do with stand-alone vs network,
or what? The prior cases I found in the archives also mentioned
not getting the error on a stand-alone machine but getting it when
logging into a network.

The code is below

Set rsSource = Forms!comps_frm!UtilitiesSub.Form.RecordsetClone If
rsSource.RecordCount 0 Then Set rsTarget =
db.OpenRecordset("select * from [TestUnitUtilities for forms]", _
dbOpenDynaset, dbSeeChanges) rsSource.MoveFirst Do Until
rsSource.EOF rsTarget.AddNew rsTarget!TestUnitID = newID
rsTarget!UtilityID = rsSource!UtilityID ==>error 3022 here
rsTarget.Update rsSource.MoveNext Loop End If

Any ideas welcome!

You're editing a local table through a bound subform and obtaining
some concurrency relief, but not total concurrency relief. When two
such systems open a recordset with dbOpenDynaset to get the changes
to the server and try an AddNew at nearly the same time, you can
still get a collision. You might want to see if the TestUnitID
exists before doing the (dot)Update. If it does you might want to
utilize the following from the A97 help file (AddNew Method):

"Caution If you issue an AddNew and then perform any operation that
moves to another record, but without using Update, your changes are
lost without warning. In addition, if you close the Recordset or end
the procedure that declares the Recordset or its Database object, the
new record is discarded without warning."

The fact that you get the error when the network is busy suggests
nearly simultaneous Update's by two or more users. Note that even
the addition of that check does not guarantee completely that you
will not get a collision. But it may significantly reduce the number
of collisions you get or allow for a few more simultaneous users.

The frequency of collisions is also due, in part, to the amount of
time it takes to perform the Update. I had the a similar thing
happen when A2K3 versions of Access performed updates slower than the
A97 versions. The customer opted for upgrading network hardware
although I think Samba on Linux would have worked at least as well.
Also check to be sure that Access A2K3 doesn't have any settings that
would slow down an Update. Note: I'm not aware of the effect of
dbSeeChanges on the Update speed.

James A. Fortune CD********@FortuneJames.com
Aug 14 '07 #5
In a multi-user environment, MS Access reliability decreases
significantly. Understand that Access is a file based desktop RDBMS
with limited network capabilities. I say this respectfully because I am
not critisizing Access. The design of an Access RDBMS is specifically a
file based system and not a server based system. For reliable
multi-user operations you should upgrade to a server based system like
MS Sql Server.

Access worked for a while for you in your scenario, and now it is not
working in that scenario. Why? Because it is being used in a manner
that it was not specifically designed for - by no fault of yours
(Microsoft just doesn't clearly specify the limitations of Access). So
in that case - it is hit and miss. It sounds like you are not getting a
solution to your problem using Access so far.

The easiest (most reliable) fix for your situation would be to step up
to a server based system where you have a lot more control over
concurrency issues, deadlocking, write conflicts ... I say this from
personal experience (lots of it) with similar problems I encountered
with Access when Company usage increased.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 14 '07 #6
On Aug 14, 4:11 pm, Jan <j...@dontspamme.comwrote:
Hi, James:

Thanks for your reply.

First, the testunitID does exist; the parent record is saved before we
get to this step (we're adding records to the related table).

Second, your commentThe fact that you get the error when the network is busy suggests
nearly simultaneous Update's by two or more users. Note that even
the addition of that check does not guarantee completely that you
will not get a collision. But it may significantly reduce the number
of collisions you get or allow for a few more simultaneous users.

...makes sense when we're talking about the client's experience during
actual use; they do have quite a few clerks on the system at once. But
yesterday I was trying it when logged into another system (a third
party, related to the others), and although others were on their
network, I was the only one in the database. And I still got the same
error. So I don't see how it can be a two-or-more user issue.
I see your point. Maybe we can still make some progress. I know it
doesn't make sense to have 'No Duplicates' on a foreign key, but when
you get an error on the .Update, it can be because of either
TestUnitID or UtilityID. Maybe comment out each of the two lines
separately using a new test subform record so that you are absolutely
sure that TestUnitID, not UtilityID, is causing the problem. I take
it that UtilityID is an AutoNumber primary key field for the master
table (tblUtilities) that comprises the main form's Recordset? How is
newID generated? If [TestUnitUtilities for forms] has an AutoNumber
primary key field you should be able to use that value to guarantee
uniqueness when you're the only person touching the database.

James A. Fortune
CD********@FortuneJames.com

Aug 14 '07 #7
Jan
Hi, James:

(response at end)

CD********@FortuneJames.com wrote:
On Aug 14, 4:11 pm, Jan <j...@dontspamme.comwrote:
>Hi, James:

Thanks for your reply.

First, the testunitID does exist; the parent record is saved before
we get to this step (we're adding records to the related table).

Second, your commentThe fact that you get the error when the
network is busy suggests
>>nearly simultaneous Update's by two or more users. Note that
even the addition of that check does not guarantee completely
that you will not get a collision. But it may significantly
reduce the number of collisions you get or allow for a few more
simultaneous users.
...makes sense when we're talking about the client's experience
during actual use; they do have quite a few clerks on the system at
once. But yesterday I was trying it when logged into another
system (a third party, related to the others), and although others
were on their network, I was the only one in the database. And I
still got the same error. So I don't see how it can be a
two-or-more user issue.

I see your point. Maybe we can still make some progress. I know it
doesn't make sense to have 'No Duplicates' on a foreign key, but
when you get an error on the .Update, it can be because of either
TestUnitID or UtilityID. Maybe comment out each of the two lines
separately using a new test subform record so that you are absolutely
sure that TestUnitID, not UtilityID, is causing the problem. I take
it that UtilityID is an AutoNumber primary key field for the master
table (tblUtilities) that comprises the main form's Recordset? How
is newID generated? If [TestUnitUtilities for forms] has an
AutoNumber primary key field you should be able to use that value to
guarantee uniqueness when you're the only person touching the
database.

James A. Fortune CD********@FortuneJames.com
Let me explain the structure a bit more. The parent table
(corresponding with the main form) is TestUnits,
whose key is testunitID. The sub table (w/subform) is
testunitUtilities, with only
three fields: TUutilityID (key), testunitID, and utilityID. Each field
is indexed, with dupes allowed on testunitID and utilityID, and an
additional noDupes index on the two fields combined. I thought it was
that combined index that was causing the problem so I got rid of
it....and still had the problem.

Also, this same structure exists in three other tables (each of the
other three subforms on this form), and there is a parallel bit of code
that does the same thing for each of these subforms. I get the error at
different times with different ones of these. That's what makes me
think the problem is not with the specific form or table, but with some
basic process in Access itself.

I thought for a while that it was corrupt data causing the problem, but
that doesn't seem to be it either.

I'm thoroughly confused and frustrated.

Jan
Aug 14 '07 #8

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

Similar topics

5
by: Patrice FRITSCH | last post by:
I'm trying to run a batch file from an asp page using WScript.Shell object. Dim oWSH set oWSH= Server.CreateObject("WScript.Shell") call oWSH.Run("cmd.exe /c " & szCmd , 0, true) szCmd...
4
by: Mark | last post by:
Not sure this is the right place for this questions, but here goes: I get an error message when deleting an table from a Access database. The code is as follows and the error message is after...
1
by: intl04 | last post by:
I am getting strange print-related error messages when trying to create (not print!) reports. For example, when I click 'new' to create a report then choose 'design view', I get an error message...
3
by: Nathan Bloomfield | last post by:
Hi there, I am having difficulty with a piece of code which would work wonders for my application if only the error trapping worked properly. Basically, it works as follows: - adds records...
2
by: brenda.stow | last post by:
error msg " An error occured while referencing the object. You tried to run a visual basic procedure that improperly references a property or method of an object" This msg occurs everytime I add a...
2
by: Timbit | last post by:
Hi, this is probably an old question, and I have had no luck finding answers to this one on the internet that has helped me out. I installed VS .NET 2003, then I installed IIS 5.1 after that. I...
8
by: g_man | last post by:
I am trying trap Runtime error 3022 (duplicates) in the click event of a command button that closes the form. I have code in the Form_Error event that does a good job of providing a more meaningful...
0
by: bullfrog83 | last post by:
On the OnClick event of a command button I have code that copies a parent record and all of its child records in the same table using recordsets: Private Sub cmdCopy_Click() On Error GoTo...
10
by: Adam Tippelt | last post by:
Hi, This one has been bugging me for a while, and I can't find a solution to get round it. The error message is: Run-time error '3022': The changes you requested to the table were not...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.