No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Mon, 23 Feb 2009 02:44:02


Hello!

In my db there is a link on file field where the fill paths are contained.
I've a task to get a calc field with short file name from the full file name
by SQL query (necessarily).
But as I see the InStrRev func doesn't work (as well as Replace and some
more funcs). Why? I need the InStrRev func very much indeed (and no other
tricks help me).
How can make the InStrRev func to be working in SQL query (through ADO 2.8
from VB6)?

Great thanks in advance.
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Mon, 23 Feb 2009 02:44:03

Hello!

In my db there is a link on file field where the fill paths are contained.
I've a task to get a calc field with short file name from the full file name
by SQL query (necessarily).
But as I see the InStrRev func doesn't work (as well as Replace and some
more funcs). Why? I need the InStrRev func very much indeed (and no other
tricks help me).
How can make the InStrRev func to be working in SQL query (through ADO 2.8
from VB6)?

Great thanks in advance.

 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Mon, 23 Feb 2009 02:46:00

Hello!

In my db there is a link on file field where the fill paths are contained.
I've a task to get a calc field with short file name from the full file name
by SQL query (necessarily).
But as I see the InStrRev func doesn't work (as well as Replace and some
more funcs). Why? I need the InStrRev func very much indeed (and no other
tricks help me).
How can make the InStrRev func to be working in SQL query (through ADO 2.8
from VB6)?

Great thanks in advance.
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by Richard Mu » Mon, 23 Feb 2009 06:45:44


Both Replace and InStrRev work fine in my VB6 on at least 3 computers. I
think InStrRev was new in VB6, but I think Replace has been around awhile.
There is also no Reverse in VB5. Long ago I coded the following in VB5 to
replace Reverse and InStrRev:
========
Public Function InStrReverse(ByVal strText As String, ByVal strSearch As
String, _
Optional ByVal intStart As Integer = -1, _
Optional ByVal Mode As VbCompareMethod = vbBinaryCompare) As Integer
' Find index of last occurrence of a search string in a text string.
' Uses Function StrRev.

strText = StrRev(strText)
strSearch = StrRev(strSearch)
If intStart = -1 Then
intStart = Len(strText)
End If
intStart = Len(strText) - intStart + 1
InStrReverse = InStr(intStart, strText, strSearch, Mode)
If InStrReverse = 0 Then Exit Function
InStrReverse = Len(strText) - InStrReverse - Len(strSearch) + 2
End Function

Public Function StrRev(ByVal strText As String) As String
' Reverse the characters in a string.
' Used by Function InStrReverse.

Dim intIndex As Integer

intIndex = Len(strText)
StrRev = ""
Do Until intIndex = 0
strText = Left(strText, intIndex)
StrRev = StrRev & Right(strText, 1)
intIndex = intIndex - 1
Loop
End Function

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.yqcomputer.com/
--
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by Richard Mu » Mon, 23 Feb 2009 07:11:59


"Richard Mueller [MVP]" < XXXX@XXXXX.COM > wrote in



Sorry, I see now that Replace was also new to VB6. Also, the function was
StrReverse that was not in VB5 but was new to VB6. At the same time I coded
the functions I gave earlier, I coded this replacement for the missing
Replace function:
============
Private Function ReplaceString(ByVal strString As String, ByVal strSearch As
String, _
ByVal strReplace As String, _
Optional ByVal Mode As VbCompareMethod = vbBinaryCompare) As String
' Take an input string and replace every instance of a search string with a
replace string.

Dim intIndex As Integer

strReplace = strString
intIndex = InStr(1, strReplace, strSearch, Mode)
Do Until intIndex = 0
strReplace = Mid(strReplace, 1, intIndex - 1) & strReplace &
Mid(strReplace, _
intIndex + Len(strSearch))
intIndex = InStr(intIndex + Len(strReplace), strReplace, strSearch,
Mode)
Loop
End Function

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.yqcomputer.com/
--
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Mon, 23 Feb 2009 16:47:01

Richard,

I ment not working in this:

Dim Con
Set Con = CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDbPath &
";Persist Security Info=False"

Dim sSql
sSql = "SELECT INSTRREV([FilePath]) FROM [MyTable]"

Dim rstA As Recordset
Set rstA = CreateObject("ADODB.Recordset")
rstA.Open sSql, Con


JET SQL, understand?
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Mon, 23 Feb 2009 16:49:01

Richard,

I ment not working in this:

Dim Con
Set Con = CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDbPath &
";Persist Security Info=False"

Dim sSql
sSql = "SELECT INSTRREV([FilePath]) FROM [MyTable]"

Dim rstA As Recordset
Set rstA = CreateObject("ADODB.Recordset")
rstA.Open sSql, Con


JET SQL !!!
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by Douglas J. » Mon, 23 Feb 2009 23:53:33


Unfortunately, I don't believe you can.

