ADO and Access: Obtaining substring from end of string based on delimiter

Ah, I forgot an example, so here it is:

Sample Data [varchar(250) SampleColumn]:
root.application.section.subsection.item
root.application.section.subsection2.dataitem
root.application.anothersection.myleaf
root.application.lastsection.subby.lala

Sample desired value of SELECT
item
dataitem
myleaf
lala

You say they are unsupported by Access ADO but what about using the functions in a UDF from ADO?

Have you tried having a saved query in Access - using a pretty standard method like

SELECT Mid([YourField],InStrRev([YourField],'.')+1) FROM tblTable WHERE InStr([YourField],'.')>0

And using your ADO connection to just open that query?
See if Access can help you as much as possible. :-)

(And if need be - then use your own UDFs in the query - Ray's advice).

Thank you GRayL and LPurvis,

I have considered the possible option of user defined functions, however it is an undesirable solution because the particular SELECT statement that I am attempting to create is part of an upgrade process that will pull information from an existing previously installed/deployed customer database for use in the creation of a new upgraded table/database. I am attempting to form the SELECT statement in such a way that the resulting recordset may be passed to the same Load method in my VB code that is used to load the data from databases using the new database schema, rather than creating seperate special-cased load methods for each upgrade scenario, polluting my object model and making a maintenance headache.

I will look into that option further, however, while waiting to see if anyone has additional comments, suggestions or insights.

Since this seems to be stumping people, I'll raise the point value to 250.

In looking into the user defined function option, it does not appear to be working out to well. I have tried to create a user defined function and I tried creating a procedure, both of which failed. The procedure failed for the same reason that the SELECT fails - it stated the InStrRev function was undefined. The user defined procedure failed because it was unable to create the defined return table, so it may just be a syntax error on my part.

I have progressed along a different pass to side-step this issue, but I would still like to find a solution in case I need it now or later.

Post your UDF and I may be able to help debug it.

Thanks LPurvis,
Admittedly, I'm one of the ones that avoids DAO. My current solution is pure ADO and I am loathe to add additional dependencies, etc. In working through this, I re-evaluated a bit and ended up enhancing my primary load method to more robustly accept values and interpret them as needed, giving me more leeway in what values I pull in.