SharePoint 2013 Access App features a new application model.
By using Microsoft SharePoint 2013 to host the front end of the app and
Microsoft SQL Server 2012 as its data storage technology, SharePoint 2013 Access
App has been adopted quickly by power users who has database development
background.
Jeff Conrad from Microsoft mentioned Access 2013 is to change the world one app at a time."]
Since Access App is a database application as we discussed in
previous
blog, it does not support some of the OoB functions. One of the key
functions not supported is the audit history. If users modified or delete an
entry from Access App list (table), there is no audit history. In this blog, I
would like to provide a workaround learned from Microsoft Access App expertise
Jeff Conrad to track the audit history.
I’ve using the similar Access Apps “HarryDevApp2” with two
tables “Employee” and “Rating” as in previous blog. You could
find the database name like “db_af65a2a2_8e70_47d6_994b_207ff0351650”
and the two tables named “Access.
Employee” and “Access.Rating”
are created inside that database. Since employee rating is extremely import and
we like to track the change history. Here is the procedure learned from Jeff
Conrad.
- Create a new table named AuditHistory with old
rating, new rating, and modified time
- Create a “On Update” event and add the
following data macro
After the macro saved, when you change the rating of one record, the change will be recorded into the AuditHistory table. Of cause, you can capture other information to make it more usable.
The design behind “On Update” event is that it creates a trigger on the database side to capture the changed data and insert to AuditHistory table. You can see the trigger in the database table.
The trigger created by the macro on the database side is listed below.
USE [db_af65a2a2_8e70_47d6_994b_207ff0351650]
GO
/****** Object: Trigger
[Access].[_dm_Employee_AFTER_UPDATE]
Script Date: 3/13/2014 1:49:17 PM ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
ALTER TRIGGER
[Access].[_dm_Employee_AFTER_UPDATE]
ON [Access].[Employee]
WITH EXECUTE
AS 'AccessWriter'
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT OFF
DECLARE @_dm_actionRunning NVARCHAR(128)
DECLARE @_dm_macroRunning NVARCHAR(128)
DECLARE @_dm_traceOn BIT = 0
IF AccessSystem.ApplicationPropertiesSelect('DataMacroTracing') IS NOT NULL
SET
@_dm_traceOn =
1
DECLARE @_dm_lastLoggingID INT
IF @_dm_traceOn > 0
SELECT
@_dm_lastLoggingID
= COALESCE(Max(ID), 0)
FROM
[Access].[Trace]
DECLARE @_dm_savePoint as VARCHAR(36)
SET @_dm_savePoint = CONVERT(VARCHAR(36), NEWID())
DECLARE @_dm_initialTranCount INT
SET @_dm_initialTranCount =
@@TRANCOUNT
IF @_dm_initialTranCount >
0
SAVE
TRAN @_dm_savePoint
ELSE
BEGIN
TRAN
IF OBJECT_ID('tempdb..#Context') IS NOT NULL
BEGIN
DECLARE
@_dm_isTopLevel BIT
SELECT
@_dm_isTopLevel =
isTopLevel
FROM
#Context
WHERE
ID = 1
IF
@_dm_isTopLevel =
1
BEGIN
UPDATE
#Context
SET
isTopLevel = 0
WHERE
ID = 1
SET
@_dm_initialTranCount =
0
END
END
SET @_dm_macroRunning = N'Employee:On Update'
BEGIN TRY
DECLARE
@varRatingOld Int
DECLARE
@varRatingNew Int
DECLARE
@_dm_cancelRecordChange Bit
DECLARE
@_dm_temp1 Int
DECLARE
_dm_cur1 CURSOR
LOCAL STATIC FOR
SELECT
[Rating]
FROM
[DELETED]
OPEN
_dm_cur1
DECLARE
@_dm_temp2 Int
DECLARE
_dm_cur2 CURSOR
LOCAL STATIC FOR
SELECT
[Rating]
FROM
[INSERTED]
OPEN
_dm_cur2
FETCH
NEXT FROM
_dm_cur1
INTO
@_dm_temp1
FETCH
NEXT FROM
_dm_cur2
INTO
@_dm_temp2
WHILE
@@FETCH_STATUS =
0
BEGIN
/*
Get the old and new values of the rating and place them in variables. */
SET
@_dm_actionRunning =
N'SetLocalVar'
SET
@varRatingOld =
@_dm_temp1
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetLocalVar', N'varRatingOld', @varRatingOld, N'', N''
SET
@_dm_actionRunning =
N'SetLocalVar'
SET
@varRatingNew =
@_dm_temp2
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetLocalVar', N'varRatingNew', @varRatingNew, N'', N''
/*
Create a record in the AuditHistory table and pass in the local variables to
the appropriate fields. */
DECLARE
@_dm_temp3 NVarChar(220)
DECLARE
@_dm_temp4 Decimal(28,6)
DECLARE
@_dm_temp5 Decimal(28,6)
DECLARE
@_dm_temp6 Date
SET
@_dm_temp4 =
0.0
SET
@_dm_temp5 =
0.0
SET
@_dm_cancelRecordChange = 0
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'CreateRecord', N'', N'', N'', N''
SET
@_dm_actionRunning =
N'SetField'
SET
@_dm_temp3 = @_dm_temp3
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.Employee', @_dm_temp3, N'', N''
SET
@_dm_actionRunning =
N'SetField'
SET
@_dm_temp4 = @varRatingOld
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.OldRating', @_dm_temp4, N'', N''
SET
@_dm_actionRunning =
N'SetField'
SET
@_dm_temp5 = @varRatingNew
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.NewRating', @_dm_temp5, N'', N''
SET
@_dm_actionRunning =
N'SetField'
SET
@_dm_temp6 = AccessSystem.NOW()
IF
@_dm_traceOn >
0
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.LastEditedTime', @_dm_temp6, N'', N''
SET
@_dm_actionRunning =
N'CreateRecord'
IF
@_dm_cancelRecordChange = 0
BEGIN
INSERT INTO [AuditHistory] (
[Employee],
[OldRating],
[NewRating],
[LastEditedTime])
VALUES (
@_dm_temp3,
@_dm_temp4,
@_dm_temp5,
@_dm_temp6)
END
FETCH
NEXT FROM
_dm_cur1
INTO
@_dm_temp1
FETCH
NEXT FROM
_dm_cur2
INTO
@_dm_temp2
END
CLOSE
_dm_cur1
DEALLOCATE
_dm_cur1
CLOSE
_dm_cur2
DEALLOCATE
_dm_cur2
END TRY
BEGIN CATCH
DECLARE
@_dm_sqlException NVARCHAR(128) = N'SQL exception'
DECLARE
@_dm_errorNumber INT
= ERROR_NUMBER()
DECLARE
@_dm_errorMessage NVARCHAR(4000) = ERROR_MESSAGE()
IF
@_dm_TraceOn >
0
BEGIN
/*
Before rollback happens, cache the tracing info so that we can refill later */
DECLARE
@tracingCache TABLE(
[ID] INT IDENTITY PRIMARY KEY,
[MacroName] nvarchar(128),
[ActionName] nvarchar(128),
[Operand] nvarchar(4000),
[Output] nvarchar(max),
[TargetRow] nvarchar(4000),
[Timestamp] datetime2(3),
[RuntimeErrorMessage] nvarchar(4000))
INSERT
INTO @tracingCache
SELECT
[MacroName],
[ActionName],
[Operand],
[Output],
[TargetRow],
[Timestamp],
[RuntimeErrorMessage]
FROM
[Access].[Trace]
WHERE
[ID] > @_dm_lastLoggingID
END;
IF
@_dm_initialTranCount >
0
BEGIN
IF
XACT_STATE()
<> -1
ROLLBACK TRAN @_dm_savepoint
END
ELSE
ROLLBACK
TRAN
IF
@_dm_TraceOn >
0 AND XACT_STATE() <> -1
BEGIN
/*
After rollback happens, refill the Tracing table with the cached info */
INSERT
INTO [Access].[Trace](
[MacroName],
[ActionName],
[Operand],
[Output],
[TargetRow],
[Timestamp],
[RuntimeErrorMessage])
SELECT
[MacroName],
[ActionName],
[Operand],
[Output],
[TargetRow],
[Timestamp],
[RuntimeErrorMessage]
FROM
@tracingCache
/*
By default, RAISERROR raises an error message with an error number of 50000.
If the
caught error's ID is 50000, then it is a user raised error
Else the
caught error is a SQL exception (the Error ID won't be 50000 for SQL exception)
*/
IF
@_dm_errorNumber <>
50000
/* Log the current running macro/action with the SQL
exception error */
EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, @_dm_actionRunning, '', '', '', @_dm_sqlException;
END;
THROW;
END CATCH
END
Now you understand the Access App is just a database UI and you can use all your database development skills to empower your access apps.
