Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 00:37:48 von Ben

Hi!

I have a trigger created for Customer table. My front-end is access. What
is the best approach to handle a trigger result when adding a new customer
record?

Below is the trigger script:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -

CREATE TRIGGER dbo.trTrackInsert

ON dbo.Customers

FOR INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Validate the new record.

-- Criteria:

-- 1. Check if there is already a record in the NewCustTracker

-- 1.1. If no record add to the table and record the user info

-- 1.2. If there is record

-- 1.2.1 Inform user that there is pending new record to be completed

-- 1.2.2 Perform roll back of the insert in the Customers table

-- Initialize variables to use in getting some info in the NewCustTracker
and Customer

-- tables.

DECLARE @recordCount int;

DECLARE @userName nvarchar(200);

SET @recordCount = 0;

SET @userName = '';

-- get the record count in the dbo.NewCustTracker table

SET @recordCount = (SELECT count(*) FROM dbo.NewCustTracker);

BEGIN TRANSACTION insertIntoNewCustTracker

IF (@recordCount > 0)

BEGIN

-- get the info in the NewCustTracker table...

SET @userName = (SELECT UserName FROM dbo.NewCustTracker);

RAISERROR(N'There is a pending new customer record to be completed by %s.
Please recheck in a couple of minutes.',16,1,@userName);

ROLLBACK TRANSACTION insertIntoNewCustTracker;

END

ELSE

BEGIN

-- record the new customer record in the NewCustTracker table for next
validation...

INSERT INTO dbo.NewCustTracker(CustNum, UserName)

SELECT [Customer Number], user_name() FROM inserted;

IF @@TranCount > 0

COMMIT TRANSACTION insertIntoNewCustTracker;

END

END

GO

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -

Any ideas/suggestions are appreciated.

Thanks,

Ben

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 01:14:41 von u28780

As I understand it, from reading numerous threads over the years, triggers
are not allowed/cannot be used in any version of Access.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 01:20:01 von Ben

Below is our software setup:

- Front-End MS Access
- Back-End MS SQL Server

The script has been created in the back-end

"Linq Adams via AccessMonster.com" wrote in message
news:8296d31df70e0@uwe...
> As I understand it, from reading numerous threads over the years, triggers
> are not allowed/cannot be used in any version of Access.
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1
>

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 02:24:33 von Rick Brandt

Ben wrote:
> Hi!
>
> I have a trigger created for Customer table. My front-end is access.
> What is the best approach to handle a trigger result when adding a
> new customer record?
>
> Below is the trigger script:

I don't believe any "result" would be usable by Access other than if you
raise an error.

What are you expecting or desiring to have happen?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 02:24:33 von Rick Brandt

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 02:43:59 von Tom van Stiphout

On Sat, 12 Apr 2008 22:37:48 GMT, "Ben"
wrote:

I didn't test this, but I would think the RAISEERROR would generate a
trappable runtime error in your Access app. Depending on the exact
details of your app, it could be trapped in the Form_Error event, or
if your form is unbound or the record is inserted using ADO or
similar, in that procedure.

-Tom.

>Hi!
>
>I have a trigger created for Customer table. My front-end is access. What
>is the best approach to handle a trigger result when adding a new customer
>record?
>
>Below is the trigger script:
>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>- - - - - - - - - -
>
>CREATE TRIGGER dbo.trTrackInsert
>
>ON dbo.Customers
>
>FOR INSERT
>
>AS
>
>BEGIN
>
>-- SET NOCOUNT ON added to prevent extra result sets from
>
>-- interfering with SELECT statements.
>
>SET NOCOUNT ON;
>
>-- Validate the new record.
>
>-- Criteria:
>
>-- 1. Check if there is already a record in the NewCustTracker
>
>-- 1.1. If no record add to the table and record the user info
>
>-- 1.2. If there is record
>
>-- 1.2.1 Inform user that there is pending new record to be completed
>
>-- 1.2.2 Perform roll back of the insert in the Customers table
>
>-- Initialize variables to use in getting some info in the NewCustTracker
>and Customer
>
>-- tables.
>
>DECLARE @recordCount int;
>
>DECLARE @userName nvarchar(200);
>
>SET @recordCount = 0;
>
>SET @userName = '';
>
>-- get the record count in the dbo.NewCustTracker table
>
>SET @recordCount = (SELECT count(*) FROM dbo.NewCustTracker);
>
>BEGIN TRANSACTION insertIntoNewCustTracker
>
>IF (@recordCount > 0)
>
>BEGIN
>
>-- get the info in the NewCustTracker table...
>
>SET @userName = (SELECT UserName FROM dbo.NewCustTracker);
>
>RAISERROR(N'There is a pending new customer record to be completed by %s.
>Please recheck in a couple of minutes.',16,1,@userName);
>
>ROLLBACK TRANSACTION insertIntoNewCustTracker;
>
>END
>
>ELSE
>
>BEGIN
>
>-- record the new customer record in the NewCustTracker table for next
>validation...
>
>INSERT INTO dbo.NewCustTracker(CustNum, UserName)
>
>SELECT [Customer Number], user_name() FROM inserted;
>
>IF @@TranCount > 0
>
>COMMIT TRANSACTION insertIntoNewCustTracker;
>
>END
>
>END
>
>GO
>
>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>- - - - - - - - - -
>
>Any ideas/suggestions are appreciated.
>
>
>
>
>
>Thanks,
>
>Ben
>
>
>
>
>

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 03:57:12 von Erland Sommarskog

Ben (pillars4@sbcglobal.net) writes:
>
> I have a trigger created for Customer table. My front-end is access.
> What is the best approach to handle a trigger result when adding a new
> customer record

I'm not really sure what you are asking, but if you plan to return
to result sets from triggers, think twice. Microsoft has deprecated
this and plan to remove it in a later version.

Error messages are of course a fair game. How you best handle them in
your Access application, I leave to other to answer.

Another note: you have BEGIN and COMMIT in the trigger. This is a
little overkill, because a trigger always runs in the context of
the statement that fired the trigger. Furthermore, if there is an
error or a rollback in the trigger, this aborts the batch and rolls
back the transaction entirely.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 13.04.2008 03:57:12 von Erland Sommarskog

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 15.04.2008 04:15:36 von Ed Murphy

Ben wrote:

> -- 1. Check if there is already a record in the NewCustTracker
> -- 1.2. If there is record
> -- 1.2.2 Perform roll back of the insert in the Customers table

> RAISERROR(N'There is a pending new customer record to be completed by %s.
> Please recheck in a couple of minutes.',16,1,@userName);

Out of curiosity, why do you need to serialize this process?

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 15.04.2008 04:15:36 von Ed Murphy

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 19.04.2008 09:09:06 von Ben

Update on this issue. I have finished my changes to the front-end and
back-end to make it work.

I did the following:
a. Created the insert and update triggers at the back-end
b. Modified my stored procedure to capture the user-defined error message I
created for this task
c.

"Ben" wrote in message
news:0RaMj.951$FF6.571@newssvr29.news.prodigy.net...
> Hi!
>
> I have a trigger created for Customer table. My front-end is access.
> What is the best approach to handle a trigger result when adding a new
> customer record?
>
> Below is the trigger script:
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - -
>
> CREATE TRIGGER dbo.trTrackInsert
>
> ON dbo.Customers
>
> FOR INSERT
>
> AS
>
> BEGIN
>
> -- SET NOCOUNT ON added to prevent extra result sets from
>
> -- interfering with SELECT statements.
>
> SET NOCOUNT ON;
>
> -- Validate the new record.
>
> -- Criteria:
>
> -- 1. Check if there is already a record in the NewCustTracker
>
> -- 1.1. If no record add to the table and record the user info
>
> -- 1.2. If there is record
>
> -- 1.2.1 Inform user that there is pending new record to be completed
>
> -- 1.2.2 Perform roll back of the insert in the Customers table
>
> -- Initialize variables to use in getting some info in the NewCustTracker
> and Customer
>
> -- tables.
>
> DECLARE @recordCount int;
>
> DECLARE @userName nvarchar(200);
>
> SET @recordCount = 0;
>
> SET @userName = '';
>
> -- get the record count in the dbo.NewCustTracker table
>
> SET @recordCount = (SELECT count(*) FROM dbo.NewCustTracker);
>
> BEGIN TRANSACTION insertIntoNewCustTracker
>
> IF (@recordCount > 0)
>
> BEGIN
>
> -- get the info in the NewCustTracker table...
>
> SET @userName = (SELECT UserName FROM dbo.NewCustTracker);
>
> RAISERROR(N'There is a pending new customer record to be completed by %s.
> Please recheck in a couple of minutes.',16,1,@userName);
>
> ROLLBACK TRANSACTION insertIntoNewCustTracker;
>
> END
>
> ELSE
>
> BEGIN
>
> -- record the new customer record in the NewCustTracker table for next
> validation...
>
> INSERT INTO dbo.NewCustTracker(CustNum, UserName)
>
> SELECT [Customer Number], user_name() FROM inserted;
>
> IF @@TranCount > 0
>
> COMMIT TRANSACTION insertIntoNewCustTracker;
>
> END
>
> END
>
> GO
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - -
>
> Any ideas/suggestions are appreciated.
>
>
>
>
>
> Thanks,
>
> Ben
>
>
>
>
>
>

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 19.04.2008 09:09:06 von Ben

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 19.04.2008 10:00:42 von Ben

c. At the front-end, MS Access, captured the error code and error message
using variant variables

This is the general overview of the things I did to make it work.

Below are the scripts:

[INSERT TRIGER SCRIPT]
----------------------------------------
CREATE TRIGGER [trTrackInsert]

ON [dbo].[Customers]

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @recordCount int;

DECLARE @userName nvarchar(200);

SET @recordCount = 0;

SET @userName = '';

SET @recordCount = (SELECT count(*) FROM dbo.NewCustTracker);

BEGIN TRANSACTION insertIntoNewCustTracker

IF (@recordCount > 0)

BEGIN

SET @userName = (SELECT UserName FROM dbo.NewCustTracker);

RAISERROR(70000,16,1,@userName) WITH SETERROR;

END

ELSE

BEGIN

INSERT INTO dbo.NewCustTracker(CustNum, UserName)

SELECT [Customer Number], user_name() FROM inserted;

IF @@TranCount > 0

COMMIT TRANSACTION insertIntoNewCustTracker;

END

END

[UPDATE TRIGGER SCRIPT]
--------------------------------------------
CREATE TRIGGER [trUpdateCustomer]

ON [dbo].[Customers]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @custNumber nvarchar(15);

DECLARE @recCount int;

DECLARE @lastName nvarchar(50);

DECLARE @company nvarchar(34);

SET @custNumber = (SELECT [Customer Number] FROM deleted)

SET @recCount = (SELECT count(*) FROM dbo.NewCustTracker WHERE [CustNum] =
@custNumber)

IF (@recCount > 0)

BEGIN

IF (UPDATE([Last Name]) OR UPDATE(Company))

DELETE FROM dbo.NewCustTracker;

END

END

[STORED PROCEDURE SCRIPT]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_InsertNewCustomer]

@NewCustomerID nvarchar(15),

@ErrNum int OUTPUT,

@ErrMsg nvarchar(4000) OUTPUT

AS

BEGIN TRY

INSERT INTO Customers ([Customer Number], Active, [Customer Record Type])

VALUES (@NewCustomerID, 1, 'Both')

END TRY

BEGIN CATCH

SELECT @ErrNum = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE();

END CATCH

[MS ACCESS FRONT-END SCRIPT]
-----------------------------------------------------
....
....
Dim errorNumber As Variant, errorMessage As Variant
Dim errMsgNum As String
....
....
Set com = New ADODB.Command

With com
.ActiveConnection =
.CommandText = "sp_InsertNewCustomer"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@NewCustomerID") = NextCustID
.Execute
End With

errorNumber = 0

If Not IsNull(com.Parameters.Item(2).Value) Then
errorNumber = com.Parameters.Item(2).Value
errMsgNum = Str(errorNumber)
errorMessage = com.Parameters.Item(3).Value

Set com = Nothing

'Check if there is an error returned from the addition of a new
customer...
If errorNumber <> 0 Then
MsgBox "Encountered error: " + errMsgNum + ". " + errorMessage,
_
vbOKOnly, "New Customer Warning!"
End If
End If
....
....
....

As we all know, there are other ways to solve the task even better ways.
But this serves my own purpose.

"Ben" wrote in message
news:0RaMj.951$FF6.571@newssvr29.news.prodigy.net...
> Hi!
>
> I have a trigger created for Customer table. My front-end is access.
> What is the best approach to handle a trigger result when adding a new
> customer record?
>
> Below is the trigger script:
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - -
>
> CREATE TRIGGER dbo.trTrackInsert
>
> ON dbo.Customers
>
> FOR INSERT
>
> AS
>
> BEGIN
>
> -- SET NOCOUNT ON added to prevent extra result sets from
>
> -- interfering with SELECT statements.
>
> SET NOCOUNT ON;
>
> -- Validate the new record.
>
> -- Criteria:
>
> -- 1. Check if there is already a record in the NewCustTracker
>
> -- 1.1. If no record add to the table and record the user info
>
> -- 1.2. If there is record
>
> -- 1.2.1 Inform user that there is pending new record to be completed
>
> -- 1.2.2 Perform roll back of the insert in the Customers table
>
> -- Initialize variables to use in getting some info in the NewCustTracker
> and Customer
>
> -- tables.
>
> DECLARE @recordCount int;
>
> DECLARE @userName nvarchar(200);
>
> SET @recordCount = 0;
>
> SET @userName = '';
>
> -- get the record count in the dbo.NewCustTracker table
>
> SET @recordCount = (SELECT count(*) FROM dbo.NewCustTracker);
>
> BEGIN TRANSACTION insertIntoNewCustTracker
>
> IF (@recordCount > 0)
>
> BEGIN
>
> -- get the info in the NewCustTracker table...
>
> SET @userName = (SELECT UserName FROM dbo.NewCustTracker);
>
> RAISERROR(N'There is a pending new customer record to be completed by %s.
> Please recheck in a couple of minutes.',16,1,@userName);
>
> ROLLBACK TRANSACTION insertIntoNewCustTracker;
>
> END
>
> ELSE
>
> BEGIN
>
> -- record the new customer record in the NewCustTracker table for next
> validation...
>
> INSERT INTO dbo.NewCustTracker(CustNum, UserName)
>
> SELECT [Customer Number], user_name() FROM inserted;
>
> IF @@TranCount > 0
>
> COMMIT TRANSACTION insertIntoNewCustTracker;
>
> END
>
> END
>
> GO
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - -
>
> Any ideas/suggestions are appreciated.
>
>
>
>
>
> Thanks,
>
> Ben
>
>
>
>
>
>

Re: Handle Triggers in MS Access 2003 with SQL Server as Back-End

am 19.04.2008 10:00:42 von Ben