VBA SQL Quote - Unquote

[dh_ander]

I am working with a linked sql table which I was able to set-up up thanks the help of you guy's here on this board.

the code below (thanx to Ahnold) is used to create the SQL statement which enables me to enter the values in my SQL table.

SQL = "INSERT INTO tblFoldersandFiles (map, datum, grootte, bestandsnaam)" & _
"VALUES ('" & strMap & "','" & strDatum & "','" & dblGrootte & _
"','" & strBestandsnaam & "')"

Basically I read a "files and folders" listing into a sql table
the problem starts when a file or folder name contains a single quote and ofcourse this can be anywhere in the string.
Any suggestions how to deal with this?

 

[Cbrine]

dh_ander,
You can use the replace function to replace a value within a string. I believe the correct replacement is ' X 2 for a single '. You can replace the replace value with whatever is required though.

SQL = "INSERT INTO tblFoldersandFiles (map, datum, grootte, bestandsnaam)" & _
"VALUES ('" & Replace(strMap, "'", "''") & "','" & Replace(strDatum, "'", "''") & "','" & Replace(dblGrootte, "'", "''") & _
"','" & Replace(strBestandsnaam, "'", "''") & "')"

HTH
Cal

 

[dh_ander]

You guuy's are avsolutely amazing

THANKS