Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Problem using Insert because of an apostrophe

    Hi guys,

    I'm using Application.filesearch to find a number of files within a folder and then dividing up the foundfiles(i) to get the title and location.

    I'm then importing this into a table using:

    DoCmd.RunSQL "INSERT INTO Tbl_TempFile (Import, FileTitle, FileLocation, DateAdded) Values (1, '" & FileTitle & "','" & FileLocation & "', now());"

    FileLocation and FileTitle are Dim As Strings

    I'm getting an error and what I assume is that the problem is because some files and folders have an apostrophe in use, for example there is a folder called "A hard day's night". If I exclude this folder from my search it works fine.

    Does anyone know how I can ammend my code to correctly grab the file name and location? I assume that its jus

    NB: Its not possible to change the name of the folders.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    The simple fix is to escape all the quotes.

    The (possibly) better fix would be to use your Connection object to set up a SQL statement with placeholders. It would be well worth your time to dig through the documentation. An easy place to start is by hitting F2 to bring up the object browser, locating Connection and hitting F1.

    I forget how Access escapes quotes... in SQL proper it's just by doubling them up, i.e.:

    String with 'embedded' quotes

    becomes

    'String with ''embedded'' quotes'

    Access might be requiring double quotes and the use of CHR$(), i.e.:

    String with "embedded" quotes

    becomes

    "String with " & Chr$(34) & "embedded" & Chr$(34) & " quotes"

    It's uglier, but it's still just a matter of search and replace.

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Access will accept the doubled quotes, but it's often easier to decipher and more robust if you use the "uglier" approach.

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Nope, I'm sorry, I don't understand.

    I actually don't understand either VB or SQL and am really just picking things up as I go along.

    How would I re-write the following so that the Dim Values:

    DoCmd.RunSQL "INSERT INTO Tbl_TempFile (Import, FileTitle, FileLocation, DateAdded) Values (1, '" & FileTitle & "','" & FileLocation & "', now());"

    Sorry, I'll keep trying this but any help is appreciated

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    The last part
    ...Values (1, """ & FileTitle & """, """ & FileLocation & """, now());"
    or
    ...Values (1, '" & replace(FileTitle,"'", "''") & "','" & replace(FileLocation,"'","''") & "', now());"

    Last one is replacing occurances of singlequotes with two of them, which makes Jet accept them as one.
    Roy-Vidar

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Thank you so much. This works perfectly. (I used the second code).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •