Hi folks

I need a function that needs to iterate many (up around 20) times.

It needs to Select records from a table

BUT if I do it as a single nested ude function, the Record Set from
previous iterations will be lost.

The only way that I can see of doing it is to have twenty functions each
with a slightly different FunctionName and RecordSetName !!


Any ideas for simple was to do it with just one function and one
TableOpen ??

Thanks in anticipation
Pete (Northolt UK)


Example
=======


dataBaseRoot = "C:\inetpub\wwwroot\data\treeSite\"



DBConMT1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
dataBaseRoot & "data\atree.mdb"
set my_connMT1 = Server.CreateObject("ADODB.Connection")
my_ConnMT1.Open DBConMT1

DBConMT2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
dataBaseRoot & "data\atree.mdb"
set my_connMT2 = Server.CreateObject("ADODB.Connection")
my_ConnMT2.Open DBConMT2



''''''''''''''''etc



DBConMT20 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
dataBaseRoot & "data\atree.mdb"
set my_connMT20 = Server.CreateObject("ADODB.Connection")
my_ConnMT20.Open DBConMT20



cRecRef = "abc"



function lookForRefs1(cRefIn)
StrSqlMT1 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
"'"
set rsMT1 = my_connMT1.Execute (StrSqlMT1)
if NOT rsMT1.EOF AND NOT rsMT1.BOF then
do while NOT rsMT1.EOF
''process bottom-ref here
cReply = lookForRefs2(rsMT1("bottom_ref"))
rsMT1.MoveNext
loop
end if

lookForRefs = "OK"
end function

function lookForRefs2(cRefIn)
StrSqlMT2 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
"'"
set rsMT2 = my_connMT2.Execute (StrSqlMT2)
if NOT rsMT2.EOF AND NOT rsMT2.BOF then
''process bottom-ref here
do while NOT rsMT2.EOF
cReply = lookForRefs3(rsMT2("bottom_ref"))
rsMT2.MoveNext
loop
end if



lookForRefs = "OK"
end function



''''''''''''''''etc



function lookForRefs20(cRefIn)
StrSqlMT20 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
"'"
set rsMT20 = my_connMT20.Execute (StrSqlMT20)
if NOT rsMT20.EOF AND NOT rsMT20.BOF then
do while NOT rsMT20.EOF
''process bottom-ref here
rsMT20.MoveNext
loop
end if

lookForRefs = "OK"
end function

--
Jeff C
Live Well .. Be Happy In All You Do


"p byers" wrote:

> Hi folks
>
> I need a function that needs to iterate many (up around 20) times.
>
> It needs to Select records from a table
>
> BUT if I do it as a single nested ude function, the Record Set from
> previous iterations will be lost.
>
> The only way that I can see of doing it is to have twenty functions each
> with a slightly different FunctionName and RecordSetName !!
>
>
> Any ideas for simple was to do it with just one function and one
> TableOpen ??

Have you considered creating action queries and/or VBA code that
accomplishes your process and then placing your process into a macro (which
can repeat).

Then you can use Access command line functionality to run the macro from a
batch file which could then be scheduled?

Sample command line:

"full path to access.exe" "full path to atree.mdb" /x "name of macro"

