Delay in INSERTing records

[mscertified]

I have a procedure that creates records in a bound form and then inserts
records to a related table. In the 'BeforeUpdate' event, I read the related
table to verify the existence of the required record, and if not existing,
display a msgbox.

This is all triggered via a command button.
When I click the button several times in succession slowly all my records
are processed error-free. However, when I click the button several times in
succession quickly (with the exact same data) I get error messages indicating
that the INSERT has not completed before the BeforeUpdate event triggers the
validations. Is this possible? And if so, how to avoid it?

 

[Marshall Barton]

I have a procedure that creates records in a bound form and then inserts
records to a related table. In the 'BeforeUpdate' event, I read the related
table to verify the existence of the required record, and if not existing,
display a msgbox.

This is all triggered via a command button.
When I click the button several times in succession slowly all my records
are processed error-free. However, when I click the button several times in
succession quickly (with the exact same data) I get error messages indicating
that the INSERT has not completed before the BeforeUpdate event triggers the
validations. Is this possible? And if so, how to avoid it?

I wonder how you are running the query? I take a guess that
you are using OpenQuery or RunSQL, which run aynchronously.

If that's even close, try using the Execute method instead.

 

[mscertified]

I am using EXECUTE, but I wondered if using a recordset would be faster.
Incidentally, if I insert a delay loop of .25 seconds after the Insert, it
cures the problem.

 

[Marshall Barton]

If you are executing an Insert query inside a loop, then a
recordset .AddNew might be faster because you can open a
query that returns no records just once. It doesn't sound
like that's what you are doing (except to simulate a loop by
clicking the button very quickly) so I seriously doubt that
it worth the effort to manage the recordset(s?).

If a delay loop solves the issue, then I would expect that
DoEvents would be even better without wasting cpu cycles. I
suspect that either your form processing or, more likely,
the BeforeUpdate validation is causing the delay.

OTOH, clicking a button rapidly implies that there is no
time spent getting user input. That, in turn, implies that
that all these records are just place holders for real data
to be entered at some later time, which is almost always a
bad thing to do.

 

[mscertified]

The records in the table represent a queue of records populated via a web page.
The user input has happened, but in the past.

If you are executing an Insert query inside a loop, then a
recordset .AddNew might be faster because you can open a
query that returns no records just once. It doesn't sound
like that's what you are doing (except to simulate a loop by
clicking the button very quickly) so I seriously doubt that
it worth the effort to manage the recordset(s?).

If a delay loop solves the issue, then I would expect that
DoEvents would be even better without wasting cpu cycles. I
suspect that either your form processing or, more likely,
the BeforeUpdate validation is causing the delay.

OTOH, clicking a button rapidly implies that there is no
time spent getting user input. That, in turn, implies that
that all these records are just place holders for real data
to be entered at some later time, which is almost always a
bad thing to do.
--
Marsh
MVP [MS Access]

I am using EXECUTE, but I wondered if using a recordset would be faster.
Incidentally, if I insert a delay loop of .25 seconds after the Insert, it
cures the problem.

 

[Marshall Barton]

The records in the table represent a queue of records populated via a web page.
The user input has happened, but in the past.

Did using DoEvents make a difference?

If you can find out how many records to insert, then you can
insert them all with one query instead of repeatedly
inserting one record at a time.

 

[mscertified]

I omitted to tell you that the Delay function I am using already issues a
DoEvents.
Unfortunately, I cannot insert them all at once, each record pulled in has
to be reviewed by an agent before it is saved. The normal situation is for
the user to pull in one record at a time, however, I am testing for unusual
circumstances.

I am currently having a similar problem related to deleting the records from
the table after processing. It seems the Delete does not happen
instantaneously and other users are able to read the same record from the
table - which is a disastrous situation.

 

[Marshall Barton]

I omitted to tell you that the Delay function I am using already issues a
DoEvents.
Unfortunately, I cannot insert them all at once, each record pulled in has
to be reviewed by an agent before it is saved. The normal situation is for
the user to pull in one record at a time, however, I am testing for unusual
circumstances.

I am currently having a similar problem related to deleting the records from
the table after processing. It seems the Delete does not happen
instantaneously and other users are able to read the same record from the
table - which is a disastrous situation.

I think I am out of ideas. Good luck.