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.

__________________
Essential: Multiple Forms
● 101 Samples: 2002 | 2003 | 2005 ● Free Components: WFC | XPCC | ElementsEx | VBPP | ADO.NET/MySQL | VisualStyles | NPlot | SDF ● Tutorials: Home & Learn | Start VB.NET | Learn VB.NET ● Favourites: MSDN | WinForms.NET | ASP.NET | WinForms FAQ | WebForms FAQ | GotDotNet | Code Project | DevBuzz

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.

__________________
Essential: Multiple Forms
● 101 Samples: 2002 | 2003 | 2005 ● Free Components: WFC | XPCC | ElementsEx | VBPP | ADO.NET/MySQL | VisualStyles | NPlot | SDF ● Tutorials: Home & Learn | Start VB.NET | Learn VB.NET ● Favourites: MSDN | WinForms.NET | ASP.NET | WinForms FAQ | WebForms FAQ | GotDotNet | Code Project | DevBuzz

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: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