Special Character in INSERT INTO query

[AngelsGuardian]

Ok here is the deal

if my line is like :

DoCmd.RunSQL ("INSERT INTO Table1 (fldtest) VALUES ('" & [fldMemo] & "');")

And that in the memo i have a (') character it create an error

if my line is like :

DoCmd.RunSQL ("INSERT INTO Table1 (fldtest) VALUES (""" & [fldMemo] & """);")

And that in the memo i have a (") character it create an error

if my line is like :

DoCmd.RunSQL ("INSERT INTO Table1 (fldtest) VALUES ([fldMemo]);")

And that in the memo i have a (') or (") its all fine but it seem i cant input more then about 512 character

Anyone know what to do with that particular probleme ?

 

[MarkK]

In this situation I would do the insert using a recordset.

Private Sub AddRecord(table as string, field as string, data as variant)
[COLOR="Green"]' Adds a new record to "table" and inserts "data" into "field"[/COLOR]
  Dim rst as dao.recordset

[COLOR="Green"]  'open an empty recordset against the specified table and field[/COLOR]
  set rst = currentdb.openrecordset( _
    "SELECT " & field & " FROM " & table & " WHERE False;")
  with rst
[COLOR="Green"]    'add a record and assign "data" to "field"[/COLOR]
    .addnew
    .fields(0).value = data
    .update
    .close
  end with
End Sub

 

[Dennisk]

you can use chr(39) instead of a '

 

[Vbark]

or do a find and replace of every single ' with two single ' this will insert into the string as one.

I normally set up a finction call SQLClean like the following, and run the string I am about to insert into it.

Public Function SQLClean(strIn As String) As String


SQLClean = Replace(strIn, Chr(39), Chr(39) & Chr(39), 1, -1, vbTextCompare)


End Function

You can add other lines to SQLClean as needed.

 

[AngelsGuardian]

Ok

I went for lagbolt's option ... at first i tought it was way to long for nothing and it's the reason why i didnt use DAO before but i have to face the reality... it does a realy great job and i will replace the majority of my "docmd.runsql" strings with that.

Thank you all guys for your comments :O)

 

[AngelsGuardian]

Ok... using

Private Sub AddRecord(table as string, field as string, data as variant)
' Adds a new record to "table" and inserts "data" into "field"
  Dim rst as dao.recordset

  'open an empty recordset against the specified table and field
  set rst = currentdb.openrecordset( _
    "SELECT " & field & " FROM " & table & " WHERE False;")
  with rst
    'add a record and assign "data" to "field"
    .addnew
    .fields(0).value = data
    .update
    .close
  end with
End Sub

I end up having a pop-up telling i am going to adda new line.. do you want to procede blah blah ...

I tried to use the docmd.setwarnings (0) but it stop the code from working completely

You guys know any way to prevent the pop up ?

 

[MarkK]

Private Sub AddRecord(table as string, field as string, data as variant)
' Adds a new record to "table" and inserts "data" into "field"
  Dim rst as dao.recordset

  DoCmd.SetWarnings False
  'open an empty recordset against the specified table and field
  set rst = currentdb.openrecordset( _
    "SELECT " & field & " FROM " & table & " WHERE False;")
  with rst
    'add a record and assign "data" to "field"
    .addnew
    .fields(0).value = data
    .update
    .close
  end with
  DoCmd.SetWarnings True
End Sub