excel可以作为mysql的前端吗,同时以Excel作为前端访问数据库 - 可以吗？

Suppose you have an database with the largest tables containing about 200.000 rows, and frequently modified. The client wants Excel to connect via ODBC to the database, and work as a frontend to manage the data. The data should be modifiable by up to 25 users concurrently.

My first instinct would be to recommend something else, for example a web frontend. But suppose the client insists on the Excel solution, would you regard it as doable, and what pitfalls would you see in it?

My doubts would be about:

data integrity (how to manage users modifying same data at the same time)

large amounts of data moved unnecessarily (when opening the Excel workbook I imagine that the whole database has to be transferred)

security (showing only parts of data to appropriate users in a secure way would be challenging - see previous point)

using a tool (Excel) for something, in which it doesn't excel (pardon the pun)

解决方案

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.