When you're querying a Jet database from outside of Access, you're very
limited as to which VBA functions you can use.

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no private e-mails, please)
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Tue, 24 Feb 2009 02:43:01

Douglas, thanks.

But the InStr function works fine an many more others! There are only some
limited functions (Replace, InStrRev...) that don't work. Why we don't have
them working so far? Why whouldn't Microsoft release a new version of DAO or
ADOx or Jet or something else with the possibility of using the functions?
Devil limitation!!! (Why didn't I start writting my own DBMS 6 years ago for
my project?) I belived them and thought they would do it?

Is there some workarounds there?
May I write my own InStrRev func definition in vb6 project or so?
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by Richard Mu » Wed, 25 Feb 2009 02:49:31

You are talking about functions supported by Access, not vb6. I see that
Access supports the INSTR() function. In SQL Server the function is
CHARINDEX(), in other DBMS's the function is POSITION() or LOCATE(). I see
also that the standard SUBSTRING() function supported in SQL Server is
called MID() in Access.

T-SQL supports a REVERSE() function, which reverses a string. You'll have to
check if this is supported by Access. If is is supported, you should be able
to do what you want using REVERSE() and INSTR().

I also find that REPLACE() is a standard T-SQL function supported by SQL
Server. I cannot find if this is supported in Access. I would expect either
REPLACE() to be supported or a similar function. You will need to check
Access documentation, or an Access newsgroup.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.yqcomputer.com/
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Wed, 25 Feb 2009 04:53:02

Richard,

Great thanks.
Your idea with REVERSE function in T-SQL (StrReverse in JET SQL) will do.
(MS guys are dolts, since they haven't made the implementation of InStrRev
till now.)

Have a nice days.
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Thu, 05 Mar 2009 06:11:01

Richard,

Unfortunatly the StrReverse function is not supplied too in Jet SQL (like
InStrRev and Replace).
May I define my own function in VB6 and use them in queries?

Thanks.
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by Schmid » Thu, 05 Mar 2009 07:52:10


"Ivan Abramov" < XXXX@XXXXX.COM > schrieb im


No, IIRC that's only possible within Access-VBA,
not in VB5/6 directly.

If your App is not too large (so that an adaption to the
SQLite Connection- and Recordset-Classes could be
done in a "reasonable timeframe"), and if you don't need to
"stay with ADO for upgrading-reasons to larger Servers",
then you could take a look at my (ADO-like usable)
approach with the just mentioned SQLite-Wrapper-Classes.
The Wrapper-Objects allow you to define your own SQL-
Functions directly within VB6 (InstrRev() if you want - or
even a directly usable GetFileNameFromPath()-function).

Maybe this would be an option for you - in case you
only need a replacement for a "small local-App-usage-
Desktop-DB, which does not require a larger rewrite".

The latest version from the link below comes with
support for nested transactions and thereby closing
the last missing "gap of features" to the JET-engine.

www.datenhaus.de/Downloads/dhRichClient3.zip
contains the 3 Toolset-Dlls, which is all you'd need
to deploy then (no ADO- or JET-Runtimes needed),
usage also possible in a regfree manner.

Let me know, if that's interesting (or an option) for you,
then I could provide you with an appropriate Demo
regarding the definition of your own SQL-functions.

Olaf
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by SXZhbiBBYn » Thu, 12 Mar 2009 19:56:18

Thank you, Olaf.

But it's rather risky to abandon the ADO2.8.
I don't understand why Microsoft hasn't included the implementation of these
2 simple functions so far. It's a part of sabotage or so.
And I believed in that...

Ivan.
 
 
 

No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)

Post by Schmid » Thu, 12 Mar 2009 22:47:05


"Ivan Abramov" < XXXX@XXXXX.COM > schrieb im

Maybe, as said that depends on your App - and if you want
to keep open the option, to switch to other DB-engines
(or -backends) in the future (with not all that much effort).

But if your App is using your DB mostly in "Desktop-Mode",
then SQLite and the ADO-like wrapper-classes within
the RichClient-AX-Dll can help to lower your dependencies,
improve performance, no 2GB-size-limit, etc.
You will have to balance the pros and cons on your side.
What remains possible with relative low efforts is, that
you can support both, the ADO-Connection, Command and
Recordset-Classes as well as their SQLite-pendants
over a thin DB-Layer-interface, which keeps both
class-based DataAccess-Models "transparent" to your App.

Nah, they are not in the class of these "typical SQL-functions" -
somewhere you will have to draw a line.

An easy to use "Plugin-interface" for selfdefined Functions
(over COM and not dependent on VBA as currently within
Access itself) would have been the better choice for a
"well-maintained" JET-engine.
But MS is shifting focus apparently to other Engines, more
compatible to their Flagship MS-SQLServer - JET seems
to be (or is already) deprecated (as you can deduce also
from the missing 64Bit-support).

Olaf