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

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.