can you really have a function in the command text?

I have a line of code as below:

oCommand.CommandText = "UPDATE [SHEET1$] SET TEST = replace([test], '~', ';')"

I keep getting an error "Undefined function 'Replace' in expression"

The code that you put in the CommandText gets executed by the database engine, so you can include any functions that are supported by the database engine. Obviously Replace is not supported by the Jet OLEDB provider for Excel.

__________________

2007, 2008, 2009, 2010

Why is my data not saved to my database? | Communicating between multiple forms
MSDN: Data Walkthroughs | "How Do I?" Videos
My Blog: Custom Events | Dynamic GDI+ Drawing

This is a data access issue so it belongs in the Data Access forum. Please post in the most appropriate forum, not just the first one. Moved.

__________________

2007, 2008, 2009, 2010

Why is my data not saved to my database? | Communicating between multiple forms
MSDN: Data Walkthroughs | "How Do I?" Videos
My Blog: Custom Events | Dynamic GDI+ Drawing

List of all vba functions:
http://office.microsoft.com/en-us/ac...353121033.aspx

List of functions allowed in "safe mode":
How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003

If these don't apply to your version of jet, you can consider stating your version of jet next time you ask a question

__________________
DW1 DW2 DW3 DW4 DNU PQ