No InStrRev func in JET SQL (vb6 + ADO2.8 + Access2000)
Ivan Abramov
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.
Ivan Abramov
Ivan Abramov
Richard Mueller [MVP]
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.rlmueller.net
--
Richard Mueller [MVP]
"Richard Mueller [MVP]" <rlmuelle...@ameritech.nospam.net> wrote in
message news:OHb0G3Gl...@TK2MSFTNGP02.phx.gbl...
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)
Ivan Abramov
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?
Ivan Abramov
JET SQL !!!
Douglas J. Steele
news:24307EA8-6FC9-4E74...@microsoft.com...
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://I.Am/DougSteele
(no private e-mails, please)
Richard Mueller [MVP]
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.rlmueller.net
--
"Ivan Abramov" <IvanA...@discussions.microsoft.com> wrote in message
news:9F0E1B0A-CC1A-4BAE...@microsoft.com...
Ivan Abramov
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.
Schmidt
"Ivan Abramov" <IvanA...@discussions.microsoft.com> schrieb im
Newsbeitrag news:BEAFB067-1006-4810...@microsoft.com...
> 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?
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
Schmidt
> Thank you, Olaf.
>
> But it's rather risky to abandon the ADO2.8.
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.
> 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...
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