Insert Into error with ' in strings.

[vinzz]

Hello,

I have a problem when i want to insert a string into a table using SQL Insert Into statement

example:

set db = currentdb()
set rs = db.openrecordset(AnSQLstr, dbopensnapshot,dbfailonerror)
rs ....
....
'examplefield: RS!afield has this string = "an example that has an ' inside"
when i run
db.execute "insert into atbl(tstrow) values ('" & RS!afield & "';", dbfailonerror)

it gives an error because in the string is an '
how can i resolve this without replacing the ' with something else?

 

[pl456]

not sure if this helps but can you not use the Char(39). may be, may be not?

 

[MSAccessRookie]

Hello,

I have a problem when i want to insert a string into a table using SQL Insert Into statement

example:

set db = currentdb()
set rs = db.openrecordset(AnSQLstr, dbopensnapshot,dbfailonerror)
rs ....
....
'examplefield: RS!afield has this string = "an example that has an ' inside"
when i run
db.execute "insert into atbl(tstrow) values ('" & RS!afield & "';", dbfailonerror)

it gives an error because in the string is an '
how can i resolve this without replacing the ' with something else?

It is not clear to me why you are unable to change the "'". If you are able to change it, then I believe that the following might work for you.

db.execute "insert into atbl(tstrow) values (" [COLOR=red][B]& Char(34)[/B][/COLOR] & RS!afield & " [B][COLOR=red]& Char(34)[/COLOR][COLOR=red] &[/COLOR][/B] ";", dbfailonerror)

 

[MSAccessRookie]

not sure if this helps but can you not use the Char(39). may be, may be not?

Char(39) is the ' character.
It probably will not work as expected, since the ' in the beginning will match up with the ' in the text, not the ' at the end as desired.

Char(34) is the " character.
It should work better, since the " in the beginning will match up with the " at the end as desired, not the ' in the text.

 

[vinzz]

Thanks, i'll check it out