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).Refr esh BackgroundQuery:=False

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

Set rsData = Worksheets("qryInventoryData").QueryTables(1).Reco rdset

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

Aaron

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" wrote in message
...
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).Refr esh
BackgroundQuery:=False

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

Set rsData = Worksheets("qryInventoryData").QueryTables(1).Reco rdset

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

Aaron

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).Refr esh BackgroundQuery:=False

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


-----< 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" wrote in message
...
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).Refr esh
BackgroundQuery:=False

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

Set rsData = Worksheets("qryInventoryData").QueryTables(1).Reco rdset

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

Aaron

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).Refr esh 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).Refr esh BackgroundQuery:=False

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


-----< 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" wrote in message
...
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).Refr esh
BackgroundQuery:=False

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

Set rsData = Worksheets("qryInventoryData").QueryTables(1).Reco rdset

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

Aaron