Does vba OPEN.. FOR OUTPUT LOCK READ imply ... LOCK READ WRITE?
Can anybody point me to microsoft documentation that shows that
OPEN FOR OUTPUT LOCK READ WRITE
is the same as OPEN FOR OUTPUT LOCK READ?
I believe they are equivalent but this MS doc does not help me.
Sub OpenWrite()
Open "C:\aaatmp\lastaltMMacro.txt" For Output Lock Read As #1
MsgBox "While this message displays, try opening file with notepad"
Close #1
End Sub
Sub OpenReadWrite()
Open "C:\aaatmp\lastaltMMacro.txt" For Output Lock Read Write As #1
MsgBox "While this message displays, try opening file with notepad"
Close #1
End Sub
Hi,
Both samples you posted lock the file for reading, wich prevent the file from being opened by any process.
So even if there are slight differences (in fact, lock for reading and lock for writing arn't related), the observable effects are the same. (Lock the file for writing only and you'll notice a difference).
Side note:
Unless you're handling binary files, I strongly suggest that you use the FSO library to handle text files, it is easyer to understand, has stronger meaning, is clearer about your intends.
Both samples you posted lock the file for reading, wich prevent the file from being opened by any process.
So even if there are slight differences (in fact, lock for reading and lock for writing arn't related), the observable effects are the same. (Lock the file for writing only and you'll notice a difference).
Side note:
Unless you're handling binary files, I strongly suggest that you use the FSO library to handle text files, it is easyer to understand, has stronger meaning, is clearer about your intends.
ASKER
So even if there are slight differences observable effects are the same.
Use FSO instead
I posted this question because I see no observable difference. So part 1 of your proposed answer is just an echo.
But, I think I understand the difference now. It probably has something to do with caching. I will do some experiments later today and will post back the results.
Part 2 is philosophically interesting. When there several techniques to solve a problem I usually choose the "simplest" solution. Clearly OPEN is slightly simpler than FSO because it takes fewer lines of code and doesn't require a reference to a library.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Edits from late evening on 1/3/2022. --------These are wrong, ignore them----------------
Please ignore my comments from earlier this morning.
I now know that Open myFile For Output Lock Read is COMPLETELY UNRELIABLE. I MUST USE LOCK READ WRITE INSTEAD !!!
I have tested it by running the following simultaneously in Word and Excel.
Word wrote 2000 records and Excel wrote 1000 records.
When both were done the file had 1000 excel records the 58000 bytes of hex 00 but only Word records 999, and 1000.
The other 998 Word records were replaced by 58000 bytes of hex 0.
When run in Word the macro puts 2000 records to the file. When run in excel it puts 1000 records in the file.
Guess how many records were in the file when the macros were run in parallel with Word going first?
I expected Excels 2000 records to be last, but they were first! I expected Words 2000 records to be first but 998 of the records were turned into 58000 hex "00" bytes. Word's last 2 records were at the end of the file.
I must have been tired when I posted this question.
It is very simple and has nothing to do with caching.
Many processes can simultaneously have access to a file with
Open myFile For Output Lock Read
Only one process can access the file with
Open myFile For Output Lock Read Write.
Sorry to have wasted everybody's time
Please ignore my comments from earlier this morning.
I now know that Open myFile For Output Lock Read is COMPLETELY UNRELIABLE. I MUST USE LOCK READ WRITE INSTEAD !!!
I have tested it by running the following simultaneously in Word and Excel.
Word wrote 2000 records and Excel wrote 1000 records.
When both were done the file had 1000 excel records the 58000 bytes of hex 00 but only Word records 999, and 1000.
The other 998 Word records were replaced by 58000 bytes of hex 0.
When run in Word the macro puts 2000 records to the file. When run in excel it puts 1000 records in the file.
Guess how many records were in the file when the macros were run in parallel with Word going first?
I expected Excels 2000 records to be last, but they were first! I expected Words 2000 records to be first but 998 of the records were turned into 58000 hex "00" bytes. Word's last 2 records were at the end of the file.
Sub OpenWrite()
Dim i
Open "C:\aaatmp\test.txt" For Output Lock Read As #1
For i = 1 To IIf(Application.Name Like "*Word*", 2000, 1000)
Print #1, i & " " & Application.Name & " wrote to file at " & now
Next
MsgBox "Open another process and check file with windows explorer"
Close #1
End Sub
I must have been tired when I posted this question.
It is very simple and has nothing to do with caching.
Many processes can simultaneously have access to a file with
Open myFile For Output Lock Read
Only one process can access the file with
Open myFile For Output Lock Read Write.
Sorry to have wasted everybody's time
Part 2 is philosophically interesting. When there several techniques to solve a problem I usually choose the "simplest" solution. Clearly OPEN is slightly simpler than FSO because it takes fewer lines of code and doesn't require a reference to a library.
Coding is not about writing as less lines as possible.
It is about writing clear and understandable code to produce the desired behavior.
As for worries about additional librairies, only profiling tools can show the benefit or detriment.
on my side, understandable code surpass everything.
Caveat: Depending on the Office version, OPEN is not Unicode sensitive. Thus you cannot write Unicode strings with it.
ASKER
Fabrice, To avoid being argumentative I will concede that I usually use FSO for most file operations.
And I totally agree 10 lines of understandable code is far more important than 6 lines of "clever" code.
But many times I have used Open myFile for Output Lock Read Write as #ff and never found it hard to understand.
Personally, I find it just as clear and just as easy to understand as Set stream = FSO.OpenTextFile(myFile, ForWriting, Create:=True). I might agree that FSO avoids a need for FreeFile. but both techniques are very easy to understand.
rberke (aka UncleBob)
========================
You can ignore the following rambling words. I am retired with too much time on my hands and I feel talkative today.
========================
I always thought I was pretty good at understanding file locks, sync points, rollbacks etc. (In the distant past I was in charge of tech support & database administration for a large company. When their IBM mainframe crashed due to an CICS/IMS deadlock or deadly embrace I was the guy that debugged the 2000 page CICS dump.
But, in the last 20 years I have done very little with Windows locks, so the purpose of this question was to further my understanding of Windows Lock Read vs Lock Read Write. I was only slightly surprised to learn that OPEN OUTPUT LOCK READ was dangerous and could lead to corrupted data files. Microsoft doesn't put much effort into data integrity for their legacy file systems. Only their sql server products do things properly.
A few other random comments:
========================
OPEN myFile OUTPUT LOCK READ can lead to corruption if large amounts of data are written by multiple process.
But if only 1 or 2 records are written by multiple processes it is safe, but it is still strange because each process writes to a different cache.
For instance run the following macro in Word, Excel and Outlook and leave the 3 msgboxes open.
Each process gets its own cache, and the records go to that cache. When the each msgbox is closed that process's
cache is written to disk and previous records are gone.
============another ramble============
Happily, FSO hides all of its locking option. This is good for most users but it means that FSO cannot help me understand OUTPUT LOCK READ. .
AFAIK FSO uses LOCK READ WRITE for all updates.
AFAIK FSO does not user any locks for operations such as FSO.FILEEXISTS and FSO.GETFILE
============another ramble============
Notepad is also of limited help. It appears to open files exclusively and briefly. Once the file is copied to main memory, Notepad closes the file, then splashes the memory copy onto the monitor. The user thinks the file is still open because he or she sees it on the monitor. But behind the scenes the file is closed, and can be renamed or even deleted via Windows Explorer.
And I totally agree 10 lines of understandable code is far more important than 6 lines of "clever" code.
But many times I have used Open myFile for Output Lock Read Write as #ff and never found it hard to understand.
Personally, I find it just as clear and just as easy to understand as Set stream = FSO.OpenTextFile(myFile, ForWriting, Create:=True). I might agree that FSO avoids a need for FreeFile. but both techniques are very easy to understand.
rberke (aka UncleBob)
========================
You can ignore the following rambling words. I am retired with too much time on my hands and I feel talkative today.
========================
I always thought I was pretty good at understanding file locks, sync points, rollbacks etc. (In the distant past I was in charge of tech support & database administration for a large company. When their IBM mainframe crashed due to an CICS/IMS deadlock or deadly embrace I was the guy that debugged the 2000 page CICS dump.
But, in the last 20 years I have done very little with Windows locks, so the purpose of this question was to further my understanding of Windows Lock Read vs Lock Read Write. I was only slightly surprised to learn that OPEN OUTPUT LOCK READ was dangerous and could lead to corrupted data files. Microsoft doesn't put much effort into data integrity for their legacy file systems. Only their sql server products do things properly.
A few other random comments:
========================
OPEN myFile OUTPUT LOCK READ can lead to corruption if large amounts of data are written by multiple process.
But if only 1 or 2 records are written by multiple processes it is safe, but it is still strange because each process writes to a different cache.
For instance run the following macro in Word, Excel and Outlook and leave the 3 msgboxes open.
Sub OpenWrite()
Dim i
Open "C:\aaatmp\test.txt" For Output Lock Read As #1
Print #1, Application.Name & " wrote to file at " & now
MsgBox "Open another process and check file with windows explorer"
Close #1
End Sub.Each process gets its own cache, and the records go to that cache. When the each msgbox is closed that process's
cache is written to disk and previous records are gone.
============another ramble============
Happily, FSO hides all of its locking option. This is good for most users but it means that FSO cannot help me understand OUTPUT LOCK READ. .
AFAIK FSO uses LOCK READ WRITE for all updates.
AFAIK FSO does not user any locks for operations such as FSO.FILEEXISTS and FSO.GETFILE
============another ramble============
Notepad is also of limited help. It appears to open files exclusively and briefly. Once the file is copied to main memory, Notepad closes the file, then splashes the memory copy onto the monitor. The user thinks the file is still open because he or she sees it on the monitor. But behind the scenes the file is closed, and can be renamed or even deleted via Windows Explorer.
You can lock and unlock the relevant record, by referring to the following
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/lock-unlock-statements