Avatar of CipherIS
CipherIS
Flag for United States of America asked on

MS Access 2007 Insert data with Apostrophe

I am developing an MS Access application and inserting data using VBA into a table.  When I have an apostrophe I receive an error.  

I know in VB6 I had to write a routine to resolve that but I do not remember the code.

Any idea how to write a Function that would allow you to pass in the text field and process it then return the corrected result?

Thanks
Microsoft AccessVB ScriptVisual Basic Classic

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Joe Howard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

Actually, using the previous method, it removes the single quote, which is one way to handle it, but generally not the best.

The best way to do it in a SQL string is to use:

strSQL = 'INSERT INTO yourTable (NumericField1, TextField2, DateField3) ' _
            & 'Values (' & me.txt_NumericField & ', ' _
                                 & REPLACE(me.txt_TextField2, chr$(39), chr$(39) & chr$(39)) & ', ' _
                                 & '#' & me.txt_DateField & '#)'

Note that I replaced the single quotes with a pair of single quotes.  Access (and most RDBMSs) will interpret this as a single quote.

The other way to do this is to simply open the recordset, use the AddNew method and write the values of the fields using text like:

     rs.AddNew
     rs!Field1 = me.txt_Field1
     rs!Field2 = me.txt_Field2
     rs!Field3 = me.txt_Field3
     rs.Update

When you write the values like this, they you don't need to wrap them in single quotes so there is no conflict with imbedded single quotes.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck