Question Importing A File

[alexfwalker]

I have an unusual problem relating to the import of files.

I need to set up a database to pick up a file every few minutes from a network location. I won't profess to know exactly how to do this yet, but I'm familiar with file imports and am confident that this won't be too trick in itself.

However, the real problem that I can foresee is Access attempting to import a file which is only part written. A sales order processing system will be writing a potentially large file to the network every few minutes or so, which the Access database will pick up. Typically it takes 5-10 seconds for these files to be written, so there is a more than high chance that at some point the import process will run at a time when the file that it's importing is part written.

How can I prevent this?!

I had considered a process whereby the SOP system begins writing the file as a .tmp file, then renames it to .txt once the file is complete, but the system doesn't have the capability to rename once written to the network.

 

[DJkarl]

I don't know of an absolute way to do this, but what I've done in the past is use the FileLen function to get the size of a file, wait a few seconds and hit it again, if the sizes are the same then I make the assumption the file is done being written.

 

[spikepl]

Try to open the file for append - I'm convinced (meaning this should be tested) the system will object while the file is still open by another process. This might apply to opening for plain read too, but I am not sure. Finally, you could try a rename of the file - that will for sure fail unless the file is no longer used by another process, in which case you could rename it back.

 

[bbulla]

This works in .NET. Just modify it for VBA.

Public Function FileInUse(ByVal sFile As String) As Boolean
If System.IO.File.Exists(sFile) Then
Try
Dim F As Short = FreeFile()
FileOpen(F, sFile, OpenMode.Binary, OpenAccess.ReadWrite, OpenShare.LockReadWrite)
FileClose(F)
Catch
Return True
End Try
End If
End Function

 

[gemma-the-husky]

having said that, i suspect you will get a rte trying to process a locked-for-editing file.

worth trying. just open a file in notepad (maybe word), and see if you can still import it.

 

[alexfwalker]

Thank you for all of the above responses - rest assured that I'll be tinkering with these during the course of today, so watch out for further posts asking for help!

Thanks,

Alex

 

[alexfwalker]

Test #1 - Open the file for editing and try importing - This did cause a JET error, which was a good thing, as I guess you could handle the error by asking to pause for 30 thirty seconds, then try again. So, moving on to the real life scenario for Test #2...

Test #2 - Start the file writing then try importing - Disaster. The import process grabbed the file, imported in part and disrupted the write process. So, looks like I'll be using the FileLen function to test files before import. I'll let you know how I get on.

 

[spikepl]

How about your original idea, modified, about renaming the file? try to rename the file from access, and loop until success.

 

[alexfwalker]

How about your original idea, modified, about renaming the file? try to rename the file from access, and loop until success.

Good thinking - I'll try this next and see what happens.

 

[alexfwalker]

Good thinking - I'll try this next and see what happens.

Test #3 - Try renaming the file whilst it's writing - Disaster. For example, the SOP writes the filename example.csv. I was able to rename this file to example2.csv during the writing process, and the SOP just began writing again to example.csv. Which meant that I had two files at the end of the process, example.csv and example2.csv, both of which were useless! Moving on to FileLen test I think...

 

[spikepl]

I am stunned! Normally when I mess around with all kinds of files I cannot rename them while some application has them open. But I assumed that your SOP-thing has the file permanently open. Perhaps it doesn't, but just opens, appends a bit, closes , and starts over again. In which case the len-thing is likely to be the only reasonably reliable indicator.

 

[alexfwalker]

I am stunned! Normally when I mess around with all kinds of files I cannot rename them while some application has them open. But I assumed that your SOP-thing has the file permanently open. Perhaps it doesn't, but just opens, appends a bit, closes , and starts over again. In which case the len-thing is likely to be the only reasonably reliable indicator.

Thanks for all your help on this Spike. In the end I had to create a user defined fucntion to get round the FileLen problem. It checks the file size, pauses for 5 seconds, then checks again and compares the two results. If they're different then it loops and starts again. Once they're the same it immediately begins the import process.

Works a charm!