Loads Openrecordset all records? (All)

Topic

New Reply

Hey,

I want to open a recordset for processing in VBA. I must be able to edit. Sort order is not important. Which is the fastest type to choose, for a query (“dbSnapshot+dbForwardOnly”?) and for a table? And: does a dbSnapshot+dbForwardOnly automatically load all records?

Background: for the follow up of the digitally available documents in files/cases we are processing, I register all incoming documents in a certain table (name, path, additional information). Now I’m trying to organise the file & document management a bit (as I’ll among others soon have to set up a mirror copy of our documents at an extranet, sharing them with people outside our organisation,…). One of the issues is the digital document location consistency: I want to check if all documents registered are still ‘at their place’ on our network,… So I wrote a procedure that loops through the table, execute the dir function and ‘flags’ all inconsistent entries (using a boolean field in the documents table). Right now, I’ve 3-4000 records, but probably this will increase further in the future. Now I’m wondering how I get the best speed-performance out of my up the ‘consistency control’.
Remark1: right now, I operate on a query, but if it’ll help speed up the process, I can use the table as a recordset too.
Remark2: I usually load all records in a recordset by using movelast – movefirst before I start, but I don’t know if this is necessary, nor possible in a dbForwardOnly type of recordset.
I searched the net & help for a while, but didn’t got all pieces of the puzzle together to know which was the best option to choose…

I hope I made myself a bit clear… Thanks for any reply!

Hasse

Reply | Quote

Replies

Neither one of those recordsets is going to be editable because snapshots are not editable. If you want to make changes to the data in the recordset, you must use a dynaset, not a snapshot. Since you move through a recordset one record at a time, I don’t understand the question about loading “all records”. The only reason to do a MoveLast and MoveFirst is to get a record count, but it isn’t necessary otherwise, and you can’t do it at all in a forward only recordset.

If you are going to examine every record every time you run this, then speed is going to be problematic as the database gets larger. If there is a datestamp in your record for indicates the last change date, it would be more effiecient to check to see whether the record has been updated since the last time you ran the routine and only examine records that have changed or that have been added since your last examination.

Reply | Quote

Charlotte,
so, a dynaset it will be… and thanks for the tip to use that datastamp (which I added already for other purposes…)
I asked the loading-all-records-question because I knew about the recordcount issue you mentioned. I wondered that if not all records are loaded for the recordcount, this might as well be the case for any other task one might want to perform on a recordset. And I needed to be sure all records are processed… But if I understand it right, that won’t be a problem then.
Hasse

Reply | Quote

Charlotte,
so, a dynaset it will be… and thanks for the tip to use that datastamp (which I added already for other purposes…)
I asked the loading-all-records-question because I knew about the recordcount issue you mentioned. I wondered that if not all records are loaded for the recordcount, this might as well be the case for any other task one might want to perform on a recordset. And I needed to be sure all records are processed… But if I understand it right, that won’t be a problem then.
Hasse

Reply | Quote

Neither one of those recordsets is going to be editable because snapshots are not editable. If you want to make changes to the data in the recordset, you must use a dynaset, not a snapshot. Since you move through a recordset one record at a time, I don’t understand the question about loading “all records”. The only reason to do a MoveLast and MoveFirst is to get a record count, but it isn’t necessary otherwise, and you can’t do it at all in a forward only recordset.

If you are going to examine every record every time you run this, then speed is going to be problematic as the database gets larger. If there is a datestamp in your record for indicates the last change date, it would be more effiecient to check to see whether the record has been updated since the last time you ran the routine and only examine records that have changed or that have been added since your last examination.

Reply | Quote