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.F orm.RecordsetCl one
If rsSource.Record Count 0 Then
Set rsTarget = db.OpenRecordse t("select * from
[TestUnitUtiliti es for forms]", _
dbOpenDynaset, dbSeeChanges)
rsSource.MoveFi rst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget!TestUn itID = newID
rsTarget!Utilit yID = rsSource!Utilit yID
==>error 3022 here rsTarget.Update
rsSource.MoveNe xt
Loop
End If



Any ideas welcome!

[Rich P]

Re: error 3022 when there are no dupes

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.F orm.RecordsetCl one

to

Dim DB As DAO.Database, rsSource As DAO.Recordset
Set DB = CurrentDB
Set rsSource = DB.OpenRecordse t("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 ***

[Jan]

Re: error 3022 when there are no dupes

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.F orm.RecordsetCl one
>
to
>
Dim DB As DAO.Database, rsSource As DAO.Recordset
Set DB = CurrentDB
Set rsSource = DB.OpenRecordse t("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 ***

[CDMAPoster@FortuneJames.com]

Re: error 3022 when there are no dupes

On Aug 14, 11:55 am, Jan <j...@dontspamm e.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.F orm.RecordsetCl one
If rsSource.Record Count 0 Then
Set rsTarget = db.OpenRecordse t("select * from
[TestUnitUtiliti es for forms]", _
dbOpenDynaset, dbSeeChanges)
rsSource.MoveFi rst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget!TestUn itID = newID
rsTarget!Utilit yID = rsSource!Utilit yID
==>error 3022 here rsTarget.Update
rsSource.MoveNe xt
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
CDMAPoster@Fort uneJames.com

[Jan]

Re: error 3022 when there are no dupes

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


CDMAPoster@Fort uneJames.com wrote:

On Aug 14, 11:55 am, Jan <j...@dontspamm e.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.F orm.RecordsetCl one If
>rsSource.Recor dCount 0 Then Set rsTarget =
>db.OpenRecords et("select * from [TestUnitUtiliti es for forms]", _
>dbOpenDynase t, dbSeeChanges) rsSource.MoveFi rst Do Until
>rsSource.EOF rsTarget.AddNew rsTarget!TestUn itID = newID
>rsTarget!Utili tyID = rsSource!Utilit yID ==>error 3022 here
>rsTarget.Updat e rsSource.MoveNe xt 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 CDMAPoster@Fort uneJames.com
>

[Rich P]

Re: error 3022 when there are no dupes

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

[CDMAPoster@FortuneJames.com]

Re: error 3022 when there are no dupes

On Aug 14, 4:11 pm, Jan <j...@dontspamm e.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 [TestUnitUtiliti es 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
CDMAPoster@Fort uneJames.com

[Jan]

Re: error 3022 when there are no dupes

Hi, James:

(response at end)

CDMAPoster@Fort uneJames.com wrote:

On Aug 14, 4:11 pm, Jan <j...@dontspamm e.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
>>simultaneou s 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 [TestUnitUtiliti es 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 CDMAPoster@Fort uneJames.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
testunitUtiliti es, 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