At the same time, access to the database with Excel as an interface - is it feasible?

Question

Suppose you have a database with the largest tables containing about 200,000 rows and are frequently modified. The client wants Excel to connect via ODBC to the database and act as an interface for managing data. Data must be modified to 25 users at a time.

My first instinct would be to recommend something else, such as a web interface. But suppose the client insists on the Excel solution, do you consider it feasible and what pitfalls will you see in it?

My doubts would be about:

• data integrity (how to manage users simultaneously changing the same data)
• large amounts of data are moved unnecessarily (when I open an Excel workbook, I assume that the entire database should be migrated)
• security (showing only parts of the data to relevant users in a safe way will be a daunting task - see previous paragraph)
• using a tool (Excel) for something in which it does not exceed (sorry for the pun)

Answer 1

I do this all the time. No, you do not need to enter the entire database or even the entire table. I use ADO and VBA and send SQL queries through the Command object. For example, I have a royalty database with an Excel interface.

The user enters an account number and the SELECT statement retrieves one record and populates some user classes. The user enters / changes some data and clicks “Save”. The class then has a method that writes the record back to the database using UPDATE or INSERT, as appropriate.

At the end of the month, the user enters a date range and retrieves some records in the report, again just a SELECT statement that populates some classes and displays on the sheet.

Use transactions so you can roll back if you encounter any write lock problems, but with 25 users you probably won't want to.

Answer 2

At first glance, I would suggest treating Excel a bit like a web page, that is, pulling out only the data you need and using a specific form for editing that updates one record at a time using ADO. You only need to lock one entry and it will take some of the time required to update. You can check if a record has changed since it was opened for editing, and users can be told that they cannot open the record for editing, and then leave it sitting in the editing form or they may lose the changes.

As a rule, it is unlikely that for such a small group it is necessary to change the same record at the same time.

I don’t think you will have many problems with 25 concurrent users.