Concurrently access database with Excel as frontend - doable?

Answer 1

At first glance I would suggest treating Excel a bit like a web page, that is, pull only the required data and use a specific form for editing that updates one record at a time via ADO. You need only lock a single record and that for the fraction of time it takes to update. You can check whether or not the record has changed since it was opened for editing and users can be told that they cannot open a record for editing and then leave it sitting around in the edit form or they may lose the changes.

It is usually quite unlikely for such a small group to need to change the same record at the same time.

I do not think you will have much trouble with 25 concurrent users.

Answer 2

I do this all the time. No you don't have to bring in the whole database or even the whole table. I use ADO and VBA and send SQL statements via the Command object. For example, I have a royalty database with an Excel front end.

The user types in an invoice number and a SELECT statement retrieves that one record and populates some custom classes. The user enters/modifies some data and clicks 'Save'. Then the class has a method that writes the record back to the database with and UPDATE or INSERT depending on the situation.

At the end of the month, the user enters a date range and retrieves some records into a report, again just a SELECT statement filling some classes and outputting to a sheet.

Use Transactions so you can roll back if you hit any record locking problems, but with 25 users you probably won't.