Access ADO Form Insert Bug

I have a bound subform (employee) which linked to the parent form
(company) by the argument to the subform's SQL Server stored procedure
(SP). The UniqueTable property is set to "tblEmployee".

Depending on the complexity of the SP (more of which later), after I
insert a record I get the following message...

"Key value for this row was changed or deleted at the data store. The
local row is now deleted" and then Still on the new record - all the
fields change to #Deleted. Then the message "Multiple-step operation
generated errors. Check each status value" appears.

I can only release myself from this situation by closing the form, the
"Multiple-step..." error appear many times followed by "Another user or
application has deleted this record or changed the value of its primary
key".

On return to the form the new employee record is there in all its
beauty. I therefore conclude that there was actually nothing wrong
with the new record.

As I hinted, the problem seems to be related to the complexity of the
SP eg "SELECT Coy.*, Emp.*..." causes the above errors but "SELECT *
FROM ..." doesn't(!). I don't want to chase that particular art down
the drainpipe, though, because I'm looking for a solution in which I
can any valid SP code.

That's not all though...

....If I dumb down the design of the SP enough to enable me to insert a
new employee record, an entirely unrelated employee record (ie an
employee from another company) appears in the subform (!!). After I
refresh the subform, the rogue record disappears and the newly added
record appears.

Does anyone know what's going on here?

i'll try to look into this later

i'd start with the basics:

a) do you have triggers
b) have you run SQL profiler or sp_who2 to see what else is hitting
this table

Thanks Aaron,

In answer:-

a) relevant triggers are disabled.
b) I'm the only user. There are no background routines running.

1- Make sure that you have a primary key. It's not a bad idea to have
primary keys constitued of single field as I had trouble in the past with
composite primary keys.

2- Create a Resync command. This is probably the best way for solving your
problem.

In some case, a dummy Resync command (which is simply the name of a SP
without the right number of parameters) might be a good choice; as this will
force Access to use the default behavior of ADO, which is simply to return
the same values without requiring SQL-Server.

3- As suggested in the other post, take a look at what Access is doing by
using the SQL-Server Profiler; as this is the only practical way of
determining what Access is trying to do and finding how to compensate for
its limitations.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Pete Nurse" wrote in message
oups.com...
I have a bound subform (employee) which linked to the parent form
(company) by the argument to the subform's SQL Server stored procedure
(SP). The UniqueTable property is set to "tblEmployee".

Depending on the complexity of the SP (more of which later), after I
insert a record I get the following message...

"Key value for this row was changed or deleted at the data store. The
local row is now deleted" and then Still on the new record - all the
fields change to #Deleted. Then the message "Multiple-step operation
generated errors. Check each status value" appears.

I can only release myself from this situation by closing the form, the
"Multiple-step..." error appear many times followed by "Another user or
application has deleted this record or changed the value of its primary
key".

On return to the form the new employee record is there in all its
beauty. I therefore conclude that there was actually nothing wrong
with the new record.

As I hinted, the problem seems to be related to the complexity of the
SP eg "SELECT Coy.*, Emp.*..." causes the above errors but "SELECT *
FROM ..." doesn't(!). I don't want to chase that particular art down
the drainpipe, though, because I'm looking for a solution in which I
can any valid SP code.

That's not all though...

...If I dumb down the design of the SP enough to enable me to insert a
new employee record, an entirely unrelated employee record (ie an
employee from another company) appears in the subform (!!). After I
refresh the subform, the rogue record disappears and the newly added
record appears.

Does anyone know what's going on here?

Sylvain, thanks for the response.

1/ Yes, I use a single field primary key of type INT with identity set
(ie autonumber).

2/ As you suggest, I've tried entering a dummy Resynch command and it
seemed to overcome the reported problem, though I don't see how I can
enter "a SP
without the right number of parameters" without receiving an error. In
fact, it seems that entering a non-existent, nonsense SP name works
just as well (ie you get an error message). Could you please explain
how I can overcome that.

