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:

tbl-logs.jpg
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

Open 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

Open 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

Open in new window


Error.jpg
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

Open in new window


Without-Quotes.jpg
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:

LogCode
LogDescription

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
Laila JacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try double the single quote in your description error, like change:

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"
   
to:

strSQL = "INSERT INTO tbl_logs (LogUserID, LogUserName, LogCode, LogDescription, LogDate, LogDateTimeStamp) " & _
    "SELECT " & vLogUserID & " as LogUserID, '" & vLogUserName & "' AS LogUserName, " & replace(uLogAuditCode,"'","''") & " AS LogCode, '" & Replace(uLogDescription,"'","''") & "' AS LogDescription, " & _
    "'" & vLogDate & "' AS LogDate, '" & vLogDate & "' AS LogDateTimeStamp"
   

by double the single quote in your scripts, this will avoid the error when the value contains single quotes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laila JacksonAuthor Commented:
Thanks Ryan. You rock!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.