General application/data parameters in a multiuser environment.

[unluckyuser]

I'm hoping to develop an application where I have several users on a common drive. I will have 85 active patients at any particular time. I was planning on writing an Excel file that ran in several instances in 8 or 10 computers at one time, which was never modifiable itself. It just has vba code and forms, so no user can change anything about the program files. They start the spreadsheet, a macro takes over and they're just left with forms that have verified inputs (dates, etc). In a separate Excel file, all data is kept on these 85 active patients, plus there will be a larger and ever-growing excel file of discharged patients. Each record will be fairly large, composed of a gif picture of each patient and many fields of various attributes and notes on each patient by various segments of the treatment team. If someone wants to run a query on patients discharged on a specific day or withing a date range, the report module would search the discharged patient spreadsheet and print the appropriate report. If they're working with active patients, they'd be working in the active patients spreadsheet.

I am not sure how to handle multiple people editing records. A user would be working with a record and when they were finished and neede to save the changes, the data file would be saved. Someone else working on another computer wouldn't know that the record had been updated.

One of the limiting issues here is overall stability of the system. Another program (multiuser health care) that can only do a limited amount is running all the time on every system. If you have that open and open a few Excel spreadsheets and word files, you are guaranteed to have a crash with unpredictable results. When my application starts, it will abort if the user has another instance of Excel or Word running. As soon as my program loads, the ribbon is not visible and vbs form are used for all input. Other than starting an excel spreadsheet file, the end user never sees a spreadsheet. They simply input new or modify existing records and then exit the spreadsheet.

A wrote several large maco programs at another job, which were entirely stable. However, I never kept data files. Users would simply call up an excel file, enable the macros and then use forms. They would enter an inmate number and the program would go to the state prisoner database and find/download information about an inmate with that number. We ran this for years without a single crash, but of course this was just a query and we weren't saving data just, generating extensive reports that we no longer had to do by hand.

The bar is higher here.

First, is this a viable approach? Would I be better off using Microsoft Access? Thanks. I'm fairly capable with VBA and have never had any issue completing any project I've taken on.

 

[Artik]

The database file (xlsx) should be closed most of the time. Communicate with the file using ADO, rather than opening-closing the workbook. If the file has to be opened for some reason, first check that it is not read-only, this would indicate that another user is using it at the moment. In such a case, we do not allow full editing, but only read data.
If the first user starts editing a record, insert True in an additional column (e.g. Blocked). If a second user wants to edit the same record, True should not allow editing until the first user finishes editing the record. At the end of the edit, of course, the blocking should be removed.

Certainly storing your data in Access would give you more security. Excel could act as the front-end here, and Access as the back-end.
BTW. it would be better for the workbook to store image files in a folder rather than in the workbook. When needed you only load what is needed.

Artik