Oh I'm a newbie trying to do something simple in VB6.0 DAO

Dim db As Database
Dim strSQL As String

FileCopy "C:orbscan.mdb", "C:orbscanCOPY.mdb"

Set db = OpenDatabase("C:orbscanCOPY.mdb")

strSQL = "UPDATE Exam SET ExamData = REPLACE(ExamData,""C:"",""P:"")" & ";"

db.Execute strSQL

Rem FileCopy  to other directories

Set db = Nothing
======================================

It doesn't like the replace code part
of the SQL. is say function replace is
not defined.

Any help would be appreciated!
Aloha.

stephen

The Following would be closer:

"UPDATE Exam SET ExamData = 'C:' WHERE ExamData = 'P:'"

Although I don't know if it'd work in Access.

Actually the field EXAMDATA
is a path C:data123.exm etc
so I am trying to change the drive letter
to P:data123.exm etc...


Im not sure why it still barfs.
Any help with UPDATE ... SET ... =REPLACE..

UPDATE [Exam] SET [ExamData] = 'P' & Right(ExamData,Len(ExamData)-1) WHERE left(ExamData,1) = 'C'

The above would change all records that start with "C" to "P"
IE: if record is: "C:data123.exm" it will become: "P:data123.exm"

This works in Access XP as stated, in 2000 may, not sure in earlier versions.

[Edited by JLRodgers on 07-09-2002 at 11:23 PM GMT]

Thanks! It works as you say>

Any reason why the access generated
sql statement doesn"t work?

regards,

stephen

The "replace" was generated by access?

Strange if so, the only references to "Replace" in my access help is how to search and replace, and how to using the  "UPDATE" command. searching for "Replace(" returns nothing. Although if it did work, you may have the problem listed below in the example.

However in SQL Server, the Replace command will work (but it wouldn't be good to use given what you wanted)

ex (In SQL Server):
SELECT Replace('abc:xxdatac.xlc','c', 'p')
Will return: abp:xxdatap.xlp

So if your data had a "c" in it anywhere other than the first character, the "Replace" would've messed it up.