Simple Array question

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • deko

    #1

    Simple Array question

    I use a For Each... Next loop like this:

    For Each varFnm In Array("This", "That", "OtherThing ", "Foo", "Bar")
    RunSql ("UPDATE.... bla bla bla)
    Next

    But the exact same elements of the Array are contained in a Table -- Is
    there some way to use a query to set the Array, or some other way to avoid
    using the Array?

    Here's some pseudo code ...

    For Each varFnm in qryStuff
    RunSql("UPDATE. .. bla bla bla)
    Next

    Can someone suggest a correct way to do this?

    Thanks in advance!


  • Fletcher Arnold

    #2
    Re: Simple Array question

    "deko" <dje422@hotmail .com> wrote in message
    news:gdFzb.6549 4$ZA2.48798@new ssvr25.news.pro digy.com...[color=blue]
    > I use a For Each... Next loop like this:
    >
    > For Each varFnm In Array("This", "That", "OtherThing ", "Foo", "Bar")
    > RunSql ("UPDATE.... bla bla bla)
    > Next
    >
    > But the exact same elements of the Array are contained in a Table -- Is
    > there some way to use a query to set the Array, or some other way to avoid
    > using the Array?
    >
    > Here's some pseudo code ...
    >
    > For Each varFnm in qryStuff
    > RunSql("UPDATE. .. bla bla bla)
    > Next
    >
    > Can someone suggest a correct way to do this?
    >
    > Thanks in advance![/color]


    If I knew the details of ("UPDATE.... bla bla bla) I could have made a
    suggestion, but the following examples might show you th way forward:

    Either using a join:

    UPDATE tblThings INNER JOIN tblSpecialThing s ON
    tblThings.Thing Name = tblSpecialThing s.ThingName
    SET tblThings.IsSpe cial=True


    Or a sub-query:

    UPDATE tblThings SET tblThings.IsSpe cial=True
    WHERE tblThings.Thing Name IN
    (SELECT tblSpecialThing s.ThingName FROM tblSpecialThing s)


    HTH
    Fletcher


    Comment

    • Scott McDaniel

      #3
      Re: Simple Array question

      If the elements are in a table, open a recordset and step through that:

      Dim rst As ADODB.Recordset

      Set rst = New ADODB.Recordset

      rst.OPen "SQL STatement Here", currentproject. connection

      Do Until rst.EOF
      RunSQL("UPDATE ... bla bla bla")
      Loop

      rst.Close
      Set rst = Nothing

      "deko" <dje422@hotmail .com> wrote in message
      news:gdFzb.6549 4$ZA2.48798@new ssvr25.news.pro digy.com...[color=blue]
      > I use a For Each... Next loop like this:
      >
      > For Each varFnm In Array("This", "That", "OtherThing ", "Foo", "Bar")
      > RunSql ("UPDATE.... bla bla bla)
      > Next
      >
      > But the exact same elements of the Array are contained in a Table -- Is
      > there some way to use a query to set the Array, or some other way to avoid
      > using the Array?
      >
      > Here's some pseudo code ...
      >
      > For Each varFnm in qryStuff
      > RunSql("UPDATE. .. bla bla bla)
      > Next
      >
      > Can someone suggest a correct way to do this?
      >
      > Thanks in advance!
      >
      >[/color]


      Comment

      • deko

        #4
        Re: Simple Array question

        thanks for the reply.

        hmmmm... that may work, but the best way to solve my problem would be to
        programmaticall y populate the Array from a query/table.

        I've discovered that I need to keep the For Each loop in tact. In generic
        terms:

        For Each varAbc in Array("a", "b", "c")
        do something
        Next

        There is a reason I need varAbc ...

        Again, there is a Table that has the exact elements a, b,c -- what I'd like
        to do is programmaticall y dump the contents of that table into the array.


        "Scott McDaniel" <scott@no.spam. thedatabaseplac e.net> wrote in message
        news:l_WdnevXca ZvtlKiRVn-hg@comcast.com. ..[color=blue]
        > If the elements are in a table, open a recordset and step through that:
        >
        > Dim rst As ADODB.Recordset
        >
        > Set rst = New ADODB.Recordset
        >
        > rst.OPen "SQL STatement Here", currentproject. connection
        >
        > Do Until rst.EOF
        > RunSQL("UPDATE ... bla bla bla")
        > Loop
        >
        > rst.Close
        > Set rst = Nothing
        >
        > "deko" <dje422@hotmail .com> wrote in message
        > news:gdFzb.6549 4$ZA2.48798@new ssvr25.news.pro digy.com...[color=green]
        > > I use a For Each... Next loop like this:
        > >
        > > For Each varFnm In Array("This", "That", "OtherThing ", "Foo", "Bar")
        > > RunSql ("UPDATE.... bla bla bla)
        > > Next
        > >
        > > But the exact same elements of the Array are contained in a Table -- Is
        > > there some way to use a query to set the Array, or some other way to[/color][/color]
        avoid[color=blue][color=green]
        > > using the Array?
        > >
        > > Here's some pseudo code ...
        > >
        > > For Each varFnm in qryStuff
        > > RunSql("UPDATE. .. bla bla bla)
        > > Next
        > >
        > > Can someone suggest a correct way to do this?
        > >
        > > Thanks in advance!
        > >
        > >[/color]
        >
        >
        >[/color]


        Comment

        • DFS

          #5
          Re: Simple Array question

          "deko" <dje422@hotmail .com> wrote in message
          news:2CGzb.3241 3$9Z4.21776@new ssvr29.news.pro digy.com...[color=blue]
          > thanks for the reply.
          >
          > hmmmm... that may work, but the best way to solve my problem would be to
          > programmaticall y populate the Array from a query/table.
          >
          > I've discovered that I need to keep the For Each loop in tact. In generic
          > terms:
          >
          > For Each varAbc in Array("a", "b", "c")
          > do something
          > Next
          >
          > There is a reason I need varAbc ...
          >
          > Again, there is a Table that has the exact elements a, b,c -- what I'd[/color]
          like[color=blue]
          > to do is programmaticall y dump the contents of that table into the array.[/color]

          How about something like this?

          Dim i,,j

          'CLEAR ARRAY
          for i = 0 to varABC(UBound)
          for j = 0 to varABC(UBound)
          varABC(i,,j) = ""
          next j
          next i


          'POPULATE ARRAY FROM TABLE
          set rs = db.openRecordse t("Table")

          if rs.recordcount > 0 then
          do until rs.eof

          for i = 0 to rs.Fields.count - 1
          varABC(i,,j) = rs(i)
          next i
          j = j + 1

          rs.movenext
          loop
          endif

          rs.close









          [color=blue]
          > "Scott McDaniel" <scott@no.spam. thedatabaseplac e.net> wrote in message
          > news:l_WdnevXca ZvtlKiRVn-hg@comcast.com. ..[color=green]
          > > If the elements are in a table, open a recordset and step through that:
          > >
          > > Dim rst As ADODB.Recordset
          > >
          > > Set rst = New ADODB.Recordset
          > >
          > > rst.OPen "SQL STatement Here", currentproject. connection
          > >
          > > Do Until rst.EOF
          > > RunSQL("UPDATE ... bla bla bla")
          > > Loop
          > >
          > > rst.Close
          > > Set rst = Nothing
          > >
          > > "deko" <dje422@hotmail .com> wrote in message
          > > news:gdFzb.6549 4$ZA2.48798@new ssvr25.news.pro digy.com...[color=darkred]
          > > > I use a For Each... Next loop like this:
          > > >
          > > > For Each varFnm In Array("This", "That", "OtherThing ", "Foo", "Bar")
          > > > RunSql ("UPDATE.... bla bla bla)
          > > > Next
          > > >
          > > > But the exact same elements of the Array are contained in a Table --[/color][/color][/color]
          Is[color=blue][color=green][color=darkred]
          > > > there some way to use a query to set the Array, or some other way to[/color][/color]
          > avoid[color=green][color=darkred]
          > > > using the Array?
          > > >
          > > > Here's some pseudo code ...
          > > >
          > > > For Each varFnm in qryStuff
          > > > RunSql("UPDATE. .. bla bla bla)
          > > > Next
          > > >
          > > > Can someone suggest a correct way to do this?
          > > >
          > > > Thanks in advance!
          > > >
          > > >[/color]
          > >
          > >
          > >[/color]
          >
          >[/color]


          Comment

          • Scott McDaniel

            #6
            Re: Simple Array question

            Not sure what you mean by "programaticall y populate the array" ... I assumed
            that open and filling an ADODB recordset would fill the bill ...

            Investigate the GetRows method of the ADO recordset object ...

            "deko" <dje422@hotmail .com> wrote in message
            news:2CGzb.3241 3$9Z4.21776@new ssvr29.news.pro digy.com...[color=blue]
            > thanks for the reply.
            >
            > hmmmm... that may work, but the best way to solve my problem would be to
            > programmaticall y populate the Array from a query/table.
            >
            > I've discovered that I need to keep the For Each loop in tact. In generic
            > terms:
            >
            > For Each varAbc in Array("a", "b", "c")
            > do something
            > Next
            >
            > There is a reason I need varAbc ...
            >
            > Again, there is a Table that has the exact elements a, b,c -- what I'd[/color]
            like[color=blue]
            > to do is programmaticall y dump the contents of that table into the array.
            >
            >
            > "Scott McDaniel" <scott@no.spam. thedatabaseplac e.net> wrote in message
            > news:l_WdnevXca ZvtlKiRVn-hg@comcast.com. ..[color=green]
            > > If the elements are in a table, open a recordset and step through that:
            > >
            > > Dim rst As ADODB.Recordset
            > >
            > > Set rst = New ADODB.Recordset
            > >
            > > rst.OPen "SQL STatement Here", currentproject. connection
            > >
            > > Do Until rst.EOF
            > > RunSQL("UPDATE ... bla bla bla")
            > > Loop
            > >
            > > rst.Close
            > > Set rst = Nothing
            > >
            > > "deko" <dje422@hotmail .com> wrote in message
            > > news:gdFzb.6549 4$ZA2.48798@new ssvr25.news.pro digy.com...[color=darkred]
            > > > I use a For Each... Next loop like this:
            > > >
            > > > For Each varFnm In Array("This", "That", "OtherThing ", "Foo", "Bar")
            > > > RunSql ("UPDATE.... bla bla bla)
            > > > Next
            > > >
            > > > But the exact same elements of the Array are contained in a Table --[/color][/color][/color]
            Is[color=blue][color=green][color=darkred]
            > > > there some way to use a query to set the Array, or some other way to[/color][/color]
            > avoid[color=green][color=darkred]
            > > > using the Array?
            > > >
            > > > Here's some pseudo code ...
            > > >
            > > > For Each varFnm in qryStuff
            > > > RunSql("UPDATE. .. bla bla bla)
            > > > Next
            > > >
            > > > Can someone suggest a correct way to do this?
            > > >
            > > > Thanks in advance!
            > > >
            > > >[/color]
            > >
            > >
            > >[/color]
            >
            >[/color]


            Comment

            Working...