INSERT Query problem with Quotes & Apostrophes

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Pete (Pete_at_discussions.microsoft.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 04:45:05 -0700

We have an application with an audit table to keep track of who changed what
and when. This is updated using the following code:
    Dim cmd1 As ADODB.Command
    Set cmd1 = New ADODB.Command
    With cmd1
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, " & Chr(34) &
RecordChanged & Chr(34) & ")"
        .CommandType = adCmdText
        .Execute
    End With
    Set cmd1 = Nothing

The Chr(34)s were added as I ran into problem if the value of the
RecordChanged string contained an apostrophe (').

I now have a problem as a user wants to write a record containg quotes (")
and an apostrophe ('). I have written a function that strips these characters
out of the string, but ideally I would like to audit exactly what the user
has entered. I have tried playing around with adding Chr(39) into the above
CommandText but not had any luck. Help please! 

-- 
Peter Schmidt
Ross-on-Wye, UK