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!

[Rich P]

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

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

[CDMAPoster]

On Aug 14, 11:55 am, Jan <[e-mail]:

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

[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 <[e-mail]:

>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

[Rich P]

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]

On Aug 14, 4:11 pm, Jan <[e-mail]:

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

[Jan]

Hi, James:

(response at end)

CD********@FortuneJames.com wrote:

On Aug 14, 4:11 pm, Jan <[e-mail]:

>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