I tried entering the Resynch command as recommended at . . .

http://msdn.microsoft.com/library/de...ynccommand.asp

It seemed to work sometimes but not always. Certainly not as well
(apart from the error message) as the dummy SP idea.

3/ I had a look at Profiler, but it's pretty arcane - I have no idea
what I'm looking at.

Thanks for your response - maybe we're homing in on the solution.

Strange that a Resync command doesn't work well; as this should work in
every case.

Here an exemple of the dummy Resync command that I use:

CREATE PROCEDURE dbo.DummyResync (@p1 int, @p2 int, @p3 int, @p4 int, @p5
int)
AS
SELECT @p1, @p2, @p3, @p4, @p5
GO

and I call it this way:

DummyResync ?, ?, ?, ?, ?

The article referenced in your previous post says to use the following
syntax instead:

"{call CustordersResync (?)}"

but I didn't have the time to make a full analysis of this.

For the Profiler, click on File -- New -- Trace; then give the correct
connection parameters and click OK. After that, click on Run and you should
see a basic trace showing you all the commands sent by Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Pete Nurse" wrote in message
ups.com...
Sylvain, thanks for the response.

1/ Yes, I use a single field primary key of type INT with identity set
(ie autonumber).

2/ As you suggest, I've tried entering a dummy Resynch command and it
seemed to overcome the reported problem, though I don't see how I can
enter "a SP
without the right number of parameters" without receiving an error. In
fact, it seems that entering a non-existent, nonsense SP name works
just as well (ie you get an error message). Could you please explain
how I can overcome that.

I tried entering the Resynch command as recommended at . . .

http://msdn.microsoft.com/library/de...ynccommand.asp

It seemed to work sometimes but not always. Certainly not as well
(apart from the error message) as the dummy SP idea.

3/ I had a look at Profiler, but it's pretty arcane - I have no idea
what I'm looking at.

Thanks for your response - maybe we're homing in on the solution.

When I use "DummyResync ?, ?, ?, ?, ?", I get the error "No value
given for one or more required parameters". When I just use
"DummyResych" (no parameters), I get no error - especially I don't get
the errors that brought me here, except . . .

.. . . remember the rogue ID problem from the original post? In the
example below empID is the autonumber employee ID and I'm entering a
new record "Craigie, Vanessa". After inserting the record, empID entry
changes from "(Autonumber)" to the rogue ID (in this case 528, it
increments for reason I don't understand - when I started this the ID
numbers were in the 400s):-
empID Last Name First Name
17853 Boon Elizabeth
17852 Evans Susan
528 Craigie Vanessa

Now, empID 528 has now been deleted and long forgotten and I'm sure SQL
Server will make sure I never use it again. BTW this rogue ID is the
parameter Access sends to the Resync Command SP (I understood enough in
SQL Profiler to see that!).

After I refresh the form, it reorders and corrects the empID:-
empID Last Name First Name
17853 Boon Elizabeth
17852 Evans Susan
30068 Craigie Vanessa

The more worrying thing is that the rogue ID may actually exist, in
which case I think it can update the wrong data (ie an employee from a
totally different company)! This I find quite scarey. I saw this
behaviour last week but it was so confused with the other insoluble
problems that I didn't follow it up.

I can work around the problem (sort of) with the following code which
immediately refreshes the form and moves the cursor to the new (empty)
record . . .
Private Sub Form_AfterInsert()
'Due to a weird bug in Access ADP (in which a completely unrelated
record is magically displayed!),
'we need to refresh then reset and then move to the bottom of the
subform:
Me.Refresh
Me.SelTop = Me.RecordsetClone.RecordCount + 1
End Sub
.. . . but that is an intellectually unacceptable way to solve the
problem.

I think this now becomes the main problem. I guess I should start
another post, but I'll see how this one flies.

Any ideas?

Any trigger performing any kind of insert (even in a temporary table) on the
SQL-Server side?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Pete Nurse" wrote in message
oups.com...
When I use "DummyResync ?, ?, ?, ?, ?", I get the error "No value
given for one or more required parameters". When I just use
"DummyResych" (no parameters), I get no error - especially I don't get
the errors that brought me here, except . . .

. . . remember the rogue ID problem from the original post? In the
example below empID is the autonumber employee ID and I'm entering a
new record "Craigie, Vanessa". After inserting the record, empID entry
changes from "(Autonumber)" to the rogue ID (in this case 528, it
increments for reason I don't understand - when I started this the ID
numbers were in the 400s):-
empID Last Name First Name
17853 Boon Elizabeth
17852 Evans Susan
528 Craigie Vanessa

Now, empID 528 has now been deleted and long forgotten and I'm sure SQL
Server will make sure I never use it again. BTW this rogue ID is the
parameter Access sends to the Resync Command SP (I understood enough in
SQL Profiler to see that!).

After I refresh the form, it reorders and corrects the empID:-
empID Last Name First Name
17853 Boon Elizabeth
17852 Evans Susan
30068 Craigie Vanessa

The more worrying thing is that the rogue ID may actually exist, in
which case I think it can update the wrong data (ie an employee from a
totally different company)! This I find quite scarey. I saw this
behaviour last week but it was so confused with the other insoluble
problems that I didn't follow it up.

I can work around the problem (sort of) with the following code which
immediately refreshes the form and moves the cursor to the new (empty)
record . . .
Private Sub Form_AfterInsert()
'Due to a weird bug in Access ADP (in which a completely unrelated
record is magically displayed!),
'we need to refresh then reset and then move to the bottom of the
subform:
Me.Refresh
Me.SelTop = Me.RecordsetClone.RecordCount + 1
End Sub
. . . but that is an intellectually unacceptable way to solve the
problem.

I think this now becomes the main problem. I guess I should start
another post, but I'll see how this one flies.

Any ideas?

Peter,

Some questions that may generate more heat:

1 How are you passing the company id to the subform's sp?
2 What is the param list to the sp?
3 What is the complete text of the sp, both the one that gives errors and the one that does not?
4 Do you have 'Link Child Fields' and 'Link master Fields' set on the parent's subform object?
5 Do you have 'Input Parameters' set on the subform.Form object?
6 What were the exact values you tried as the 'Resync Command' and what was the exact behavior that resulted (per Syvain's line
of thought


--
Malcolm Cook -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


"Pete Nurse" wrote in message oups.com...
I have a bound subform (employee) which linked to the parent form
(company) by the argument to the subform's SQL Server stored procedure
(SP). The UniqueTable property is set to "tblEmployee".

Depending on the complexity of the SP (more of which later), after I
insert a record I get the following message...

"Key value for this row was changed or deleted at the data store. The
local row is now deleted" and then Still on the new record - all the
fields change to #Deleted. Then the message "Multiple-step operation
generated errors. Check each status value" appears.

I can only release myself from this situation by closing the form, the
"Multiple-step..." error appear many times followed by "Another user or
application has deleted this record or changed the value of its primary
key".

On return to the form the new employee record is there in all its
beauty. I therefore conclude that there was actually nothing wrong
with the new record.

As I hinted, the problem seems to be related to the complexity of the
SP eg "SELECT Coy.*, Emp.*..." causes the above errors but "SELECT *
FROM ..." doesn't(!). I don't want to chase that particular art down
the drainpipe, though, because I'm looking for a solution in which I
can any valid SP code.

That's not all though...

...If I dumb down the design of the SP enough to enable me to insert a
new employee record, an entirely unrelated employee record (ie an
employee from another company) appears in the subform (!!). After I
refresh the subform, the rogue record disappears and the newly added
record appears.

Does anyone know what's going on here?

you know that if your sproc is looking for a parameter @txtPLU if you
have a control named 'txtPLU' it will automatically fill it in?

i think that this is undocumented; but i love this feature enough that
I'm still madly in love with ADP 5 years after it came out.