使用 Excel 作为前端同时访问数据库 - 可行吗? [英] Concurrently access database with Excel as frontend - doable?
问题描述
假设您有一个数据库，其中包含大约 200.000 行的最大表，并且经常被修改.客户希望 Excel 通过 ODBC 连接到数据库，并作为前端来管理数据.数据最多可以同时被 25 个用户修改.
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.
我的第一反应是推荐其他东西，例如 Web 前端.但是假设客户坚持使用 Excel 解决方案，您认为它可行吗?您会从中看到哪些陷阱?
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?
我的疑惑是:
- 数据完整性(如何管理同时修改相同数据的用户)
- 不必要地移动了大量数据(打开 Excel 工作簿时，我想必须传输整个数据库)
- 安全性(以安全的方式仅向适当的用户显示部分数据将具有挑战性 - 请参阅前一点)
- 使用工具 (Excel) 做某事，但它并不擅长(请原谅双关语)
- 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)
推荐答案
我一直这样做.不，您不必引入整个数据库甚至整个表.我使用 ADO 和 VBA 并通过 Command 对象发送 SQL 语句.例如，我有一个带有 Excel 前端的版税数据库.
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.
用户输入发票编号，SELECT 语句检索该记录并填充一些自定义类.用户输入/修改一些数据并单击保存".然后该类有一个方法可以根据情况使用 UPDATE 或 INSERT 将记录写回数据库.
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.
在月底，用户输入一个日期范围并将一些记录检索到报告中，同样只是一个 SELECT 语句填充一些类并输出到工作表.
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.
使用事务，以便在遇到任何记录锁定问题时可以回滚，但如果有 25 个用户，您可能不会.
Use Transactions so you can roll back if you hit any record locking problems, but with 25 users you probably won't.
这篇关于使用 Excel 作为前端同时访问数据库 - 可行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
