Error logging

[Carl Witte]

Hi. I've built a shared table to track errors from all users but I'm running into a problem. I populate this table with an SQL command. Many, maybe even most, errors have protected characters such as the apostrophe (') or a quote (") in them. To get arround this if there is a protected character it isn't logging the error description, just the number.

    
DoCmd.RunSQL "INSERT INTO ErrLog ( [User], ErrorNum, AddDesc, ErrorDesc, [Module], Function, ErrSection, VNum, VTime ) " & _
        "SELECT '" & VBA.Environ$("USERNAME") & "' AS E1, " & ErrNum & " AS E2, '" & user_string & "' AS E3, '" & _
        err_String & "' AS E4, '" & Module & "' AS E5, '" & Proceedure & "' AS E6, " & errSection & " AS E7, " & vRS![VNum] & " as E8, " & Now() & " as E9;"

Really I'm only having problems with the "err_string ", the text description of the error.
I'd really like to keep the description if at all possible. Some of them are really handy to fixing problems.

Any good ways arround this?

If I use ADODB won't I run into the same set of problems?

Sorry my question isn't better formed. I really don't know a better way to say it though.

Carl

[ajalwaysus]

OK, I looked this over twice, am I missing anything? What is your error?

-AJ

[ChipR]

I can tell you that less than 1% of access errors have quote characters in them. You could always replace the quote characters in the string and then surround it with quotes.

"""" & Replace(Err.Description,"""", "'") & """"

[Carl Witte]

Thank you ChipR. I'll try out the replace function. I must just only throw the errors with quotes and apostrophes. Clearly a problem with my coding that I keep throwing the same types of errors.

In the last week more than 8 out of 10 errors I've thrown are balked by the SQL system.
I'll have to use

err_string = replace(err_string, chr(34), "<DQ>") ' fixes "
err_string = replace(err_string, chr(39), "<SQ>") ' fixes '

right?

[NeoPa]

Carl,

Looking at your (admirably formatted) SQL string which uses the correct, standard, quote characters (See Quotes (') and Double-Quotes (") - Where and When to use them), it seems all you need is for any variable that may have quote (') characters in to use

Replace(Var, "'", "''")

Your quotes around the variables are already perfect.
EG.

DoCmd.RunSQL "INSERT INTO ErrLog ( [User], ErrorNum, AddDesc, ErrorDesc, [Module], Function, ErrSection, VNum, VTime ) " & _
        "SELECT '" & VBA.Environ$("USERNAME") & "' AS E1, " & ErrNum & " AS E2, '" & user_string & "' AS E3, '" & _
        Replace(err_String,"'","''") & "' AS E4, '" & Module & "' AS E5, '" & Proceedure & "' AS E6," & _
        errSection & " AS E7, " & vRS![VNum] & " as E8, " & Now() & " as E9;"

[Carl Witte]

Thanks NeoPa. I put them in separate lines for personal clarity. That SQL statement is getting a little rough on a reader.

Carl

[NeoPa]

Ah. It's so nice when people understand such things. You should see some of the SQL strings we see posted here. Completely unintelligible without a reformat.

Anyway, I'm pleased that helped :)