3

I am having a VB.Net application. I connect it with a MS Access database. Now I have to execute a query. My query is : Update table1 set field1=replace(field1,'|','"')

This query seems to work fine from the query analyzer of the Access database but when I do the same form the VB.Net code it throws me an error stating "Undefined function 'Replace' in expression"

I am using OLEDB connection and command. I am using the "executenonquery" feature.

Can any one help me?

hawbsl
  • 15,313
  • 25
  • 73
  • 114
Rajdeep
  • 485
  • 4
  • 15
  • 28

2 Answers2

4

The currently-accepted answer to this question is somewhat outdated. Using

Provider=Microsoft.ACE.OLEDB.12.0

the following C# code works just fine:

cmd.CommandText =
    "UPDATE table1 SET field1 = Replace(field1, '|', '\"')";
cmd.ExecuteNonQuery();

In other words, the Replace() function may have caused problems with the older "Jet" OLEDB driver (and perhaps earlier versions of the "ACE" driver) but as of the version for Access 2010 this is no longer an issue.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Is there a certain version of VS you need to run Microsoft.Ace.OLEDB.12? If not then where can I get it to add to my project? – DotNet Programmer Mar 07 '16 at 18:47
  • No particular version of Visual Studio is required and you shouldn't need to add any additional references to your project. Just use `System.Data.OleDb` objects and a connection string that includes `Provider=Microsoft.ACE.OLEDB.12.0`. (And make sure that the "bitness" of the installed version of the Access Database Engine matches the "bitness" of your application: 64-bit or 32-bit.) – Gord Thompson Mar 07 '16 at 19:30
  • I don't see Ace.OLEDB.12.0 as a option to import into my class. I have the System.Data.OleDb imported into my class – DotNet Programmer Mar 07 '16 at 19:40
  • Just create an OleDbConnection object with the appropriate connection string. See connectionstrings.com for details and/or search for examples here on SO. – Gord Thompson Mar 07 '16 at 21:12
4

Unfortunately when you connect to an Access database from you VB.Net application you aren't using the Access query engine at all. You're using Jet. Some of the functions available to you in Access (such as Replace) aren't available in Jet.

The classic solution is to use a combination of Iif, Instr etc (those are available). Yep, it's not as nice as having Replace to play with but you'll have to learn to do without.

hawbsl
  • 15,313
  • 25
  • 73
  • 114
  • I am very new to Sql query. Iam confused to find the alternative of replace function which is accepted in Jet. Any help? – Rajdeep Jun 14 '11 at 09:58