query data, pass to an array then write back to table

[scottian]

Can i query a table then pass that data to an array, then check a field and if it meets certain criteria update another field with either true or false then move to the next record looping through all the records in the array.

at present im using a query which retrieves the top record does the validation then re-runs the top record query, this takes an age if the file i need to check is quite large.

any help much appreciated

"My God! It's full of stars...

 

[PHV]

You may consider playing with a Recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Waud]

As PHV says you'd be better of using a recordset

or you could use an update query which should be quicker

 

[scottian]

I think need to elaborate more.
I have a regular file that i recieve daily. It contains quite a few records, the records contain one parent record which may have a number of child records, these follow immediately in the file until a new parent record is reached.
however the records themselves contain no common link thefore i have to create one so the data doesnt become jumbbled up. i use an SQL update to insert the link which is based on a query which selects the. but this takes ages as i cannot work out a way to have a query work its way through the full set of records updating as it goes.

"My God! It's full of stars...

 

[formerTexan]

Scottian,

Perhaps you can post your Update query. Someone may suggest an improvement. Also, how do you differentiate between the parent and child records in the intial file and how many records does this file typically contain?

Cheers,
Bill

 

[VBslammer]

I would read in the file's contents using the FileSystemOjbect, then split the text into an array at the carriage returns. Then it's just a matter of looping through the array and using a variable to determine when the parent name or id changes.

It would help if you posted a sample portion of the file.



"I'm sorry Dave, I'm afraid I can't do that...