Access crashes on DAO delete against SQL Server

[david epsom dot com dot au]

CurrentDb.Execute "DELETE * FROM tblBK_BAISummary;", DAO.dbFailOnError Or
DAO.dbSeeChanges

That line crashes Access if at the same time this query is open in datasheet
view or as a form:

SELECT tblBK_BAITrans_local.*
FROM (tblCB_SettleTypes INNER JOIN tblBK_BAITrans_local
ON tblCB_SettleTypes.idxSettleType = tblBK_BAITrans_local.idxSettleType)
INNER JOIN tblBS_Accounts
ON tblBK_BAITrans_local.Accounts_idxID = tblBS_Accounts.idxID;


tblBK_BAITrans_Local is a local table in CurrentDb.
tblCB_SettleTypes and tblBS_Accounts are in SQL Server 2000.

tblBK_BAITrans_local is empty. Dynaset or Snapshot.

Access 2000,2002,2003. Fully patched PC's. Referencing only Access, VBA,
DAO.
Decompiled, compacted, rebuilt, relinked, imported to new mdb.

If the code is called from a macro, instead of crashing Access, I get a
macro error, and then Access won't close.

No error if DAO.dbFailOnError is not used.
No error if I use RunSQL instead of Execute.

error signature: AppName: msaccess.exe AppVer: 9.0.0.6620 ModName: ntdll.dll
ModVer: 5.1.2600.2180 Offset: 00001010

 

[Van T. Dinh]

Try:

CurrentDb.Execute "DELETE * FROM tblBK_BAISummary", _
dbFailOnError + dbSeeChanges

HTH
Van T. Dinh
MVP (Access)

 

[david epsom dot com dot au]

Db.Execute "DELETE * FROM tblBK_BAISummary", 512 + 128

same problem

 

[Van T. Dinh]

I guess the datasheet / Form has a lock on the Table, possibly just on the
current / edited Record and this will fail you Execute / Delete. However,
you should get an ODBC error rather than crashing out ...