MS Access is attempting to call SQL Server before BeforeDelConfirm

[D-Someone]

Our MS Access app has a SQL Server 2000 backend. We have a form viewed in
datasheet mode with a child datasheet. Whenever an item is attempted to be
deleted from the paraent datasheet MS Access pops up with an ODBC error if
there is a referential integrity problem. It will then CRASH everytime
causing the user to CTRL-ALT-DEL and restart the app.

I would like to put in a work around in the code so that in the delete
method or before delete confirm method I can cancel the delete and do it
manually by calling an SQL Server stored procedure using ADO (instead of
letting the MS Access form do it...and crash). Or I could put a msgbox in
the BeforeDelConfirm event to tell the user they cannot delete this record
until the child records are deleted first.

I can't even do this workaround because it seems BeforeDelConfirm wont even
be called until after MS Access pops up with an ODBC error and crashes....

FYI, The referential integrity problem is related to records being deleted
that exist in another table (not the sub datasheet)

Is there anything I can do?

<rant>I find MS Access is very buggy when dealing with an SQL Server backend
and these workarounds are buggy and annoying to have to deal with...
</rant>

 

[Dirk Goldgar]

In

Our MS Access app has a SQL Server 2000 backend. We have a form
viewed in datasheet mode with a child datasheet. Whenever an item is
attempted to be deleted from the paraent datasheet MS Access pops up
with an ODBC error if there is a referential integrity problem. It
will then CRASH everytime causing the user to CTRL-ALT-DEL and
restart the app.

I'm not sure what is causing this.

I would like to put in a work around in the code so that in the delete
method or before delete confirm method I can cancel the delete and do
it manually by calling an SQL Server stored procedure using ADO
(instead of letting the MS Access form do it...and crash). Or I
could put a msgbox in the BeforeDelConfirm event to tell the user
they cannot delete this record until the child records are deleted
first.

I can't even do this workaround because it seems BeforeDelConfirm
wont even be called until after MS Access pops up with an ODBC error
and crashes....

BeforeDelConfirm is raised *after* the records have already been
deleted -- or at least attempted to be deleted -- within a transaction
that can then be rolled back if the reply to the prompt is "No".

FYI, The referential integrity problem is related to records being
deleted that exist in another table (not the sub datasheet)

Is there anything I can do?

If you want to catch the delete action before the records are deleted,
use the Delete event. It will fire for each record to be deleted. In
the event procedure for that event, you can set the Cancel argument to
True, thus preventing the record from being deleted.

An alternative would be to use a continuous form instead of datasheet
view, set the form's AllowDeletions property to No, and provide your own
button to delete records. That button could do all the checking you
want to do, and/or call the stored procedure to delete the record, and
then requery the form to reflect the deletion.

<rant>I find MS Access is very buggy when dealing with an SQL Server
backend and these workarounds are buggy and annoying to have to deal
with... </rant>

I haven't had so many problems, but I do find that for any complex
application, I have to program carefully for the SQL Server environment.