Solved

Speeding up/ or different method for lock file creation/delete on a server from a client.

Posted on 2002-07-01
14
218 Views
Last Modified: 2013-11-13
I have a Client server application written in excel VBA on Excel 97.

Before any changes can be made on the client, it must first check the see if any other changes are in progress by looking for the presence a "lock file" on the server on the network

If  the file does not exist it is presumed that no other user is making a change and the file is then created to ensure no other users can make changes at the same time. This is then deleted when complete, thus freeing up the application for others to use.

The problem is the time it takes for a client open application to actually perform the open command (eg Open \\server_name\sharename\filename" For Output As #2), then do a simple 1 word write, then close it again.

Also Deleting this file (using the "kill") command, takes time.

They may only take a second or so each over the network, but when you have to look for the file, then create it, then delete it, every time a change is initiated, it slows everything down, and adds about a 3-4 second delay to the macro execution. This is not acceptable.


Does anyone have any suggestions as to another way to do all this.  I need to be able to check, create and delete some sort of lock on a network server from a client.

Is it possible to create some sort of lock in memory in the server from the client, and then delete it, or check for it. Would this be quicker that using the file/creation method I am currently using.

And if so how do I do this.

Any suggestions would be greatly appreciated.
0
Comment
Question by:gnome42
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 6

Expert Comment

by:xSinbad
ID: 7123716
One method to this may be to use the winsock control to interogate the server app for its state.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 7123722
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 7123724
0
 

Author Comment

by:gnome42
ID: 7123747
xsinbad,

Thanks for the above. I can not access the 1st one, but if it is along the lines of the 2nd site, it will not work, as I am using VBA on excel 97, and not VB. VBA does not seem to know anything about winsock stuff, and I do not want to call a VB executable external to VBA to run winsock as it will just add more time , and this is what I am trying to reduce.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7123949
What sort of data are you holding in your spread sheet. Excel is not really intended as a shared data resource.  If we no what you are doing with this spreadsheet perhaps we can suggest a better and faster system.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7124034
Why not use DDE. You could add a linked workbook to your current one that is stored on the network drive. Modifying the contents of cells in this linked workbook is trivial and using DDE would allow it to be visible within a *very* short time to any client that is linked to it.
0
 
LVL 7

Expert Comment

by:jaynee
ID: 7124099
Tim's suggestion sounds good to me.  Does your current lock file tell you whether the workbook is locked, or a worksheet in a workbook, or a range, or a single cell?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 43

Expert Comment

by:TimCottee
ID: 7124107
jaynee, what are you doing in this TA did you take a wrong turning out of the lounge? Nice to see you anyway.
0
 
LVL 7

Expert Comment

by:jaynee
ID: 7124130
Hi Tim.  Don't tell anyone, but I'm a VB coder as well : )

Back to you, gnome42...
0
 
LVL 3

Accepted Solution

by:
schworak earned 500 total points
ID: 7124664
You can also try doing a little trick I used a while back for the same type of effect. This code opens the lock file for append and leaves it open while working.

If it is already open, an error is returned and the current program knows it is already locked and can't do the update.

If it is not locked you do the work and close the append file.

By using this method you don't have to kill the file ever. Only one system can gain append access to the file at any one time. And if that system happens to crash with the file open other systems will be able to regain control of the file without a human needing to delete your lock file first.

You never need to actually write anything to the file, just open and close it. Here is a sample...




Dim Unlocked as Boolean
Dim FileNum as Byte
On Error Resume Next
FileNum = FreeFile
Open YOUR_LOCK_FILE for append as #FileNum
Unlocked = (err = 0)
On Error Goto 0 ' or your own error code sub

if Unlocked then
   '
   ' your can go here  
   '
   if not locked then
     close #FileNum
   end if
end if
0
 
LVL 3

Expert Comment

by:schworak
ID: 7124679
Oops.. In the code sample just inside the IF UNLOCKED THEN section the comment block is where your code would go.

'
' your CODE can go here
'

I guess I left the word CODE out. The code actually works rather fast because the file locking is on a system level and very fast.

If the file is not there it is created. If it is there and not in use it is opened and locked. If it is already locked ERR returnes a NON-ZERO value and the IF statement will be skipped because the file couldn't be locked.

Simple, fast, works in all systems. It also works if the user has READ-ONLY access (or no access) to the folder where the lock file is stored. If the user can't gain append control of the lock file for any reason they are then not allowed to make the edits within the IF block.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7125759
You might also use a database to do the locking for you.  You would need to have an (active) open connection to the database for every user.  A one-row table can be "locked" for update before the change and unlocked after the change.  This is VERY fast and already supports multiple users.  You don't have to change any data in the database, just use the database's locking mechanism.
0
 

Author Comment

by:gnome42
ID: 7125786
All

Nice to know that people are so willing to help a "not so knowledgeable (yet)" person.
It is much appreciated.


Inthdark.
The spreadsheet, has data in it relating to a bookings system. It is specifically formatted to look like a system it has replaced, and due to the large amount of cell formatting, Excel proved to be the best application to handle this. Also I have tried using the "shared workbook" facility, but this is very resource hungry, and can easily create huge files (1/2M +) that need to be distributed when you modify only a few cells. This is all very slow, and full of other issues as well.  What I ended up doing is splitting everything into functions that, create "rollback" files that contain minimal information about the changes. These files are then distributed and the appropriate functions run to recreate the data locally. As this application is used via an Intranet, across many continents, speed is important. The rollback files are no bigger than 1k each, so they are quick to distribute.   Thanks for your input.

Timcottee,
DDE- scary stuff !!!, and can be a bit unpredictable, specially on excel 97, VBA tends to be flaky enough as it is. Also I would need to keep the other spreadsheet open on the server, which it not always is, otherwise it takes too long to do the DDE stuff. There are too many other subs and timers running around in the background at the same time and this causes enough headaches. But thanks for the suggestion.

Schworak
Sometimes it is the simplest solutions that are the best. In one simple bit of code and suggestion, you supplied, you have managed to succesfully reduce, the time it takes me to create, check for and unlock the application.  I broke down the open, close, and kill routines into 3 separate subs, and it is the kill that takes the most time. By not having to kill it, or even write to it it has taken sufficient time off what I am trying to do to make it acceptable to the users.

Also you have solved my longstanding problem, of what to do when a client crashes and has not removed this lock file. No more as you put it "Human needing to delete it". Simple and brilliant.

The points are all yours.

Thanks
0
 
LVL 3

Expert Comment

by:schworak
ID: 7127138
Thanks. I am glad the solution worked for you. You will find in most cases, the simplest answer is usually the best answer. This little trick is one I have used on and off for many years (over 20 now) and it has always worked even on the really old systems I started with way back then.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now