Change Trigger - Log all data changes in a table

I needed a MSSQL trigger to store alterations to data in a table called tblContacts. All changes (i.e. the before & after values) needed to be stored in a table that logs all changes from various tables.

tblAudit Structure

irpEditRecordID (int)
dwoEditDate (datetime)
swoUser (nvarchar)
cwoAction (char)
swoRecordID (nvarchar)
swoSourceTable (nvarchar)
swoSourceField (nvarchar)
swoBeforeValue (sql_variant)
swoAfterValue (sql_variant)

This is a bit quick and nasty, and probably a bit slow if you've got a table that really gets hammered but it suits my needs. The trigger below will spin through all available fields looking for changes and log them in tblAudit. The payoff is I don't need to remember to update the trigger if I add new fields!

tblContacts Update Trigger

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- Change the trigger name below to reflect your table name
CREATE TRIGGER trg_tblContacts_Update

-- Change the table name below to reflect the table you want to audit
ON dbo.tblContacts

FOR UPDATE

AS

DECLARE @sField    VARCHAR(255)
DECLARE @sTable    VARCHAR(255)
DECLARE @sPrimaryKey  VARCHAR(255)
DECLARE @sSQL    VARCHAR(3000)

-- SETTINGS --
-- Change the table name below to reflect the table you want to audit
SET @sTable = 'tblContacts'
-- Change the primary key below to reflect the primary key field for table you want to audit
SET @sPrimaryKey = 'irpContact'

-- INIT --
SET @sSQL = ''

SELECT  * INTO #temp_inserted FROM inserted;
SELECT  * INTO #temp_deleted FROM deleted;


DECLARE fields_cursor CURSOR FOR 
 SELECT column_name FROM information_schema.columns
  WHERE table_name = @sTable
 ORDER BY ordinal_position
OPEN fields_cursor

-- CHECK CHANGES TO DATA IN EACH FIELD --

FETCH NEXT FROM fields_cursor INTO @sField

