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

Try removing these two lines from your GetRS function

oRS.Close
Set oRS = Nothing

Tim
--
Tim Williams
Palo Alto, CA


"Dash" wrote in message
...
Tim
wasn't sure how best to 'post' the code so I have attached the xl file and
pasted the the vba code below.
Your help is much appreciated.
Dash

Public Sub DisConnTest() 'based on 4guys example
Dim RS As ADODB.Recordset
Dim RcdCnt As Long
'call the function
strSQL = "SELECT * FROM Authors"
Set RS = GetRS(strSQL)
'What has to happen here in order to access and operate on RS??
'RS.Open

RcdCnt = RS.RecordCount
End Sub

Function GetRS(strSQL)
'this function returns a disconnected RS

'Set some constants
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockBatchOptimistic = 4

'Declare our variables
' Dim oConn
' Dim oRS
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset

'Open a connection
Set oConn = New ADODB.Connection
' Set oConn = Server.CreateObject("ADODB.Connection")
' oConn.Open mydsn, Sean, Grimaldi
oConn.Open "PubTest", "test1", "test1"
'Create the Recordset object
' Set oRS = Server.CreateObject("ADODB.Recordset")
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseClient

'Populate the Recordset object with a SQL query
oRS.Open strSQL, oConn, adOpenStatic, adLockBatchOptimistic

'Disconnect the Recordset
Set oRS.ActiveConnection = Nothing

'Return the Recordset
Set GetRS = oRS

'Clean up...
oConn.Close
oRS.Close
Set oConn = Nothing
Set oRS = Nothing
End Function




"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
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