+ Reply to Thread
Results 1 to 4 of 4

Querytable results into vba Recordset

  1. #1
    Aaron
    Guest

    Querytable results into vba Recordset

    I wish to use the results of a querytable update in the local worksheet as
    the basis for loading up / populating a VBA Recordset WITHOUT needing to
    re-hit the database for the information.

    I am taking this approach so as to retain visibilty of the raw data and have
    it on hand, in entirety, to mannually inspect / analyse if my automated logic
    and integrity checks in VBA highlight that there is a need to delve deeper
    into the integrity of the data.

    There is likely to be great amounts of data in a real-time warehousing
    environment so I want to avoid placing extra loads on the database / bandwith.

    Here's what I've tried and been stumped at :

    The vba refresh :

    Worksheets("qryInventoryData").QueryTables(1).Refresh BackgroundQuery:=False

    And here's the failing attempt to populate the VBA recordset :

    Set rsData = Worksheets("qryInventoryData").QueryTables(1).Recordset

    Does anyone think this iss too much to expect ? Is there another way to
    skin tis cat ?

    Aaron

  2. #2
    George Nicholson
    Guest

    Re: Querytable results into vba Recordset

    Failed how?
    With an Error message? What is it? (Type mismatch?)
    How is rsData dimmed? (As ADODB.Recordset? the example in Help implies
    that's what Recordset property expects.)

    HTH,
    --
    George Nicholson

    Remove 'Junk' from return address.


    "Aaron" <[email protected]> wrote in message
    news:[email protected]...
    >I wish to use the results of a querytable update in the local worksheet as
    > the basis for loading up / populating a VBA Recordset WITHOUT needing to
    > re-hit the database for the information.
    >
    > I am taking this approach so as to retain visibilty of the raw data and
    > have
    > it on hand, in entirety, to mannually inspect / analyse if my automated
    > logic
    > and integrity checks in VBA highlight that there is a need to delve deeper
    > into the integrity of the data.
    >
    > There is likely to be great amounts of data in a real-time warehousing
    > environment so I want to avoid placing extra loads on the database /
    > bandwith.
    >
    > Here's what I've tried and been stumped at :
    >
    > The vba refresh :
    >
    > Worksheets("qryInventoryData").QueryTables(1).Refresh
    > BackgroundQuery:=False
    >
    > And here's the failing attempt to populate the VBA recordset :
    >
    > Set rsData = Worksheets("qryInventoryData").QueryTables(1).Recordset
    >
    > Does anyone think this iss too much to expect ? Is there another way to
    > skin tis cat ?
    >
    > Aaron




  3. #3
    Aaron
    Guest

    Re: Querytable results into vba Recordset

    Goerge,

    Thanks and sorry for not enough info, here's the rest (or some more at
    least) :


    Dim rsData As ADODB.Recordset

    ' Refresh the worksheet data
    Worksheets("qryInventoryData").QueryTables(1).Refresh BackgroundQuery:=False

    ' Try load up the recordest in VBA witht he data refresh
    Set rsData = Worksheets("qryInventoryData").QueryTables(1).Recordset


    -----< end of the extract code

    So the actual error I get is 1004 - Not a DAO Object

    Is my approach wrong ?

    Aaron

    "George Nicholson" wrote:

    > Failed how?
    > With an Error message? What is it? (Type mismatch?)
    > How is rsData dimmed? (As ADODB.Recordset? the example in Help implies
    > that's what Recordset property expects.)
    >
    > HTH,
    > --
    > George Nicholson
    >
    > Remove 'Junk' from return address.
    >
    >
    > "Aaron" <[email protected]> wrote in message
    > news:[email protected]...
    > >I wish to use the results of a querytable update in the local worksheet as
    > > the basis for loading up / populating a VBA Recordset WITHOUT needing to
    > > re-hit the database for the information.
    > >
    > > I am taking this approach so as to retain visibilty of the raw data and
    > > have
    > > it on hand, in entirety, to mannually inspect / analyse if my automated
    > > logic
    > > and integrity checks in VBA highlight that there is a need to delve deeper
    > > into the integrity of the data.
    > >
    > > There is likely to be great amounts of data in a real-time warehousing
    > > environment so I want to avoid placing extra loads on the database /
    > > bandwith.
    > >
    > > Here's what I've tried and been stumped at :
    > >
    > > The vba refresh :
    > >
    > > Worksheets("qryInventoryData").QueryTables(1).Refresh
    > > BackgroundQuery:=False
    > >
    > > And here's the failing attempt to populate the VBA recordset :
    > >
    > > Set rsData = Worksheets("qryInventoryData").QueryTables(1).Recordset
    > >
    > > Does anyone think this iss too much to expect ? Is there another way to
    > > skin tis cat ?
    > >
    > > Aaron

    >
    >
    >


  4. #4
    Aaron
    Guest

    Re: Querytable results into vba Recordset

    I may have concluded to myself that my train of thought was wrong.

    Given that I wanted to hit the database ONCE to get a local copy of the raw
    data into a worksheet, via a data refresh of a querytable, my thought is
    that a recrdset is the wrong vehicle to iterate through the returned (local)
    data set within the worksheet.

    I now think that the Recordset / Database / Connection approach is all
    oriented around a direct connection to a database and I can't simply use the
    structure of a recordset and it's niceties as if it were an array
    representation the data I already have locally via :
    Worksheets("qryInventoryData").QueryTables(1).Refresh BackgroundQuery:=False.

    Does anyone want to confirm that I was wrong in my first train of thought or
    that I should still be able to use recordset on this local copy of the data.

    Thanks
    Aaron

    "Aaron" wrote:

    > Goerge,
    >
    > Thanks and sorry for not enough info, here's the rest (or some more at
    > least) :
    >
    >
    > Dim rsData As ADODB.Recordset
    >
    > ' Refresh the worksheet data
    > Worksheets("qryInventoryData").QueryTables(1).Refresh BackgroundQuery:=False
    >
    > ' Try load up the recordest in VBA witht he data refresh
    > Set rsData = Worksheets("qryInventoryData").QueryTables(1).Recordset
    >
    >
    > -----< end of the extract code
    >
    > So the actual error I get is 1004 - Not a DAO Object
    >
    > Is my approach wrong ?
    >
    > Aaron
    >
    > "George Nicholson" wrote:
    >
    > > Failed how?
    > > With an Error message? What is it? (Type mismatch?)
    > > How is rsData dimmed? (As ADODB.Recordset? the example in Help implies
    > > that's what Recordset property expects.)
    > >
    > > HTH,
    > > --
    > > George Nicholson
    > >
    > > Remove 'Junk' from return address.
    > >
    > >
    > > "Aaron" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >I wish to use the results of a querytable update in the local worksheet as
    > > > the basis for loading up / populating a VBA Recordset WITHOUT needing to
    > > > re-hit the database for the information.
    > > >
    > > > I am taking this approach so as to retain visibilty of the raw data and
    > > > have
    > > > it on hand, in entirety, to mannually inspect / analyse if my automated
    > > > logic
    > > > and integrity checks in VBA highlight that there is a need to delve deeper
    > > > into the integrity of the data.
    > > >
    > > > There is likely to be great amounts of data in a real-time warehousing
    > > > environment so I want to avoid placing extra loads on the database /
    > > > bandwith.
    > > >
    > > > Here's what I've tried and been stumped at :
    > > >
    > > > The vba refresh :
    > > >
    > > > Worksheets("qryInventoryData").QueryTables(1).Refresh
    > > > BackgroundQuery:=False
    > > >
    > > > And here's the failing attempt to populate the VBA recordset :
    > > >
    > > > Set rsData = Worksheets("qryInventoryData").QueryTables(1).Recordset
    > > >
    > > > Does anyone think this iss too much to expect ? Is there another way to
    > > > skin tis cat ?
    > > >
    > > > Aaron

    > >
    > >
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1