Re: SQL REPLACE function does not work thru ADO/DAO/OLE layer

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Norman Yuan (nobody_at_nowhere.no)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 07:36:20 -0700

Replace() is a VB/VBA function. You can use it in Access because Access
integrates it into Access application (not Jet engine). While you sent SQL
statement through ADO/DAO, it is processed by Jet engine, thus, Replace() in
SQL statement is not recogised.

"rmsterling" <rmsterling@avaya.com> wrote in message
news:985197AC-7051-40B6-8D9B-3F8E9E2A7C04@microsoft.com...
> Windows 2000 Professional installed with Service Pack 4.
> MDAC 2.8 installed.
> Visual Basic 6.0 installed with Service Pack 5.
>
> SQL REPLACE function executes fine in a Access 2003 Query window with an
> Access 2000 database.
>
> Run the same query in a VB6 SP5 program causes an error through the
DAO/OLE
> layer.
>
> References
> Microsoft DAO 3.6
> Microsoft ADO 2.8
>
> ' Connection Defined & Setup
> Global adoConnection As New ADODB.Connection
> adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> sDBLocation & "\" & DB_FILENAME & ";"
>
> ' Query Defined & Executed
> Dim sSQL As String
> sSQL = "UPDATE Question SET sScriptLine=REPLACE(sScriptLine,'LINE:11 ','L
')
> WHERE lRunNo = 45;"
> adoConnection.Execute sSQL
>
> ' Error produced from running query
> ADO/DAO/OLE Error
> Number : -2147217900 (80040E14 Hex)
> Desc : Undefined function 'REPLACE' in expression.
> Native : -530123806
> Source : Microsoft JET Database Engine
> SQL State : 3085
>
> Can anyone tell me what I need to do to get this to work?
>
> --
> ----------------------------
> Regards,
> Richard Sterling
> Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815
> Avaya ECS Ltd, United Kingdom
> mailto:rmsterling@avaya.com

Relevant Pages

  • Re: Access, ASP, DATE problems!
    ... Yes I am using the JET engine. ... require dates in American format. ... Criteria row under a date field in Query Design, and then switch to SQL ... My code is successfully submitting dates (they appear in Access as ...
    (comp.databases.ms-access)
  • Re: Sloooow queries
    ... Access data Project or regular database with Linked tables. ... If the Jet engine is involved, and the tables are large, this is ... When I am running queries through Access to SQL, ...
    (microsoft.public.sqlserver.programming)
  • Jet finds a syntax error in my SQL statement
    ... I'm trying to alter a report's recordsource using VB code, but the Jet engine ... It is adding brackets where they don't belong. ... Post, A.AssignmentEndDate, A.AssignedEmployeeSS AS SS FROM Assignments AS A]. ... My SQL code inside the VB code looks as follows: ...
    (microsoft.public.access.queries)
  • Re: Access and GUID Column Type
    ... I'm using the Jet Engine and Access DataBase. ... Gotta be a bug in the Jet Engine. ... Perhaps you could assign the SQL statement to ...
    (microsoft.public.dotnet.languages.vb)