>
> Thanks in anticipation
> Pete (Northolt UK)
>
>
> Example
> =======
>
>
> dataBaseRoot = "C:\inetpub\wwwroot\data\treeSite\"
>
>
>
> DBConMT1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> dataBaseRoot & "data\atree.mdb"
> set my_connMT1 = Server.CreateObject("ADODB.Connection")
> my_ConnMT1.Open DBConMT1
>
> DBConMT2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> dataBaseRoot & "data\atree.mdb"
> set my_connMT2 = Server.CreateObject("ADODB.Connection")
> my_ConnMT2.Open DBConMT2
>
>
>
> ''''''''''''''''etc
>
>
>
> DBConMT20 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> dataBaseRoot & "data\atree.mdb"
> set my_connMT20 = Server.CreateObject("ADODB.Connection")
> my_ConnMT20.Open DBConMT20
>
>
>
> cRecRef = "abc"
>
>
>
> function lookForRefs1(cRefIn)
> StrSqlMT1 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
> "'"
> set rsMT1 = my_connMT1.Execute (StrSqlMT1)
> if NOT rsMT1.EOF AND NOT rsMT1.BOF then
> do while NOT rsMT1.EOF
> ''process bottom-ref here
> cReply = lookForRefs2(rsMT1("bottom_ref"))
> rsMT1.MoveNext
> loop
> end if
>
> lookForRefs = "OK"
> end function
>
> function lookForRefs2(cRefIn)
> StrSqlMT2 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
> "'"
> set rsMT2 = my_connMT2.Execute (StrSqlMT2)
> if NOT rsMT2.EOF AND NOT rsMT2.BOF then
> ''process bottom-ref here
> do while NOT rsMT2.EOF
> cReply = lookForRefs3(rsMT2("bottom_ref"))
> rsMT2.MoveNext
> loop
> end if
>
> lookForRefs = "OK"
> end function
>
>
>
> ''''''''''''''''etc
>
>
>
> function lookForRefs20(cRefIn)
> StrSqlMT20 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
> "'"
> set rsMT20 = my_connMT20.Execute (StrSqlMT20)
> if NOT rsMT20.EOF AND NOT rsMT20.BOF then
> do while NOT rsMT20.EOF
> ''process bottom-ref here
> rsMT20.MoveNext
> loop
> end if
>
> lookForRefs = "OK"
> end function
>
>

Jeff C wrote:

> --
> Jeff C
> Live Well .. Be Happy In All You Do
>
> "p byers" wrote:
>

> > Hi folks
> >
> > I need a function that needs to iterate many (up around 20) times.
> >
> > It needs to Select records from a table
> >
> > BUT if I do it as a single nested ude function, the Record Set from
> > previous iterations will be lost.
> >
> > The only way that I can see of doing it is to have twenty functions each
> > with a slightly different FunctionName and RecordSetName !!
> >
> >
> > Any ideas for simple was to do it with just one function and one
> > TableOpen ??

>
> Have you considered creating action queries and/or VBA code that
> accomplishes your process and then placing your process into a macro (which
> can repeat).
>
> Then you can use Access command line functionality to run the macro from a
> batch file which could then be scheduled?
>
> Sample command line:
>
> "full path to access.exe" "full path to atree.mdb" /x "name of macro"
>

> >
> > Thanks in anticipation
> > Pete (Northolt UK)
> >

Thank you for the suggestion - I will give it a try.

Since the original post, I have been mulling it over and am wondering about
adding a "Sequence number" on the table
Then
Populating it as phase One of a process
and
Re-Read the table "ORDER BY" the sequence number


But as the purpose of the script is to examine the Table (which contains data
describing a tree) for closed loops,

The creating of such sequence numbers might get into an everlasting loop !!

Actually, that may have given me the answer
Set the sequence number to null to start with
then
If I find the Sequence number is present already before I put one in - that
indicates a loop !!

Sorry to have asked a question and then rambled on in this post - I have been
contemplating the problem for several days before the original post

Pete (Northolt UK)

"p byers" <pb@xxxxxx-ltd.co.uk> wrote in message
news:4A1D54E9.86FCD066@xxxxxx-ltd.co.uk...

>
>
> Jeff C wrote:
>

>> --
>> Jeff C
>> Live Well .. Be Happy In All You Do
>>
>> "p byers" wrote:
>>

>> > Hi folks
>> >
>> > I need a function that needs to iterate many (up around 20) times.
>> >
>> > It needs to Select records from a table
>> >
>> > BUT if I do it as a single nested ude function, the Record Set from
>> > previous iterations will be lost.
>> >
>> > The only way that I can see of doing it is to have twenty functions
>> > each
>> > with a slightly different FunctionName and RecordSetName !!
>> >
>> >
>> > Any ideas for simple was to do it with just one function and one
>> > TableOpen ??

>>
>> Have you considered creating action queries and/or VBA code that
>> accomplishes your process and then placing your process into a macro
>> (which
>> can repeat).
>>
>> Then you can use Access command line functionality to run the macro from
>> a
>> batch file which could then be scheduled?
>>
>> Sample command line:
>>
>> "full path to access.exe" "full path to atree.mdb" /x "name of macro"
>>

