Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag 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?

Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!Field1 = me.txt_Field1
     rs!Field2 = me.txt_Field2
     rs!Field3 = me.txt_Field3

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.