File Locking - Multiple access

[monpasdg]

So I am running ME on about 7 computers that break apart a process to greatly increase the run time speed. (2 hours for 7 computers or 16 hours + for 1 computer) Its not a direct time relationship due to the system speeds during the day.

 

I have a file that is read and written too by each macro express on each computer and it directs what processes have been done already.

 

For example computer 1 would create the file and write the first process it did on line one so (processed 11/23/2009, worked on xyz) then computer 2 would read that file see that another computer created the file and read the file. It then recognizes that (11/23/2009, xyz) was worked and it will output the next process (11/22/2009, ABC) so another computer doesn’t work it.

 

So that’s the setup for the multiple computers working together, its more complicated than that but that’s not this issue. The issue is locking the file so that when the file is being read it’s only being done by one computer at a time. I do understand that the "read" cannot be blocked unless permissions are changed but locking the file so computers are not all writing at once may get around this issue.

 

I have tried several different tests with no success.

 

Things I have tried:

• Using the "Wait for file ready"
  
• making sure the file is ready for 5 seconds
  
• This doesn't accomplish much besides making the computers all read and write to the file at the exact same time causing each one to pickup the same task.
  

 

• Using the "if file ready"
  
• With this I created another file which a computer would open in the ASCII then execute the code to read and write on the task list file.
  
• Computers would then see if the "lock" file was ready rather than the task file.
  
• This showed much better results and computers didn't double up on processes, however it wasn't 100% in my stress testing.
  

 

My last resort is to have each computer rename or move the file so other computers don’t have access to it then move it back when it’s done. I am worried that this will crash any running macros that are trying to access the file at the same time rather then having them wait for it to be ready. This poses other concerns such as other machines thinking the file hasn't been created or the computer that renames it crashing bringing down everything.

 

Any suggestions would be great, I know there is a lot here but I wanted to explain the situation fully.

[paul]

I have had a similar requirement in the past, though not one that involved Macro Express. We had a Microsoft Access project (similar to an Access .mdb database application file) which needed to be run from users' machines. The project was launched from a server belonging to the user's region, and it was the launch program's responsibility to check for new versions of the project on the server and "distribute" them when required. A new version of the project was distributed from a single central computer to each of the regional servers as a Winrar compressed archive. The launcher would, for the first user off the block in each region, unpack the archive and make it available for subsequent copying to each user's machine. For all other users, the launcher simply copied the latest version to the user's machine (assuming this hadn't already been done earlier).

 

I used a semaphore file for this purpose. Initially named, say, semaphore.txt, the launcher would rename this to semaphore.xxx for the first user to launch the new version. Any other users finding semaphore.xxx would simply wait (in a loop) until semaphore.xxx ceased to exist. The launcher would then unpack the Winrar file and erase semaphore.xxx, at which point the launcher would copy the unpacked project file to all outstanding users waiting in the queue together with that first user who had initiated the whole thing.

 

I imagine you could use a similar process to meet your needs.

[monpasdg]

That does sound like a great idea, but with macro express I am not sure how it handle the creating on the semaphore.xxx file. I see an issue with two computers identifying that the file doesn't exist and creating the file. I could not genereate an error with two computers creating the same file over and over (using an infinite loop of course). This of course leads both computers into making use of the shared resource.

 

One thing I am going to try to do with the method is have the computer output its name in the semaphore.xxx file and then wait 1 second for any other computers to finish their operation should the timing be so close that they both try to create the same file. After the 1 second, each computer in the loop will read the file and if its name does not exist in the file it will kick out and wait for the semaphore.xxxx file to no longer exist.

 

 

Thanks for the Response!

 

 

 

--Don