[RESOLVED] Data input from file already in use

[VB22]

Hello all, I hope you can help me resolve what seems to be quite a straightforward problem.


I've bulit an application that reads in data records which are stored in text files. I use the following method for doing this:

strCSVPath = "c:\mypath"
strCSVFileName = "myfile.txt"

With db1
    .CursorLocation = adUseClient
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strCSVPath & ";" & "Extended Properties=""text;FMT=Delimited;HDR=NO"""
End With

With rs1
    .Open "Select distinct * From [" & strCSVFileName & "]", db1, adOpenStatic, adLockPessimistic, adCmdText
End With

However, the files being read in are on a network drive, and if some other user has it open on their system when my program tries to run the above code, it gives an error message saying something like "Permission denied - another person or program is using the data source".

I have tried to resolve this issue by attempting to copy the file to another location using the filecopy method, then running the above code on the copied file, but unfortunately this gives the same error as above when attempting to do the copy. What's strange is that I can copy the file manually with my mouse in Windows without any error, but if I try programatically do it I get the error.

So, my questions are - a) how can I read the data when someone is already in the file, or b) how do I replicate Window's method of copying a file which is already in use.

Many thanks for your time

James

[LaVolpe]

You might be able to open the file, read the data and process it or write it to a temp file. Try this, supplying the path & file name

Dim bData() As Byte
Dim ff as Integer
ff=freefile()
On Error Resume Next
Open [path/file] For Binary Access Read As #ff
If Err then 
   MsgBox "Failed to open file. Ensure Path/File Name exists"
Else
   On Error GoTo 0  ' allow errrors to happen, if any
   ReDim bData(1 To LOF(ff))
   Get #ff, , bData()
   Close #ff
   ff=FreeFile()
   Open [temp path/file] For Binary As #ff
   Put #ff, , bData()
End If
Close #ff

Replicating Window's file copy: If I was on a pc where I could reproduce your problem, I would have tested what I'm about to suggest. Unfortunately, you will have to do that. There are 2 APIs commonly used for copying files: CopyFile/CopyFileEx which is probably what VB uses. You can try that API but I think it might fail too. The other is SHFileOperation. Recommend trying those to copy the file.

[VB22]

Thanks a lot for your suggestions LaVolpe - I will give them a try when I am back at work tomorrow and let you know how I get on.

[wes4dbt]

If you want to copy the file even when it's open, this works

Dim vFs As Object, vFile As Object
    Set vFs = CreateObject("Scripting.FileSystemObject")
    Set vFile = vFs.GetFile(cPath + "data.txt")
    vFile.Copy cPath + "import_archive\" + cFname

[Darkbob]

If you want to copy the file even when it's open, this works

Dim vFs As Object, vFile As Object
    Set vFs = CreateObject("Scripting.FileSystemObject")
    Set vFile = vFs.GetFile(cPath + "data.txt")
    vFile.Copy cPath + "import_archive\" + cFname

That looks interesting! What do you need to add to your project for a reference to get the Scriping object to work?

--DB

[si_the_geek]

As late-binding is used in that example (using CreateObject, and variables declared simply 'as object'), you don't actually need a reference - it will effectively be generated at run-time (which simplifies installation, and avoids various version based issues).

[VB22]

Excellent wes4dbt - just tried your code on my home machine and it worked perfectly

Thanks everyone for your help!!

[Darkbob]

Well isn't that just the most handy thing I've ever seen! Cool. Thanks very much. You wouldn't believe what I was doing to make a backup of files in use.

--DB