PDA

View Full Version : Advice on a sort of client-server app



Sherwood
01-18-2007, 04:41 PM
Hi,

I want to write an app in Excel VBA to record jobs where one person enters initial info like date, time, requirements, etc. (let's say the receptionist) and another person enters work done, parts used, time spent, completion, etc. (let's say the technician). Each has own PC on a network and there's a server PC. Each would have their Excel-VBA front end open all the time so it's 'read-only' for the other party. So how do I get data between them?

The solution obvious to me is to have a third workbook, sitting on the server, which each user's front end accesses briefly to upload/download data (open workbook, do the business, close the workbook), hence no read-only issue. Of course one front end could try to access it at same time that the other one is in there so I need to detect whether the workbook on the server is already open (then I'd program a short wait before trying again).

Q1. No doubt plenty of ways to skin this cat but am I approaching the whole concept in a suitable direction (one 'server' workbook and two 'client' workbooks)?

Q2. What sort of code would I use to detect workbook on server already open?

Sherwood.

Bob Phillips
01-18-2007, 05:02 PM
Q1. Yes, but why two client workbooks? You could just as easily have one read-only client workbook also on the server. As long as nothing in that workbook is updated, no problem.

Q2. You could have the data in a database, such as Access, and use ADO to access it. You don't need to worry whether it is open or not, you don't even need Access.

matthewspatrick
01-18-2007, 06:06 PM
Sherwood,

Welcome to the board!

You could make Excel work for something like this, but in a fraction of the time and effort you could get it running (and running much better at that) in a real database application. Access will do just fine as long as your user base is small.

Bottom line: Excel is just not designed for multi-user capabilities. Do not even get me started on 'share workbook'.

Sherwood
01-19-2007, 02:45 PM
Thanks for replies.

xld,

I want to be able to send job status info back to receptionist such as job in progress, job completed (so they can inform customers). I presume this means that neither client can be read-only.

I don't know what ADO is but looks interesting so I'll need to go off and learn.

matthewspatrick,

I know it's really a db project but I 'do' Excel (I'd say intermediate level) whereas my Access will be quite rusty. I did a short traing course some years ago but never really applied it much. Anyway, in light of your comments I've decided to re-think my strategy but am wary that 'fraction of time and effort' wouldn't necessarily be the case with my lack of db knowledge.

Sherwood.

Bob Phillips
01-19-2007, 03:15 PM
I want to be able to send job status info back to receptionist such as job in progress, job completed (so they can inform customers). I presume this means that neither client can be read-only.

That wouldn't stop read-only. Wouldn't that just be a print-out generated by the client app?


I don't know what ADO is but looks interesting so I'll need to go off and learn.

See this (http://www.vbaexpress.com/forum/showthread.php?t=11065)recent thread

Sherwood
02-02-2007, 03:20 AM
Thanks for advice guys. I have read up ADO and have got the basics working. I created a Jet database using Access, just one table needed. I have working VBA code in Excel to read and write to this through ADO and to compact it through JRO. Just need to do the easy bit now; write the rest of the Excel client app. I might have to learn/refresh SQL knowledge on the way so that I can read/write selectively (at present I am just reading/writing the whole recordset).

Sherwood.

Bob Phillips
02-02-2007, 04:18 AM
Great, that is two people I have converted to slinet-server code with ADO this past month.

Job done!