WHILE @@FETCH_STATUS = 0
BEGIN

 -- Find and log changes in tblAudit
 SET @sSQL = ' INSERT INTO tblAudit  (dwoEditDate,swouser,cwoAction,swoRecordID,swoSourceTable,swoSourceField, swoBeforeValue,swoAfterValue) ( select GETUTCDATE() as calc_datestamp, system_user as calc_username, ''A'' as calc_status, #temp_inserted.' + @sPrimaryKey + ', ''' + @sPrimaryKey + ''' as calc_TableName, ''' + @sField + ''' as calc_fieldname,  #temp_deleted.' + @sField + ', #temp_inserted.' + @sField + ' from #temp_inserted , #temp_deleted  where (#temp_deleted.' + @sPrimaryKey + ' = #temp_inserted.' + @sPrimaryKey + ') AND (#temp_inserted.' + @sField + ' != #temp_deleted.' + @sField + ' ) ) '
 
--PRINT @sSQL
 EXEC(@sSQL)

 FETCH NEXT FROM fields_cursor INTO @sField
END

CLOSE fields_cursor
DEALLOCATE fields_cursor



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Reports in Forms

You learn something new every day! I had no idea that in MS Access 2010 you could embed Reports inside Forms (new feature definitely not available in Access 2007).
See: http://social.technet.microsoft.com/Forums/en-US/office2010/thread/78dd8a46-1281-47b7-84e8-125c56e13b2a#6aaa6cf0-af99-49e7-96ee-3ab12a043b7b

That might come in very handy with displaying a list html formatted notes in a form.

Now to have a play...

Displaying a 6 digit number as 4 text characters

Recently I had to link up 2 systems, one financial off the shelf package and a bespoke Access database used for contact management.
The contact management data was going to create and maintain customer accounts in the financial package. The problem was in the access DB each contact had a unique number and in the financial package we needed a reference where the first 2 letters of the customers surname were followed with up to 4 more characters for a unique reference.
Tricky.
In the end I put together this short bit of code that allows you to convert a number to any base. All you have to do is supply the digits to use.
In my case I've used base 33 to pack the id numbers into a shorter format using numbers and letters.

Const vbB2Digits = "01" 'base 2 - Binary
Const vbB8Digits = "01234567" 'base 8 - Octal
Const vbB10Digits = "0123456789" 'base 10 - Decimal
Const vbB10Digits = "0123456789XE" 'base 12 - Dozenal. See http://www.dozenal.org
Const vbB16Digits = "0123456789ABCDEF" 'base 16 - Hexedecimal
Const vbB33Digits = "0123456789ABCDEFGHJKMNPQRSTUVWXYZ" 'base 33 - non similar characters (I,L,O removed) from 0-9 & Alphabet


Public Function sEncodeNumber(lNumber As Long, Optional sDigitList As String = vbB10Digits) As String
Dim sOutputString As String
Dim lTmpNumber As Long
Dim lRangeSize As Long
Dim lTmpPower As Long
Dim lTmpLastPower As Long

    OutputString = ""
    lTmpNumber = lNumber
    lRangeSize = Len(sDigitList)
    lTmpPower = 1

    ' Work with postive numbers
    If lTmpNumber < 0 Then
        lTmpNumber = lTmpNumber * -1
    End If

    While lTmpNumber > 0
        lTmpLastPower = lTmpPower
        lTmpPower = lTmpPower * lRangeSize
        sOutputString = Mid(sDigitList, ((lTmpNumber Mod lTmpPower) / lTmpLastPower) + 1, 1) & sOutputString
        lTmpNumber = lTmpNumber - (lTmpNumber Mod lTmpPower)
    Wend

    ' insert the -ve sign if the orginal number was negative
    If lNumber < 0 Then
        sOutputString = "-" & sOutputString
    End If

    sEncodeNumber = sOutputString
End Function

For those maths geeks out there, if you haven't come across dozenal before, have a quick gander at Alex Bellos' blog entires on the subject.

Roll out WPKG Client using AD Group Policy

Using Microsoft's Orca tool to create an MST to work alongside the WPKGSetup.msi is a complete faff especially considering the end goal is rolling out a system that

will handle package management and rollout. Instead why not leverage WPKG to install it's own client?
The major advantage of doing this is that it allows you to upgrade and change WPKG Client parameters in the future using WPKG itself.

So this is how you do it:

First create a WPKG-Client package like this:

<package
   id="gplwpkgclient"
   name="WPKG Client 1.3.9"
   revision="2011.11.05.00"
   reboot="false"
   priority="99999">
 
 <!-- because the client hasn't been installed yet we cannot use the SOFTWARE parameter for paths -->
 <variable name="PKG_PATH" value="\\myserver\myshare\software\Components\gpl.wpkg.client.1.3.9" />
 
 <check type="uninstall" condition="exists" path="WPKG" />
 <check type="file" condition="versionequalto" path="%PROGRAMFILES%\wpkg\wpkginst.exe" value="1.0.0.18" />
 
 
   <install cmd='msiexec /i "%PKG_PATH%\WPKG Client 1.3.9-x32.msi" /qn SETTINGSFILE="%PKG_PATH%\settings.xml"' />
 
   <upgrade cmd='msiexec /i "%PKG_PATH%\WPKG Client 1.3.9-x32.msi" /qn SETTINGSFILE="%PKG_PATH%\settings.xml"' />
 
   <remove cmd='MsiExec /x{08DF8731-5B69-4709-979A-CC08E49D7686} /qn' />
 
</package>

Then in Active Directory, create a new Group Policy object with the following setting:
Computer Configuration → Windows Settings → Scripts(startup/shutdown) → Startup
Script: \\myserver\myshare\wpkg.js
Parameters: /install:gplwpkgclient /quiet

Job done!

The startup script will get WPKG to check if the latest version of the client has been installed every time the PC boots up but will only go through the installation process if no version exists or the current version installed does not match the revision number on the local PC's xml file.

And yes before you ask, I've added this to the wiki at www.wpkg.org.

MSSQL Calls through ODBC in MS Access

As MS Access no longer supports .adp projects any Access databases created have to link to MS-SQL back ends through ODBC.
However, sometimes you still need to call Stored Procedures, Views or create dynamic query statements to be passed directly to MS-SQL.

You can still achieve this by creating a passthru query in Access.

Below is a little bit of code I put together to allow you to run SQL statements using a passthru query called "SQLCall" or choosing one of your own.

To set things up, you'll need to create a passthru query in Access (see http://support.microsoft.com/kb/303968 for how to do this) called SQLCall and then drop this code into a module:

Public Sub SQLExec(ByVal sSQL As String _
 , Optional bRetRecords As Boolean = False _
 , Optional bExecute As Boolean = True _
 , Optional sQueryName As String = "SQLCall" _
 , Optional vConnect As Variant)
 
' vConnect   : Optional connection string
' bRetRecords   : Optional set return records on / off. Default: on
' sQueryName : Optional set alternate location for query storage

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sConnect As String

 Set db = CurrentDb
 Set qdf = db.QueryDefs(sQueryName)
 If IsMissing(vConnect) Then
  sConnect = qdf.Connect
 Else
  sConnect = CStr(vConnect)
 End If
 
 qdf.SQL = sSQL
 qdf.Connect = sConnect
 qdf.ReturnsRecords = bRetRecords
 If bExecute Then qdf.Execute

 GoTo ErrorExit
ErrorHandler:
 Select Case Err.Number
 Case Else
  MsgBox cErrorIntro & vbCrLf _
  & vbCrLf & Err.Number _
  & vbCrLf & Err.Description _
  & vbCrLf & Err.Source _
  , vbCritical + vbOKOnly, "Error"
 End Select
 Resume Next
ErrorExit:
End Sub

You can use this Sub to do thinks like:

Call the untag all contacts stored procedure

SQLExec "EXEC spUntagAllContacts;", False, True

Delete Contact #5

SQLExec "DELETE FROM tblContacts Where ContactID = 5;"

Call the SP to tag a contact based on ContactID

SQLExec "EXEC spTagContact " & Nz(ContactID, 0) & ";"

Do a select query and make the results accessible from the SQLContactList passthru

SQLExec "SELECT * from tblContacts WHERE ContactID < 100;", True, False, "SQLContactList"

Call the stored procedure to get the current users security groups and make the results accessible from the SQLSecurityGroupList passthru

SQLExec "EXEC spListMySecurityGroups '" & UserName & "';", True, False, "SQLSecurityGroupList"

Viewing a Nested Set Model tree as an Adjacency List

Recently, I needed to view a Nested Set Model tree as an Adjacency list so I could use it with VB's TreeCtrl.

Here's the MS-SQL view I came up with:

SELECT   T1.irpCategory, T1.iwmleft, T1.iwmright, T1.swmCategoryName, T1.ircDepth, T2.ircParentID
FROM     (SELECT   TOP 100 PERCENT node.irpCategory, node.iwmleft, node.iwmright, node.swmCategoryName, (COUNT(parent.swmCategoryName) - 1) 
                       AS ircDepth
            FROM     dbo.tblCategories AS node, dbo.tblCategories AS parent
            WHERE   node.iwmleft BETWEEN parent.iwmleft AND parent.iwmright
            GROUP BY node.irpCategory, node.swmCategoryName, node.iwmleft, node.iwmright
            ORDER BY node.iwmleft) T1 LEFT OUTER JOIN
             (SELECT   TOP 100 PERCENT irpCategory,
                            (SELECT   TOP 1 irpCategory
                             FROM     dbo.tblCategories t2
                             WHERE   t2.iwmleft < t1.iwmleft AND t2.iwmright > t1.iwmright
                             ORDER BY t2.iwmright - t1.iwmright ASC) AS ircParentID
              FROM     dbo.tblCategories t1
              ORDER BY iwmright - iwmleft DESC) T2 ON T1.irpCategory = T2.irpCategory

And the VB for the TreeCtrl object called tvCategories:

Private Sub Refresh_tvCategories()
On Error GoTo Err_Handler
Dim vNodeSet As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim bFirstItem As Boolean
Dim sFirstKey As String
    Set db = CurrentDb
    'SQLCategoriesList is a odbc passthru query to the view on the MSSQL server
    Set rs = db.OpenRecordset("SELECT * FROM SQLCategoriesList")
    bFirstItem = True
    
    
    If Not rs.EOF Then
        rs.MoveFirst
        While Not rs.EOF
                        

            If IsNull(rs.Fields("ircParentID")) Then
                Set vNodeSet = tvCategories.Nodes.Add()
                vNodeSet.Key = "K" & rs.Fields("irpCategory")
                vNodeSet.Expanded = False
                vNodeSet.Text = rs.Fields("swmCategoryName")
            Else
                'Tree Controls need text for their IDs so I'm prefixing the ID numbers with the letter K
                Set vNodeSet = tvCategories.Nodes.Add("K" & rs.Fields("ircParentID"), tvwChild)
                vNodeSet.Key = "K" & rs.Fields("irpCategory")
                vNodeSet.Expanded = False
                vNodeSet.Text = rs.Fields("swmCategoryName")
            End If
            If bFirstItem Then
                sFirstKey = vNodeSet.Key
                bFirstItem = False
            End If
            rs.MoveNext
        Wend
    End If
    
    'for some reason the form doesnt work properly unless an item has been selected at some point
    tvCategories.Nodes(sFirstKey).Selected = True
    'it can be unslected straight away
    
    
    
Exit_Err_Handler:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
       Case vbObjectError + 1
          Resume Next
       Case Else
          msgbox Err.Number & Err.Description
          Resume Exit_Err_Handler
    End Select

End Sub

RTFM! Let me google that for you

G. just showed me the LMGTFY site.

using links like this, you can remind people to try searching before asking obvious questions:
http://lmgtfy.com/?q=Search Terms

e.g.
http://lmgtfy.com/?q=how long is a piece of string
http://lmgtfy.com/?q=8 feet in meters