File on LAN

[mangeshmm]

Greetings everyone. I am hoping that someone out there can point me in the right direction for the problem Im facing. I am attempting to automate a file which is stored on the LAN in office. The file will throw up a userform when opened. The user will fill up the various fields and hit Ok. This will record the entry on a table in this file. The problem I am grappling with is how to control (forbid) another user from opening the file in parallel. I cannot even simulate an environment in my design and testing at home to know how a second copy behaves. For example, apart from giving a read-only notification, will it fire the workbook_open() event (intuitively) it will. If so, what happens if the user naively fills up the userform and clicks Ok? Will it harm the file or will Excel take over natively and ask the user to do a Save As (at which point the user realizes what he has done and closes the file to try later). Additionally, what will happen to the original session in this scenario (that is, what will be the impact on the actions of the first user to open the file).

Any help/advice to navigate this issue would be much appreciated. Thanking everyone out there in advance for their time and attention.

 

[dmt32]

Hi,
As a suggestion, rather than have one workbook to share across your LAN create template copies of the workbook for each your users.

You will need to update your forms code to open & save the data to your table which would be placed in a central (database) workbook on the network.
With this approach, your users can leave open their copy as long as they want without worry of conflicts.

Dave

 

[mangeshmm]

Thanks Dave. One problem that I can see right off the bat is that it adds overload to the solution. I was hoping (in an ideal world) is that if the central workbook is open on any machine, then the thisworkbook.readonly can be got into play to close the workbook. If that is possible (after suppressing all the native Excel prompts of reopening the workbook will cause loss of changes) then we are all good. While your suggestion is certainly tenable, it does add a coding overhead to the entire solution, where, for a start, we are now dealing with multiple files (one for each user) which are then pulled by the central workbook. This also therefore requires the need to wipe out entries from the support files when there is a successful upload to the central fie. Maybe its just greed, but I was hoping that the solution could be simpler. Any thoughts? Thanks mate

 

[dmt32]

Based on your first post which suggested you had one form writing to one table then suggestion should work as the Central workbook (database) is opened & closed only by the code in the users template copies.

– About 10 years ago using same approach, I created a timesheet submission system for my daughters place of work (a charity) where each week, 250 employees across country submitted their attendance record which worked without issue so not sure what you mean by “overloads the solution”?

Dave

 

[mangeshmm]

Thanks again Dave. Let me make sure I got you right to avoid any loss in transmission. My impression from your first response was this:

There is one template file per user. Each user records their entries into their respective template files through a userform input. Thereafter, the user is no longer in control, but passes control to the "admin" (lets say thats me). The admin docks each user file into the central database which updates the tables based upon the entries across multiple user created files which have been docked to the central database

Based upon your most recent post the impression I now have is:

There is one template file per user. Each user records their entries into their respective template files through a userform input. Recording the entry (OK_click()) results in accessing the central database and the entries are transferred from that particular userfile into the database. Whenever the admin opens the file it will contain the entries aggregated from the various user files.

If my first understanding is correct, my limited point was that this option involves far more coding rather than the option where a user is prohibited from opening the central database in parallel when already opened by another user. However, assuming that this is possible, I am unable to think through of the unintended consequences that may result, both upon the central database as well as the original session opened by user1.

If my revised understanding is correct, it still leaves the original question unsettled. That is, if user1 and user2 are using their separate templates in parallel, clicking OK would still result in the same central database file being accessed leading to exactly the same consequences when 2 separate users try to access the central database.

I am certainly keen to understand your solution better. For the moment, Im hoping that I have been able to articulate my reading of your proposed solution in order to express my confusion correctly.

 

[dmt32]

2nd revised part on the right lines but you as Admin would never manually open the workbook in read write mode otherwise suggestion fails - you would just extract required data from it via another workbook or open it read only. The workbook is just a central Repository (database) for your users the submit their data. Better would be to connect the template copies to a database like access.


As regards user conflicts - unless there is anything in the workbook the slow things down & user base not too large, master workbook should open & close in blink of an eye - and where there is a User data submission conflict, you can write code to manage it.

just a suggestion.

Dave

 

[mangeshmm]

As regards user conflicts - unless there is anything in the workbook the slow things down & user base not too large, master workbook should open & close in blink of an eye - and where there is a User data submission conflict, you can write code to manage it.

just a suggestion.

Dave

Thanks again Dave - as regards the quoted text above - can you offer a suggestion for the typical construct of the code one would use to manage a user conflict?

And sorry for belabouring the point - I just want to make sure that I have a complete handle on what you are suggesting. As follows:

Each user accesses his/her particular template file. Opening this file opens a userform which the user fills in. Clicking the OK button accesses a separate central database workbook. The entries entered by the user are logged in the central database. I, as the admin, am detached from the process of entering the userfile or updating the database. I note your suggestion that my only privilege should be read-only - fair enough. You are also suggesting that where there is an access conflict for more than 1 parallel attempt to access the central database workbook, suitable code can be put into place to handle it. I am certainly keen to understand the indicative design of the tie breaker mechanism as requested above.

I think i am getting a better picture of what you are suggesting than what I could earlier. Would appreciate if you could round off some unsettled questions. Thanks a million Dave

 

[mangeshmm]

Sorry, I missed out to answer something else your response touched upon. While it is likely that the number of potential users making entries to the database may be limited first up, I need to ensure that the code I have in place responds adequately to a situation where the number of users increase rapidly - so I would much rather make the investment of making the solution scalable even though there is no immediate need to do so (or for that matter, any foreseeable need either)

 

[dmt32]

Each user accesses his/her particular template file. Opening this file opens a userform which the user fills in. Clicking the OK button accesses a separate central database workbook. The entries entered by the user are logged in the central database. I, as the admin, am detached from the process of entering the userfile or updating the database. I note your suggestion that my only privilege should be read-only - fair enough. You are also suggesting that where there is an access conflict for more than 1 parallel attempt to access the central database workbook, suitable code can be put into place to handle it. I am certainly keen to understand the indicative design of the tie breaker mechanism as requested above.

That's about it.

have a look here:https://www.mrexcel.com/forum/excel-questions/1005007-standard-entry-another-worksheet.html

#post 10 Code I provided for another here with a similar need. Solution is for template entry from worksheet but you should be able to update for your userform

Would not worry at this stage, about conflicts too much unless you have a very large user base

Dave

 

[mangeshmm]

Thanks a lot for your time and help Dave. Much appreciated mate. Happy to go over the links which you have provided. If some doubts still linger, wont hesitate to raise you again!!