Syntax error

[Albilly]

Hi, I am not sur eif this is the right section or not, I have a small syntax problem and after trying all possible syntax I cannot solve this one

I need to update a table with a replace function in it but I always get the synbtax error, this is the code I use right now:

strFind = "S:\__MTL\Corp\Tresor\Compliance reporting"
Set db1 = OpenDatabase(strDB)

db1.Execute "UPDATE PARAMETRE_CONFORMITE SET REPERTOIRE_DESTINATION = Replace(REPERTOIRE_DESTINATION, '" & strFind & "', '" & strSrc & "', , , vbBinaryCompare)"

the error is on the db1.execute
Where is my mstake here ???

Thanks

 

[Galaxiom]

vbBinaryCompare is a VBA constant that is not available to the database engine.

Concatente it into the SQL command so that it becomes the value of the constant before being executed. Or directly use the zero that it stands for.

 

[BlueIshDan]

You could always make a public function within a module that acts as an interface between VBA and the database engine... Idk what kind of flags that raises for anyone, or if it causes huge performance issues, but yeah...

 

[arnelgp]

db1.Execute "UPDATE PARAMETRE_CONFORMITE SET REPERTOIRE_DESTINATION = Replace(REPERTOIRE_DESTINATION, '" & strFind & "', '" & strSrc & "', , , 0)"