Hi All
Have been using Excel to generate custom reports on data from remote
databases quite happily.
I now have a need to avoid making lots of calls to the DB by downloading the
complete table concerned and using a rsABC.Filter in a loop to do a
different multi-column FIND in each iteration to get the various records I
need.
However any statement as ' rsABC.Filter "..." ' throws a compile error
'Invalid Use of property' with the '.Filter' highlighted.


Any suggestions as to what I am missing will be much appreciated.

TIA

Dash

Make sure you set the RS "cursorlocation" to "client". I forget the exact
syntax.
Once the recordset is complete you can then disconnect it from the DB
(unless you're doing updates in which case you might be better off leaving
it connected).

Good code sample he
http://www.4guysfromrolla.com/webtech/080101-1.shtml


Tim



"Dash" wrote in message
...
Hi All
Have been using Excel to generate custom reports on data from remote
databases quite happily.
I now have a need to avoid making lots of calls to the DB by downloading
the complete table concerned and using a rsABC.Filter in a loop to do a
different multi-column FIND in each iteration to get the various records I
need.
However any statement as ' rsABC.Filter "..." ' throws a compile error
'Invalid Use of property' with the '.Filter' highlighted.


Any suggestions as to what I am missing will be much appreciated.

TIA

Dash

Thanks Tim but I am still missing something!!
I used the 4guys example for a disconnected RS - as it in Excel VBA I had
to 'type' the Dim statements.
If I try to use the returned RS I get error msg. 'Operation not allowed when
the object is closed.'

If I comment out the lines in the 4guys function closing the oRS then the
returned rs is 'open'
i.e. RS.RecordCount returns without error.
but RS.Filter throws the same compile error 'invalid property assignment'

I tried typing the returned RS as Recordset, ADODB.Recordset and Variant

Anybody got any further thoughts??

Dash


"Tim Williams" <saxifrax at pacbell dot net wrote in message
...
Make sure you set the RS "cursorlocation" to "client". I forget the exact
syntax.
Once the recordset is complete you can then disconnect it from the DB
(unless you're doing updates in which case you might be better off leaving
it connected).

Good code sample he
http://www.4guysfromrolla.com/webtech/080101-1.shtml


Tim



"Dash" wrote in message
...
Hi All
Have been using Excel to generate custom reports on data from remote
databases quite happily.
I now have a need to avoid making lots of calls to the DB by downloading
the complete table concerned and using a rsABC.Filter in a loop to do a
different multi-column FIND in each iteration to get the various records
I need.
However any statement as ' rsABC.Filter "..." ' throws a compile error
'Invalid Use of property' with the '.Filter' highlighted.


Any suggestions as to what I am missing will be much appreciated.

TIA

Dash

Post the code which doesn't work - it's easier than having us apply any
changes you may have made to the original.

Tim

--
Tim Williams
Palo Alto, CA


"Dash" wrote in message
...
Thanks Tim but I am still missing something!!
I used the 4guys example for a disconnected RS - as it in Excel VBA I had
to 'type' the Dim statements.
If I try to use the returned RS I get error msg. 'Operation not allowed
when
the object is closed.'

If I comment out the lines in the 4guys function closing the oRS then the
returned rs is 'open'
i.e. RS.RecordCount returns without error.
but RS.Filter throws the same compile error 'invalid property assignment'

I tried typing the returned RS as Recordset, ADODB.Recordset and Variant

Anybody got any further thoughts??

Dash


"Tim Williams" <saxifrax at pacbell dot net wrote in message
...
Make sure you set the RS "cursorlocation" to "client". I forget the
exact
syntax.
Once the recordset is complete you can then disconnect it from the DB
(unless you're doing updates in which case you might be better off
leaving
it connected).

Good code sample he
http://www.4guysfromrolla.com/webtech/080101-1.shtml


Tim



"Dash" wrote in message
...
Hi All
Have been using Excel to generate custom reports on data from remote
databases quite happily.
I now have a need to avoid making lots of calls to the DB by
downloading
the complete table concerned and using a rsABC.Filter in a loop to do
a
different multi-column FIND in each iteration to get the various
records
I need.
However any statement as ' rsABC.Filter "..." ' throws a compile error
'Invalid Use of property' with the '.Filter' highlighted.


Any suggestions as to what I am missing will be much appreciated.

TIA

Dash