Access to SQL server - question on deleting record

[PurpleUnicorn]

I have an Access database(F/E and B/E) that I am in the process of migrating to SQL Server. I currently have sample data in SQL Server and I am testing my Access F/E.

I have a delete trigger that is firing correctly - however, I am not getting the error message from my Access F/E.

Here is my trigger:
ALTER Trigger [CUSTOMERdelete] on [dbo].[CUSTOMER]
for delete
as
BEGIN
declare
@errorNumber int,
@errorMsg varchar(255)


if exists(
select * from deleted, ORDERS where ORDERS.CUSTOMER_ID = deleted.CUSTOMER_ID)
begin
select @errorNumber = 30004,
@errorMsg = 'Cannot delete from CUSTOMER because ORDERS exists.'
goto errorHandler
end

return
errorHandler:
raiserror @errorNumber @errorMsg
rollback transaction
END
GO

I have also tried creating Foreign Key constraint (and dropping the trigger) - still no error message in my front end.

If there are child records - the parent does not get deleted, but no error message is displayed.

 

[lameid]

Is the F/E Access Data Project or typical MDB?

How are you calling the delete?

 

[PurpleUnicorn]

Thanks for the reply.

The F/E is an MDB. The delete is done by clicking a button - Docmd.RunCommand acCmdDeleteRecord.

 

[lameid]

Something off the application object (docmd) is not going to show you any errors...

I think you should move into ADO. I'm not quite sure if your error would be a return or a property but two things to check out are the command object and the connection object. Take a look at thread958-1226791 to get some ideas.