Using Access to make decisions based on a text file

[techsupport3977]

I have a bunch of computers that have a data acquisition program and the program uses a file (.qma this is a text file format) to determine whether or not data records need to be submitted to a database. At the top of the file shows the KEY field. Below the key field, it will be blank (all records safely transmitted or the file may not exist which means all data has been sent) or it will keep a log of the key field entries of the records that have not been transmitted. I would like to have access or SQL look at this file(s) and determine if there are records to be sent. To me if there are records to be sent, then there is a problem with the computer.

My question is how can I create a query in Access or a stored procedure in SQL to look up a file that may or may not exist, review the contents and decide if there is data from line 2 of the text file? I would like the query to simply say 1 for problem, 0 for no problem.

 

[alvechurchdata]

If you could find an ODBC driver for this qma format you could link it in as an external data source but that sounds unlikely. I think I would write a function which attempted to open the file in the old-fashioned BASIC way:

Open myFile For Input as #1

then read the contents line by line. If the open failed then I'd catch the error and report that the file didn't exist.

By the way, I'm writing from memory so check my syntax on the Open command.

Geoff Franklin

http://www.alvechurchdata.co.uk