open/activate book and edit without readonly -query

[Bob Phillips]

No, quite the opposite. You would have a simple client workbook that you put on eacgh desktop which access a data workbook on the central server.

Better still, create an addin and access a real database, SQL-Server if you can, Access if you must.

[vinny2984]

xld, i think i've got it now. Unfortunately the database option which was my first thought, is not an option for the company, so i'll work towards the excel server based option with client books accessing it.

many thanks for your helpul advice, i think you've saved me a lot of time and headaches.

[Bob Phillips]

Why not, you can run a database completely from Excel, you don't even need an Access license.

[vinny2984]

Your taking my thinking on this project in a totally different direction and one I don't really have knowledge of, but it's sounding better than the way I have planned it. I'm thinking if your way would give better results then i need to stop my current project and redisign it before it goes to far in the wrong dirction.
My original idea was to have a workbook for each office containing the staff rotas for that office stored on a central drive/server (all identical with the exception of the data), but these workbooks do need to be able to edit each others workbooks for some jobs. so in effect each workbook has its own database.
I think what you are suggesting is that I have an excel database held centrally that the client workbooks connect to, for 2 way info?
My understanding of how to set that up is little more than a vision needing a reat deal of trial and error so;
Do you know of any tutorials or examples that could set me on the the course for doing this?

[Bob Phillips]

No I know of no tutorials, but the principle is very simple.

You are absolutely correct that I am suggesting a single, central store of data. You say an Excel workbook, that can work but if the data is being shared my experience suggests that a database (even Access) is a better bet. I would never implement a shared Excel workbook, so if you use Excel data store, you have to manage the possibility that someone else has it open (harp back to my code), and ensure that your code holds the data workbook for a very brief period.

Then you would have a client workbook that is used simply to get the data from the data workbook, presents it to the user, accepts updates, and posts those updates. The critical thing is that most of the work is done async from the data workbook, don't hold onto it.

In the client workbook, you have a couple of options to either work on a single data item basis, say throw up a form that the user specifies the data item they wish to look at, pull that back to a userform, and then post back the updates. In this example, the workbook could be opened for two very short periods, minimising lock out (you could even use ADO). An alternative is to pull back all of the data pertaining to that user's office and present it in a worksheet tabular form, then allow the user to change/add to that as they wish, validating it for correctness/completeness. Then have a menu option to post it ALL back to the central data workbook (again this could all be done via ADO or open the workbook and write it). One consideration here is that two different users may pull back the same office's data and then both try to update it, the last will win in this scenario (again you can add logical locks to the data to tell the second user that the data is locked out).

I have implemented both types of update, they both work well.

As regards the client, as it stores no data, I would implement that as an addin.

So, the logic may be something like this

- user requests data
- check if data workbook available, tell user to try later if not
- pull back the data
- user updates within the client
- user signals update
- check if data workbook available, here best to have a try loop and fail gracefully
- post update back to central data workbook
- inform user

[vinny2984]

xld, many thanks thats given me a good overview of how to set about this and it definately sounds like a better way of going about it than i had planned.
thanks again

[rinser]

xld is the man!