asked on

Using the REPLACE( str, str, str) command in a SQL statement for a adodb.recordset

I have an access 2000 database that I am connecting to using the following connection string:

rs.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=P:\EDD\VVL\ValidValues.mdb ;Jet OLEDB:Database"

No problem accessing it until I tried to use the REPLACE() function in a SQL statement.

SELECT replace(casvv,chr(39),chr(33)) FROM tblCASSTD;

The REPLACE() works as expected when open the table in MSACCESS but does not work when I try to open the DB in VB6.0 code. Gives the follow error: undefined function 'replace' in expression. Can I use the REPLACE() function in SQL when using ADODB recordsets? If not, is there an alternative function?

The reason I am using the REPLACE in the first place is because I have single quotes in my search field and it returns an error when I use try to use the rs.filter that has a single quote. So, I replace the single quotes with a different character and the filter works fine. I tried using two single quotes together, but that does not seem to work in the filter either. Thanks in advance.

Leigh Purvis

Interesting.

Replace wasn't supported in Access SQL when 2000 first came out (but then many things in it were well dodgy to begin with).
But it got a lot better. (And Jet is doing the opening anyway - and as long as it can read it... it will do so from any source)

Opening the recordset from VB6 shouldn't make a big difference (I wouldn't have thought so anyway).
Are you using *exactly* the same code from there?

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jyamazawa

ASKER

Here is the actual connection code where I get the error:

Set cVVL = New ADOdb.Connection
Set rVVL = New ADOdb.Recordset

cVVL.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=P:\EDD\VVL\ValidValues.mdb;"
cVVL.Open
'MAINTAIN RECORDSET CONNECTION
rVVL.Source = "SELECT replace(VV, chr(39), chr(33)) as CASVV,CLIENTVV,[CAS_NO],[ALTVV],[ABBR],[CASASID],[VVLTYPE],[VVLLIST]from joinAll WHERE [VVLTYPE] = "CLIENT_DIOXIN" AND ucase(VVLLIST) = "CASE_COMPONENTS" ORDER BY [VV];"
rVVL.CursorType = adOpenStatic
rVVL.LockType = adLockOptimistic
rVVL.ActiveConnection = cVVL
rVVL.Open Options:=adCmdText

I do have to add to this that I often use two machines at work, one with MSAccess 2000 and the other with 2003. The database was created with 2000, but I've been doing most of my development work on the machine with 2003. After the first comment, I checked and went to the 2000 machine and yes, replace does not work on that machine when I manually run a query. Sorry for the confusion since I checked my code using 2003 on a 2000 database. So, does MSACCESS 2003 have a different provider that I can use which allows me to access the extended SQL statements.

I tried using a double-quote " instead of two '' single-quotes and it still did not return any of the data from my recordset with single-quotes in the field.

An alternative I can use an oracle table with the same data in it, single-quotes and all, and connect using an ODBC connection. Would doing that allow me to use the replace() function?