Comma Delimited text file speed question

[Movian]

Hi,
We are implementing a process where by a windows service interface receives messages in real time, processes those messages and then appends them to a txt file as a new comma delimited line. As such i am trying to figure out two things.

1) Method with best speed?
Currently i am opening the file reading in a line splitting it into and array and updating tables as needed then moving onto the next line. Would it be faster to read each line into and array then close the text file and then process each line from the array in the same manner?.

2) I am torn between two methods to process this update and also have a question. I am thinking that i can either set this processing function to run on my main forms on current event. Or alternatively i could have a form sat in the background with a timer event. However this leads me to another few questions is there a way to re query the main form to display any new records added without reloading the whole form and secondly aside from that what things should i be taking into account with this process from a multi user prospective ?

As always i apriciate ANY help and thank you in advance :)

[OBP]

What "processing" are doing to the data?
It might be qucker to import the file in to a Temprorary Table and then use the form or possibly even a query (which is quicker) to do the processing.

[Movian]

well please don't hit me but due to requirements we manually force access to create a record for each patient in each table when an original patient record is created. So by processing i mean that each line read in will relate to a single patient. The processing involves using a seek on our tblPatient to find out if the patient already exists in the system. If The patient DOES exist then it simply updates the record through DAO with the new values pulled in from the comma delmited file. If the patiend does NOT exist then the system calls a sub the systematicly creates records for that patient in the required tables and also sets some user definalbe lookup values called from a defaultvalue lookuptable (you may remember my post about that one).

[OBP]

I should think under those circumstances that a Temporary Table with an Append Query, an Update Query and a Delete Query (for the temp table) would be much faster than using VBA, queries tend to be a lot quicker than VBA.
It is then more a case of can you get the data in the format that you need in a Query or Module Query?

[Movian]

Ok well, im not that familiar with append queries, update queries or delete queries ^_^;;

So i will probably leave it as is and then move to that structure over time as what i have at the moment is working i was just trying to optimize.
So that essentially covers point 1. (i will just stick with my method for now).

Any thoughts on point 2 ?

[OBP]

Movian, Append, Update and Deelete queries are very simple, providing that you have some form of "No Duplicates" set up then you can create a simple select query and then convert it to any of the other 3 using the query design main menu "Query" menu.
As to question 2, which is actually 2 questions :) I would consider an Autoexec opened hidden form to the processing and when it has finished, assuming that it has added any records I would have a Message box ask the User if they would like to see the latest data.

[Movian]

hmmm, i like that process a lot. with the msgbox I am presuming i would just reload the form ? or is there a re query function i can use to just show the new options? Similar to a re query for combo boxes based on SQL Queries

[OBP]

Yes you can use the Requery function, I think it is possible to call it from the hidden form, if not you could pass the data to the Open main form using a Public variable to trigger the message box and requery from within the main form.

[Movian]

you know i just noticed that if you are in the main form of the system and the invisible background form starts processing the txt file then the whole system kind of locks up until its finished processing :S (possibly caused by the direct DAO access to the tables.)

IS there a way i can prevent this ? Or will switching over the SQL method solve this issue ?

[OBP]

I don't know, does it do the same from the Main Form?
I know queries on a temp table shouldn't do it, so SQL shouldn't either.

[Movian]

ok looks like i will need to go in that direction then. Any resources you know of i could look at to get information on setting up these queries ? And yes it does the same thing no matter which form it is called from.

And yes you can re query a form from a hidden form :)

[OBP]

Movian, if you can supply some dummy data and a blank table or tables I can create them for you. But honestly they are very easy to create with the Query Wizards.