SQL Query with apostrophe

Posted on 2012-08-24
Last Modified: 2012-08-24

I have an access database where I insert some records into my table. The records are file names from certain folders. The query works fine (please see the code below).

The only issue is when a file name has an apostrophe in it. For example a file called "Word Documents" would work fine, however a file called "Word Document's" would not work becuase of the apostrophe.

Any help would be great.


                strSQL = "INSERT INTO tblCompanyFiles ([Ticker], [Folder], [FileName], [DocType]) " & _
                            "VALUES ('" & Ticker & "','" & objFolder.Name & "','" & objFile.Name & "','" & DocType & "');"
                DoCmd.RunSQL strSQL   

Open in new window

Question by:mcs26
    LVL 18

    Assisted Solution

    To use concatenation, you'd have to use Replace(objFolder.Name,"'","''"). However, you should consider using parameters, as this leaves you open to SQL injection.
    LVL 8

    Accepted Solution

    just double the quotes...
    for example
    strSQL = "INSERT INTO tblCompanyFiles ([Ticker], [Folder], [FileName], [DocType]) " & _
                                "VALUES ('" & Ticker & "','" & replace(objFolder.Name,"'","''") & "','" & replace(objFile.Name,"'","''") & "','" & DocType & "');"
                    DoCmd.RunSQL strSQL   

    Open in new window


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now