Lock A Text File 2

[Skittle]

I have a program that reads CSV file, processes the data and stores it in a database. Its basically a text import to database program.

How can I prevent more than one user at a time running the import program and duplicating the data? Is there a way of locking the .csv input file?


Dazed and confused

 

[RichardWolf]

Assuming you are using standard file I/O to open and read the file, you only need to modify your open statement slightly:

On Error GoTo FileLocked
Open "C:\temp\test.csv" For Input Lock Read Write As #1
On Error GoTo 0
Do While Not EOF(1)
'your processing code
Loop
Close #1
Exit Sub

FileLocked:
MsgBox "Someone else has that file locked"
Exit Sub

 

[GDGarth]

Here's what I do:

1. Rename the file, or move it to a different location.
2. Read the data from the file and dump it into the database.
3. Delete the file (or you could rename it).

I just delete the file because we've got a copy of the data in another location.

The other option, and one you should take anyway, is to create an index so you can't get duplicate data. I'm storing test records in one database, so what I do is create a primary key (not Autonumber) out of a string including the part number, assembly line, date, and time of test. In our system, this combination will never be duplicated exactly, so I've got a unique identifier for that record. If I have to reload data, I can just go back to any point before the crash, and run the system without worrying about duplicates.

The second option is really the way to eliminate duplicate records.

 

[Skittle]

Thanks.

I had been thinking along the lines of GDGarth's ideas but the problem I have is that duplicates can occur so for this case I will need to lock the file.

Dazed and confused