0

I have created a form in Access 2010 that is used to insert data into an existing table. The table contains a Keywords field, Source combo box, and a Code text box where i write the data to be inserted and there is a button for executing the query. The code for the form is:

Private Sub cmd_go_Click()
Dim insertstring As String
insertstring = "INSERT INTO KWTable (KW, Source, Code) VALUES('" & text_key.Value & "','" & combo_source.Value & "','" & txt_code.Value & "');"
DoCmd.RunSQL insertstring
End Sub

The code is simple, it inputs the data to the table so i can reference it for future use. Now the problem I am having is that when I try to add long bits of code that I use in SQL Server i get a syntax missing expression error which I am assuming is coming from the single quotes since the code is from SQL. I am getting the error because when i am trying to store a code i used in SQL Server it uses single quotes which access does not recognise. I think if I try to write in the code for the insert form something to help convert the single quotes into double quotes, then reconvert them back to single quoteswill help solve the problem. I just cant figure out how to do it and could really use some help.

Thank You

user2119980
  • 499
  • 4
  • 12
  • 25
  • Can you please post the error message? Also (a dumb but possible and frequent situation) `debug.print` your SQL statement: a missing space can make a lot of fuss – Barranka Apr 02 '13 at 19:12
  • I'm really confused; my gut instinct is to say "escape your apostrophes by replacing a single apostrophe with two apostrophes", but I'm not exactly sure what `Sub JoinCells` or your second query has to do with your problem. I'm not really sure what your overall question is. – LittleBobbyTables - Au Revoir Apr 02 '13 at 19:14
  • The codes functions dont relate to my issue, the issue is getting writing in the main code for the inserting purpose to replace single quotes with double quotes then re replaceing them back with single quotes once inserted into access. The error message is RUN-TIME ERROR '3075' SYNTAX ERROR (MISSING OPERATOR) IN QUERY EXPRESSION ..... – user2119980 Apr 02 '13 at 19:17
  • possible duplicate of [Escaping ' in Access SQL](http://stackoverflow.com/questions/199889/escaping-in-access-sql) – LittleBobbyTables - Au Revoir Apr 02 '13 at 19:27

2 Answers2

3

You can avoid trouble with included quotes in your inserted text by using a parameter query.

Consider an approach such as this for cmd_go_Click().

Dim strInsert As String
Dim db As DAO.database
Dim qdf As DAO.QueryDef

strInsert = "PARAMETERS pKW TEXT(255), pSource TEXT(255), pCode TEXT(255);" & vbCrLf & _
"INSERT INTO KWTable (KW, Source, Code) VALUES (pKW, pSource, pCode);"
'Debug.Print strInsert
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strInsert)
qdf.Parameters("pKW") = Me.text_key.value
qdf.Parameters("pSource") = Me.combo_source.value
qdf.Parameters("pCode") = Me.txt_code.value
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing

However, I don't understand how JoinCells() fits in.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • That bit of code did not work, and the join cells code is just an example. The problem I am having is with the quotes – user2119980 Apr 02 '13 at 19:40
  • What was the error message when it failed? Which line is highlighted in debug mode? – HansUp Apr 02 '13 at 19:41
  • There is no `DoCmd.RunSQL insertstring` in the code I offered. – HansUp Apr 02 '13 at 19:48
  • Oh in your code when I tried to run it nothing happened at all, the code did not execute and no error message popped up. Nothing happened – user2119980 Apr 02 '13 at 19:49
  • Make sure your form's code module includes `Option Explicit` in its Declarations section. Then run Debug->Compile from the VB Editor's main menu. What compile errors does it complain about? – HansUp Apr 02 '13 at 19:51
  • Does your form include a control named `txt_code`? If so, what is it? Does it have a `.Value` property? – HansUp Apr 02 '13 at 20:00
0

I use a function that handles Null Values, and escapes single quotes (by converting them to two single quotes) when creating SQL statements directly:

Function SafeSQL(ByVal pvarSQL As Variant) As String
    SafeSQL2 = Replace(Nz(pvarSQL, ""), "'", "''")
End Function

Then in your routine you would have:

insertstring = "INSERT INTO KWTable (KW, Source, Code) VALUES('" & SafeSQL(text_key.Value) & "','" & SafeSQL(combo_source.Value) & "','" & SafeSQL(txt_code.Value) & "');"
Dan Metheus
  • 1,418
  • 9
  • 16