copy records from a recordset

[fkauffman]

In my data-object there is an ADO-recordset with a lot of records, but I just need one or two to send to my business-object.

What's the best way* to copy just some records from a large recordset?

* also just a good way will do

Fedor

[UKPhil]

If you can't define the records that you want by using a SQL query then 2 ways that I've used before is to either fill an array with the records that you want and then empty them out to what-ever business object you are using, or to create a temporary recordset using MSPerist and copying the records across one field/record at a time from the large recordset. Both are quite fast.

If you think either of these might work and you need any examples let me know and I'll post some for you.

Phil

[JHausmann]

Marty's partial to using Collections (he uses them far more than I do)

[fkauffman]

Hi Phil,

Thanks for your reply,
I'm specially interrested in using the temporary recordset. Could you post an example?

Fedor

[UKPhil]

Hi Fedor,

Here's an example of how I use ADO to persist a recordset.

Dim rst As New ADODB.Recordset

'Specify Client cursorlocation as there is no backend database to get the information
rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic

'Create the fields that you want in your recordset. I've made all mine variables and I don't allow Null values but the choice is yours
rst.Fields.Append "CrewCode", adVarChar,255,adFldIsNullable
rst.Fields.Append "RostaDate", adVarChar,255,adFldIsNullable
rst.Fields.Append "ChangeDate",adVarChar,255,adFldIsNullable
rst.Fields.Append "ChangeTime",adVarChar,255,adFldIsNullable
rst.Fields.Append "LogType", adVarChar, 255, adFldIsNullable
rst.Fields.Append "ActionCode",adVarChar,255,adFldIsNullable
rst.Open

'Save the created recordset to a location using the Persist innate connection. You can give the file name any file extension you want
rst.Save "C:\myfolder\myfile.rst", adPersistADTG

'Close recordset
rst.Close

Once you have created the temporary recordset, you can use it like any recordset. Here is an example where I've already opened my large recordset and want to copy records across to the temp one:

Dim rst as new ADODB.Recordset

'Open the temporary recordset using the innate MSPersist provider
rst.Open "C:\myfolder\myfile.rst", "Provider=MSPersist"

'Once I've come to a record I want to copy across then:
rst.AddNew
rst.Fields("CrewCode") = rstLarge.Fields("CrewCode")
rst.Fields("RostaDate") = rstLarge.Fields("RostaDate")
rst.Fields("ChangeDate") = rstLarge.Fields("ChangeDate")
rst.Fields("ChangeTime") = rstLarge.Fields("ChangeTime")
rst.Fields("LogType") = rstLarge.Fields("LogType")
rst.Fields("ActionCode") = rstLarge.Fields("ActionCode")
rst.Update

'Once finished, save the temp recordset
rst.Save "C:\myfolder\myfile.rst", adPersistADTG


I hope this helps you. For some detailed info, I learnt about using the MSPerist recordset through an article in Australia's Offical VB Site: http://www.avdf.com/
Just search the Jan/Feb 1999 articles for "MSPerist" and it gives the whole enchillada.

Good luck.

Phil

[rod]

thanks for the code phil!!!!!

i've been searching for vb codes on ado (i was using dao 3.51 for years) both on the internet and other print materials for something like the one you gave. i've acquired numerous books on that subject but gave me no meaningful solutions and instead i got a lot of questions and problems as well from them.

[Clunietp]

you don't even need to save it as a file....create the recordset in your data objects and return it to your business objects (just like any other recordset)

that way, you won't have to worry about writing to the file system, file concurrency, etc. with your multi-tier/multi-user application

[fkauffman]

Thanks for all your help,
I used the temporary recordset and it works fine.

(saving the recordset to a file is handy for an application with more concurrent users, so I used the example without the save, thanks Clunietp)

Fedor