ADO recordset

[JonoB]

Typically, when using ADO recordsets, I like to keep everything nice and tidy. So, I always end off with the following:

cnn.close
set cnn = nothing
rsRecordset.close
set rsRecordset = nothing

However, there are times when I want to keep a recordset open all the time, especially when the underlying data only changes every day or so. By doing this, the user doesnt have to generate the recordset every time they need to use it.

However, this means that I have to keep the connection and the recordset open all the time.

Given that the recordset is currently returning about 450 records (only 2 fields per record), is this bad programming? Does keeping the connection and recordset open waste resources and hog memory?

Thanks for the help!

 

[MoLaker]

My personal preference is to close the recordset and connection just as soon as the operation is complete. I doubt if the user will notice the difference unless, perhaps, the application opens the same recordset many, many times w/o some other more time consuming screen update occurring at the same time.

Perhaps it's just my paranoia peeking through, but open recordsets don't fair well with network and/or workstation crashes. Of course, the likelihood of such crashes is sooo infinitesimal. Right?

 

[JonoB]

Well, my whole point is that the user does notice the lag....it takes about 2 - 3 seconds to generate the recordset everytime....whereas if I save the recordset then there is no lag whatsoever.

 

[BNPMike]

The normal approach would be to tune your database/query so it doesn't take so long to return such a small recordset. Can you post your SQL and tell us what fields have indexes on them?

 

[JonoB]

The recordset is generated directly from a table, and both field are indexed (one of the fields is the primary key anyway).

To be honest, its not quite 2-3 seconds (more like 1 - 2 seconds), but when you need to run the same code a couple of times in a row, then it does get a bit irritating having to wait for that 1-2 seconds everytime. Instantanteous is so much more user friendly.....

 

[Ed2020]

Are you using ADO recordsets on MS Access tables?

If so you will find DAO is far quicker with Jet tables. It certainly shouldn't be taking that long to open a recordset on so few records.

My recommendation is to stick with DAO for Jet/Access tables and use ADO for any other data access.

Ed Metcalfe.

Please do not feed the trolls.....

 

[BNPMike]

Ed2020

We don't know whether 107 million records are being scanned to get the 450 records required, or whether the selection criteria use indexes.

I've heard this "DAO is better" but I would add some Jet features are not accessible via DAO eg transactions. Also using ADO should make it easy to upsize to MS SQL Server if required.

 

[Ed2020]

Transactions are available under DAO and ADO.

I never said it was better, but it is certainly quicker.

"We don't know whether 107 million records are being scanned to get the 450 records required, or whether the selection criteria use indexes." - From the statement "The recordset is generated directly from a table, and both field are indexed (one of the fields is the primary key anyway)." I assume that there are 450 records in the table and that indexes are being used. Perhaps JonoB can confirm?

Ed Metcalfe.

Please do not feed the trolls.....

 

[JonoB]

Thanks for all the input.

Yes, I can confirm - there are only 450 records, all of which are returned.

I also confirm that the underlying table is currently MS Access.

I can also confirm that the two fields that are returned to the recordset (out of 20 in the table) are both indexed.

As to using DAO - not something that I really want to do, as moving to SQL server in the next couple of months is more than likely. Besides, it seems a step back as ADO will be supported by MS going forward and DAO will not.

Once again, what is the actual issue with leaving a recordset open until the application is closed?

 

[Ed2020]

As to using DAO - not something that I really want to do, as moving to SQL server in the next couple of months is more than likely. Besides, it seems a step back as ADO will be supported by MS going forward and DAO will not.
" - Both will be supported for quite a while yet. DAO is still the recommended data access method for Jet tables.

"Once again, what is the actual issue with leaving a recordset open until the application is closed?" - Very few, if it's done correctly.

Ed.

Please do not feed the trolls.....

 

[BNPMike]

From

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/data_mdacroadmap.asp

Obsolete technologies are technologies that have not been enhanced or updated in several product releases and that will be excluded from future product releases. Do not use these technologies when you write new applications. When you modify existing applications that are written using these technologies, consider migrating those applications to MDAC or ADO.NET.

The following components are considered obsolete:

...
Data Access Objects (DAO): DAO provides access to JET (Access) databases. This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®, and scripting languages. It was included with Microsoft Office 2000 and Office XP. DAO 3.6 is the final version of this technology. It will not be available on the 64-bit Windows operating system