Windows Vista Forums

Iterating Functions

  1. #1


    p byers Guest

    Iterating Functions

    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


      My System SpecsSystem Spec

  2. #2


    Jeff C Guest

    RE: Iterating Functions


    --
    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
    >
    >

      My System SpecsSystem Spec

  3. #3


    p byers Guest

    Re: Iterating Functions



    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)



      My System SpecsSystem Spec

  4. #4


    Richard Mueller [MVP] Guest

    Re: Iterating Functions


    "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
    --



      My System SpecsSystem Spec

  5. #5


    p byers Guest

    Re: Iterating Functions



    "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)


      My System SpecsSystem Spec

  6. #6


    trading_jacks Guest

    Re: Iterating Functions

    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.

      My System SpecsSystem Spec


Iterating Functions
Similar Threads
Thread Forum
Functions and how to use them PowerShell
Fun with functions PowerShell
Iterating over List of Servicese PowerShell
Where are the functions? Vista General
Functions PowerShell