I'm trying to import a file that's being updated with information from an RS232 data logger. While there is no information updating the file, it's safe to import the file and then empty it which can be done from DOS copy commands, essentially I do the following:
Make a snapshot of the existing data into an upload.txt file, import it into Access, overwrite the logging.txt file with a blank copy, overwrite the snapshot with a blank copy.
The reason I'm doing this in DOS is that the import routine fails within Access saying that the logging.txt file is already in use and can't be accessed (I'm assuming that Access wants to take full control of the file, or opens it in a particular way hence the batch file approach)
The problem I'm experiencing is that Macros allow you to run batch files, but the macro continues to run immediately after the batch file is triggered to run, so in this particular case, the information in the logging.txt file isn't copied quick enough to the upload.txt file before Access tries to import it, meaning that Access imports no information (empty file) and then the macro of course runs the second batch file which then empties both the logging.txt file and the upload.txt file.
Two options at the moment if suggestions are willing and available:
1) Somehow stop Access trying to take full control of the text file and allow it to import the information and then overwrite the file with a blank copy.
2) Make Access wait until the batch file has finished before proceeding with the macro.
A couple of thoughts cross my mind for option 2, a timed loop to wait for the batch file, although no guarantees on timing on that one and also perhaps having the batch file create a tag.txt file after the copy is complete which Access looks for before continuing the Macro (maybe have to go into VBA coding for that) then delete the tag file after import routine has finished (the tag file of course will not be in use, so deletion from within Access would be possible)
It's always better to bounce these ideas round the board before heading down one particular road.
Thanks everyone.
Make a snapshot of the existing data into an upload.txt file, import it into Access, overwrite the logging.txt file with a blank copy, overwrite the snapshot with a blank copy.
The reason I'm doing this in DOS is that the import routine fails within Access saying that the logging.txt file is already in use and can't be accessed (I'm assuming that Access wants to take full control of the file, or opens it in a particular way hence the batch file approach)
The problem I'm experiencing is that Macros allow you to run batch files, but the macro continues to run immediately after the batch file is triggered to run, so in this particular case, the information in the logging.txt file isn't copied quick enough to the upload.txt file before Access tries to import it, meaning that Access imports no information (empty file) and then the macro of course runs the second batch file which then empties both the logging.txt file and the upload.txt file.
Two options at the moment if suggestions are willing and available:
1) Somehow stop Access trying to take full control of the text file and allow it to import the information and then overwrite the file with a blank copy.
2) Make Access wait until the batch file has finished before proceeding with the macro.
A couple of thoughts cross my mind for option 2, a timed loop to wait for the batch file, although no guarantees on timing on that one and also perhaps having the batch file create a tag.txt file after the copy is complete which Access looks for before continuing the Macro (maybe have to go into VBA coding for that) then delete the tag file after import routine has finished (the tag file of course will not be in use, so deletion from within Access would be possible)
It's always better to bounce these ideas round the board before heading down one particular road.
Thanks everyone.