>> >
>> > Thanks in anticipation
>> > Pete (Northolt UK)
>> >

>
> Thank you for the suggestion - I will give it a try.
>
> Since the original post, I have been mulling it over and am wondering
> about
> adding a "Sequence number" on the table
> Then
> Populating it as phase One of a process
> and
> Re-Read the table "ORDER BY" the sequence number
>
>
> But as the purpose of the script is to examine the Table (which contains
> data
> describing a tree) for closed loops,
>
> The creating of such sequence numbers might get into an everlasting
> loop !!
>
> Actually, that may have given me the answer
> Set the sequence number to null to start with
> then
> If I find the Sequence number is present already before I put one in -
> that
> indicates a loop !!
>
> Sorry to have asked a question and then rambled on in this post - I have
> been
> contemplating the problem for several days before the original post
>
> Pete (Northolt UK)
>
>

I don't understand what you are trying to do, but from your last response I
wonder if a dictionary object would help. This is great for keeping track of
unique items. I use it when working with nested groups, for example, to
prevent an infinite loop, or anytime I use recursive functions. If all you
want is to count for 20 iterations (or nested loops), then a counter may be
the solution.

Also, it looks to like your 20 Connection objects are identical. If so,
there is no reason why you cannot use one. In the past I have used several
nested Recordset objects but one Connection object with success. However, I
would recommend closing each Recordset object when you are done with it, and
then closing the Connection object when you are done with that.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

"Richard Mueller [MVP]" wrote:

> "p byers" <pb@xxxxxx-ltd.co.uk> wrote in message
> news:4A1D54E9.86FCD066@xxxxxx-ltd.co.uk...

> >
> >
> > Jeff C wrote:
> >

> >> --
> >> Jeff C
> >> Live Well .. Be Happy In All You Do
> >>
> >> "p byers" wrote:
> >>
> >> > Hi folks
> >> >
> >> > I need a function that needs to iterate many (up around 20) times.
> >> >
> >> > It needs to Select records from a table
> >> >
> >> > BUT if I do it as a single nested ude function, the Record Set from
> >> > previous iterations will be lost.
> >> >
> >> > The only way that I can see of doing it is to have twenty functions
> >> > each
> >> > with a slightly different FunctionName and RecordSetName !!
> >> >
> >> >
> >> > Any ideas for simple was to do it with just one function and one
> >> > TableOpen ??
> >>
> >> Have you considered creating action queries and/or VBA code that
> >> accomplishes your process and then placing your process into a macro
> >> (which
> >> can repeat).
> >>
> >> Then you can use Access command line functionality to run the macro from
> >> a
> >> batch file which could then be scheduled?
> >>
> >> Sample command line:
> >>
> >> "full path to access.exe" "full path to atree.mdb" /x "name of macro"
> >>
> >> >
> >> > Thanks in anticipation
> >> > Pete (Northolt UK)
> >> >

> >
> > Thank you for the suggestion - I will give it a try.
> >
> > Since the original post, I have been mulling it over and am wondering
> > about
> > adding a "Sequence number" on the table
> > Then
> > Populating it as phase One of a process
> > and
> > Re-Read the table "ORDER BY" the sequence number
> >
> >
> > But as the purpose of the script is to examine the Table (which contains
> > data
> > describing a tree) for closed loops,
> >
> > The creating of such sequence numbers might get into an everlasting
> > loop !!
> >
> > Actually, that may have given me the answer
> > Set the sequence number to null to start with
> > then
> > If I find the Sequence number is present already before I put one in -
> > that
> > indicates a loop !!
> >
> > Sorry to have asked a question and then rambled on in this post - I have
> > been
> > contemplating the problem for several days before the original post
> >
> > Pete (Northolt UK)
> >
> >

