0

I have a form with textboxes. I am inserting what the user enters into the textbox into a table. If the user enters an apostrophe in the textbox labeled "Me.ProjectName", I get an error. My code is:

   CurrentDb.Execute "INSERT INTO Table1(ProjectNumber, Title) " & _
        " VALUES('" & ProjectNumber & "','" & Me.ProjectName & "')"
Thomas G
  • 9,886
  • 7
  • 28
  • 41
Mekenzie Buhr
  • 29
  • 1
  • 8
  • 1
    Possible duplicate of [How to deal with single quote in Word VBA SQL query?](http://stackoverflow.com/questions/3975042/how-to-deal-with-single-quote-in-word-vba-sql-query) – 001 Jul 29 '16 at 12:50
  • 1
    In general, to avoid many errors when concatenating in SQL, you can use the function here: [CSql](http://stackoverflow.com/a/37164607/3527297). – Gustav Jul 29 '16 at 13:51

2 Answers2

4

You should not construct and execute dynamic SQL based on user input. You should use a parameterized query, something like:

Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("", _
        "INSERT INTO Table1 (ProjectNumber, Title) VALUES (@prjnum, @title)")
qdf.Parameters("@prjnum").Value = ProjectNumber
qdf.Parameters("@title").Value = me.ProjectName
qdf.Execute
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you @gord-thompson. I'm having trouble running this when I have a long text (over 255 characters). How do I fix this? – Mekenzie Buhr Jul 29 '16 at 20:25
  • Check the column definition by opening the table in Design View. `Short Text` (sometimes just called `Text`) columns have a maximum length defined, which can be no more than 255 characters. `Long Text` (sometimes called `Memo`) columns are practically unlimited in size. – Gord Thompson Jul 29 '16 at 21:07
  • The table is already set to Long Text in that particular field. I've had this happen before and I've never known out to fix it. Didn't know if you ever ran into this before. – Mekenzie Buhr Jul 30 '16 at 15:41
2

You should escape your strings possibly containing quotes by replacing a quote with 2 quotes:

  Dim SQL As String

  SQL = "INSERT INTO Table1(ProjectNumber, Title) " & _
    " VALUES('" & ProjectNumber & "','" & Replace(Me.ProjectName, "'", "''")  & "')"

  CurrentDb.Execute SQL
Thomas G
  • 9,886
  • 7
  • 28
  • 41
  • Perfect. Thank you! – Mekenzie Buhr Jul 29 '16 at 13:49
  • @MekenzieBuhr - If you decide to use this approach, please take a moment to Google "dynamic SQL" and "parameterized query" to learn why you're still doing it wrong. – Gord Thompson Jul 29 '16 at 13:57
  • @GordThompson I just see your comment now because I had a +1 (6 years!). SQL injection is mostly not a thing in Access. You are doing some code for yourself only, or eventually a tenth of persons that are your close colleagues and certainly not hackers. %Most of the time, the code is not even compiled and everyone as access to it, like all underlying tables. Your remark is valid, but not as important in the context of MS Access – Thomas G Feb 15 '22 at 14:21