Recomendations for Opening Recordset Object & Resources

[Amanda V]

Hello,

I am making a VB6 application to automate an MS Access database.

One can only use the .RecordCount property with certain cursortypes,
but those cursor types do not let me use the rst.MoveFirst property as
well.

Would it be very costly in terms of resources to just re-open the
recordset again instead of using rst.MoveFirst to return to the
beginning of the recordset? Or is there a combination of rst properties
that will allow me to know how many records were returned in the
recordset AND still be able to easily navigate forward and backwards
through my recordset?

I've already tried using adOpenDynamic and it returns -1

Thanks!

Amanda

P.s. Also looking for an article or link with tips on a resource
efficient application, that would be great. Tips like, "instead of
using a connection string, pass a connection object when opening
multiple recordsets"... etc... My overall goal is to get fast
performance by not eating up resources with costly code (but whose
isn't?

 

[RoyVidar]

Hello,

I am making a VB6 application to automate an MS Access database.

One can only use the .RecordCount property with certain cursortypes,
but those cursor types do not let me use the rst.MoveFirst property
as well.

Would it be very costly in terms of resources to just re-open the
recordset again instead of using rst.MoveFirst to return to the
beginning of the recordset? Or is there a combination of rst
properties that will allow me to know how many records were returned
in the recordset AND still be able to easily navigate forward and
backwards through my recordset?

I've already tried using adOpenDynamic and it returns -1

Thanks!

Amanda

P.s. Also looking for an article or link with tips on a resource
efficient application, that would be great. Tips like, "instead of
using a connection string, pass a connection object when opening
multiple recordsets"... etc... My overall goal is to get fast
performance by not eating up resources with costly code (but whose
isn't?

I'm not sure you'll get dynamic on Jet tables, I think it will coerce
it to another cursor type.

I think you should probably get correct recordcount when using static
or keyset cursor, or you could use a client side cursor, which will
also return a static cursor.

Here's a short article, though for a different environment, but
http://www.adopenstatic.com/faq/recordcounterror.asp

I'm still inclined to not trust the .recordcount, and will do a
select count(*) ... with the same where clause if I really, really
need the recordcount. If it's just to find out whether the recordset
contains records, you could just check

if ((not rs.bof) and (not rs.eof)) then
' contains records
end if

or

do while (not rs.eof)

depending on situation.

 

[aaron.kempf]

or if you were using SQL Server; then you could just PEEK at the value
using a query to sysindexes

that's 1000 times faster than scanning an Access table with a Select
Count(*)

lol