docmd.runSQL. Trying to pass in a string

[homer2002]

Can anyone help me

I'm trying to use docmd.runsql to add a string into a table.

Problem is my strings contain ' (single quotes) " (double quotes)
and , (comma's)


This really screws up my docmd.runsql line of code.

i.e

TempString = "Hello' My ,Name 'Is "
docmd.runsql "INSERT INTO tbl (field) VALUES(' & Tempstring & ');"

doesnt work

I get this message

Runtime Error 3075 Syntax Error Missing Operator

Can I get round this easily as my sql is a touch more complex than the example.

Thanks

 

[WayneRyan]

homer2002,

In VBA you can:

Dim dbs As Database
Dim rst As RecordSet
Dim sql As String

Set dbs = CurrentDb
sql = "Select * From MyTable"
Set rst = dbs.OpenRecordset(sql)
rst.AddNew
rst!SomeField = TempString
rst.Update
Set rst = Nothing
Set dbs = Nothing

hth,
Wayne

 

[homer2002]

Cheers, thats a wicked Idea..

Never thought of doing it that way round.

You dont realise how much time thats saved me.

 

[WayneRyan]

homer2002,

No problem.

I think it is better to figure out a way to keep those characters
out of the data in the first place, but it can't always be done.

Wayne

 

[Calvin]

You actually have two problems in your post. The method you tried originally is more efficient than the method in Wayne's post and normally that is the method that I would have to suggest, you just have a minor syntax error with your single and double quotes, no big deal.

docmd.runsql "INSERT INTO tbl (field) VALUES('" & Tempstring & "');" 
'you needed a single and a double quote here--^------and here--^

BUT, because the string (TempString="Hello' My ,Name 'Is ") that you are trying to concatinate into the SQL statment has single quotes in it it will confuse the SQL statement with duplicate single quotes and generate an error anyway because the statement will have too many single quotes after it is concatinated it will look like this:

"INSERT INTO tbl (field) VALUES('Hello' My ,Name 'Is');"

so if your going to be inserting data into your table that will possibly have either single or double quotes then Waynes method is suggested other wise this method is more efficient especially if your having to insert hundreds or thousands of records.

good luck,