We help IT Professionals succeed at work.
Get Started

Access VBA Insert SQL Database Logging Description Error

509 Views
Last Modified: 2016-02-11
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
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE