Instead of returning all of the records only so you can count them, why not just count them in SQL ?

SELECT count(dbo_Product.[Product_ID]) AS NumRecs...

Then :
rstNum = rstData.Fields("NumRecs").value

and use adOpenStatic and not adOpenKeyset.

That should work unless you cut out some code in that second section and you're doing something else with the second RS.



--
Tim Williams
Palo Alto, CA


"Alex" wrote in message ...
Thank you very much for your response, Tim.

Now, on that 'other' computer it's saying something as 'ODBC drivers cannot
provide required functionality".
I think the problem is with the adOpenKeyset in the recordset. I cannot be
accepted on that 'other' computer.
Do you have ani idea how to get rid of it?

Thanks

"Tim Williams" wrote:

Define "not working". Does it error: if so what is the error number and description ?

Tim

"Alex" wrote in message ...
I have an issue with the different recordsets from the same DB. I'm trying to
establish one connection and open and close one recordset and after that to
open and close another recordset. On my computer everything is working very
well. However, on another computer the only first recordset is working.

Dim Cnxn As ADODB.Connection
Dim rstData As ADODB.Recordset
Set Cnxn = New ADODB.Connection
strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;"
Cnxn.Open strCnxn
Set rstData = New ADODB.Recordset
rstData.Open "SELECT ...", Cnxn, adOpenStatic

Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData

rstData.Close
Set rstData = Nothing
' *** This recordset is not working on another computer but working very
well on mine******
Set rstData = New ADODB.Recordset
rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _
& " FROM dbo_Product" _
& " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND
dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset

rstNum = rstData.RecordCount

rstData.Close
Set rstData = Nothing

Another similar thing that isn't working on another computer but working on
mine is:
I have a form (the same app) and I'm establishing the connection on the form
opening and close it with the form unloading.The recordset isn't working
there. The connection name is the same Cnxn. I've tried to give different
names to the connections and recordsets but it doesn't help.

Please, advise what can be wrong with all this stuff.

Thanks