Access VBA Insert SQL Database Logging Description Error

Hello All,

This one has me stumped so I would really appreciate some help.

So, I created some error logging capacity inside my application (MS Access 2013 Split DB 32-Bit) . In doing so, I created generic Error Handler procedures to track the errors with the help of MZ Tools.

The error logging works absolutely fine UNTIL, the error involves the application not being able to locate a referenced control in a form.

For example:  'Error: 2450 (System cannot find the referenced form 'frm_entity'.) in procedure xyz...'

When this occurs, the string that is built in the Error Handler for my "Log Description" field contains single quotation characters that Access just does not seem to like.

Following is an outline of everything in code to the actual error and simple mitigation.

My table design:


Sample of code used inside my generic Error Handlers which is the same approach across the entire database.

On Error GoTo populate_entity_wizard_Err_Handler

Err_Handler_Exit:
    Exit Function

populate_entity_wizard_Err_Handler:
    If Err.Number = 0 Then
       Resume Err_Handler_Exit
    Else
       Call addDBLog(6, "Error: " & Err.Number & " (" & Err.Description & ") in procedure populate_entity_wizard of Module bas_entity")
       MsgBox "An unexpected application error has been detected." & vbCrLf & "" & vbCrLf & _
       "Error: " & Err.Number & " (" & Err.Description & ") in procedure populate_entity_wizard of Module bas_entity." & vbCrLf & "" & vbCrLf & _
       "Please note the above details before raising a support ticket.", vbOKOnly, "Support"
       Resume Err_Handler_Exit
    End If

Select allOpen in new window

Code to write the actual database log:

Function addDBLog(uLogAuditCode As Integer, uLogDescription As String)
'---------------------------------------------------------------------------------------
'Disclaimers
'---------------------------------------------------------------------------------------
'Log audit code definition
'("1") = RECORD INSERT
'("2") = RECORD DELETE
'("3") = RECORD UPDATE
'("4") = REPORT PUBLISHED
'("5") = GENERAL
'("6") = APPLICATION ERROR
    
    'declaring connection ->
    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.AccessConnection
    Dim strSQL As String
    Dim dbFailOnError, dbSeeChanges
    
    'declaring variables ->
    Dim vLogUserID As Integer, vLogUserName As String, _
        vLogDate As Date
    
    'collecting variables ->
    vLogUserID = TempVars!APPUserID
    vLogUserName = TempVars!APPUser
    vLogDate = Now()
        
    'building SQL string for table record insert ->
    strSQL = "INSERT INTO tbl_logs (LogUserID, LogUserName, LogCode, LogDescription, LogDate, LogDateTimeStamp) " & _
    "SELECT " & vLogUserID & " as LogUserID, '" & vLogUserName & "' AS LogUserName, " & uLogAuditCode & " AS LogCode, '" & uLogDescription & "' AS LogDescription, " & _
    "'" & vLogDate & "' AS LogDate, '" & vLogDate & "' AS LogDateTimeStamp"
    Debug.Print strSQL
    Conn.Execute strSQL, dbFailOnError

End Function

Select allOpen in new window

The following SQL is a debug of the above procedure which has been slightly modified to retain privacy. This is an example of the string that causes the error:

INSERT INTO tbl_logs (LogUserID, LogUserName, LogCode, LogDescription, LogDate, LogDateTimeStamp) SELECT 1 as LogUserID, 'Test User' AS LogUserName, 6 AS LogCode, 'Error: 2450 (System cannot find the referenced form 'frm_entity'.) in procedure populate_entity_wizard of Module bas_entity' AS LogDescription, '25/06/2015 1:03:31 PM' AS LogDate, '25/06/2015 1:03:31 PM' AS LogDateTimeStamp

Select allOpen in new window

Below is the same debug as above but with the quotations taken out (i.e.  'frm_entity' becomes frm_entity) and it works fine:

INSERT INTO tbl_logs ( LogUserID, LogUserName, LogCode, LogDescription, LogDate, LogDateTimeStamp )
SELECT 1 AS LogUserID, 'Test User' AS LogUserName, 6 AS LogCode, 'Error: 2450 (System cannot find the referenced form frm_entity.) in procedure populate_entity_wizard of Module bas_entity' AS LogDescription, '25/06/2015 1:03:31 PM' AS LogDate, '25/06/2015 1:03:31 PM' AS LogDateTimeStamp

Select allOpen in new window

In case you have missed, it might also be worth noting that I only ever parse two variables when calling the log procedure. These are:


Everything else is collected from TempVars or assigned on the fly.

Has anyone got any ideas on how I can get this type of log description written to a table without the errors occurring or taking another approach without changing to much of my code?

Thanks. Lai