>
> I don't understand what you are trying to do, but from your last response I
> wonder if a dictionary object would help. This is great for keeping track of
> unique items. I use it when working with nested groups, for example, to
> prevent an infinite loop, or anytime I use recursive functions. If all you
> want is to count for 20 iterations (or nested loops), then a counter may be
> the solution.
>
> Also, it looks to like your 20 Connection objects are identical. If so,
> there is no reason why you cannot use one. In the past I have used several
> nested Recordset objects but one Connection object with success. However, I
> would recommend closing each Recordset object when you are done with it, and
> then closing the Connection object when you are done with that.
>
> --
> Richard Mueller
> MVP Directory Services
> Hilltop Lab - http://www.rlmueller.net
> --

Richard, thank you - "dictionary object" is a great suggestion !!

I knew that the folks in this group would direct my thinking in lots of other
directions.

Pete (Northolt UK)

On May 27, 7:24*am, p byers <p...@xxxxxx-ltd.co.uk> wrote:

> Hi folks
>
> I need a function that needs to iterate many (up around 20) times.
>
> It needs to Select records from a table
>
> BUT if I do it as a single nested ude function, the Record Set from
> previous iterations will be lost.
>
> The only way that I can see of doing it is to have twenty functions each
> with a slightly different FunctionName and RecordSetName !!
>
> Any ideas for simple was to do it with just one function and one
> TableOpen ??
>
> Thanks in anticipation
> Pete (Northolt UK)
>
> Example
> =======
>
> dataBaseRoot = "C:\inetpub\wwwroot\data\treeSite\"
>
> DBConMT1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> dataBaseRoot & "data\atree.mdb"
> set my_connMT1 = Server.CreateObject("ADODB.Connection")
> my_ConnMT1.Open DBConMT1
>
> DBConMT2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> dataBaseRoot & "data\atree.mdb"
> set my_connMT2 = Server.CreateObject("ADODB.Connection")
> my_ConnMT2.Open DBConMT2
>
> ''''''''''''''''etc
>
> DBConMT20 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> dataBaseRoot & "data\atree.mdb"
> set my_connMT20 = Server.CreateObject("ADODB.Connection")
> my_ConnMT20.Open DBConMT20
>
> cRecRef = "abc"
>
> * * function lookForRefs1(cRefIn)
> * * * * StrSqlMT1 = "SELECT * FROM links WHERE top_ref = '" &cRefIn &
> "'"
> * * * * set rsMT1 = my_connMT1.Execute (StrSqlMT1)
> *if NOT rsMT1.EOF AND NOT rsMT1.BOF then
> * * * * * * do while NOT rsMT1.EOF
> * * * * * * * * ''process bottom-ref here
> * * * * * * * * cReply = lookForRefs2(rsMT1("bottom_ref"))
> * * * * * * * * rsMT1.MoveNext
> * * * * * * loop
> * * * * end if
>
> *lookForRefs = "OK"
> * * end function
>
> * * function lookForRefs2(cRefIn)
> * * * * StrSqlMT2 = "SELECT * FROM links WHERE top_ref = '" &cRefIn &
> "'"
> * * * * set rsMT2 = my_connMT2.Execute (StrSqlMT2)
> *if NOT rsMT2.EOF AND NOT rsMT2.BOF then
> * * * * * * * * ''process bottom-ref here
> * * * * * * do while NOT rsMT2.EOF
> * * * * * * * * cReply = lookForRefs3(rsMT2("bottom_ref"))
> * * * * * * * * rsMT2.MoveNext
> * * * * * * loop
> * * * * end if
>
> *lookForRefs = "OK"
> * * end function
>
> ''''''''''''''''etc
>
> * * function lookForRefs20(cRefIn)
> * * * * StrSqlMT20 = "SELECT * FROM links WHERE top_ref = '" & cRefIn &
> "'"
> * * * * set rsMT20 = my_connMT20.Execute (StrSqlMT20)
> *if NOT rsMT20.EOF AND NOT rsMT20.BOF then
> * * * * * * do while NOT rsMT20.EOF
> * * * * * * * * ''process bottom-ref here
> * * * * * * * * rsMT20.MoveNext
> * * * * * * loop
> * * * * end if
>
> *lookForRefs = "OK"
> * * end function

What about using loop to call the same function 20 times, then using
GetRows to set the RS as a two dem array and pass it back to the main
program? I don't know if this will work for you, but it seems
possible? Is passing a recordset completely out of the question with
VBScript? I think I have tried with no luck in the past.