使用 Excel 作为 Access 数据库的前端（使用 VBA）

Using Excel as front end to Access database (with VBA)

提问by Reed Copsey

I am building a small application for a friend and they'd like to be able to use Excel as the front end. (the UI will basically be userforms in Excel). They have a bunch of data in Excel that they would like to be able to query but I do not want to use excel as a database as I don't think it is fit for that purpose and am considering using Access. [BTW, I know Access has its shortcomings but there is zero budget available and Access already on friend's PC]

我正在为一位朋友构建一个小型应用程序，他们希望能够使用 Excel 作为前端。（UI 基本上是 Excel 中的用户表单）。他们在 Excel 中有一堆数据，他们希望能够查询，但我不想将 excel 用作数据库，因为我认为它不适合该目的，正在考虑使用 Access。[顺便说一句，我知道 Access 有它的缺点，但可用预算为零，而且 Access 已经在朋友的 PC 上]

To summarise, I am considering dumping a bunch of data into Access and then using Excel as a front end to query the database and display results in a userform style environment.

总而言之，我正在考虑将一堆数据转储到 Access 中，然后使用 Excel 作为前端来查询数据库并在用户表单样式环境中显示结果。

Questions:

问题：

1. How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?
2. Do I pay a performance penalty (vs.using forms in Access as the UI)?
3. Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?
4. Any other things I should be aware of?

1. 使用 ADO/DAO 从 Excel 链接到 Access 有多容易？它在功能方面是否非常有限，或者我可以发挥创意吗？
2. 我是否支付性能损失（与使用 Access 中的表单作为 UI）？
3. 假设将始终使用 Excel VBA 中的 ADO/DAO 命令更新数据库，这是否意味着我可以让多个 Excel 用户使用同一个 Access 数据库而不会遇到任何并发问题等？
4. 还有什么我应该注意的事情吗？

I have strong Excel VBA skills and think I can overcome Access VBA quite quickly but never really done Excel / Access link before. I could shoehorn the data into Excel and use as a quasi-database but that just seems more pain than it is worth (and not a robust long term solution)

我有很强的 Excel VBA 技能，并认为我可以很快克服 Access VBA，但以前从未真正做过 Excel / Access 链接。我可以将数据硬塞到 Excel 中并用作准数据库，但这似乎比它的价值更痛苦（而且不是一个强大的长期解决方案）

Any advice appreciated.

任何建议表示赞赏。

Alex

亚历克斯

回答by Reed Copsey

I'm sure you'll get a ton of "don't do this" answers, and I must say, there is good reason. This isn't an ideal solution....

我相信你会得到很多“不要这样做”的答案，我必须说，这是有充分理由的。这不是一个理想的解决方案......

That being said, I've gone down this road (and similar ones) before, mostly because the job specified it as a hard requirement and I couldn't talk around it.

话虽如此，我以前也走这条路（和类似的路），主要是因为工作将其指定为一项硬性要求，我无法谈论它。

Here are a few things to consider with this:

这里有一些事情需要考虑：

How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?

使用 ADO/DAO 从 Excel 链接到 Access 有多容易？它在功能方面是否非常有限，或者我可以发挥创意吗？

It's fairly straitforward. You're more limited than you would be doing things using other tools, since VBA and Excel forms is a bit more limiting than most full programming languages, but there isn't anything that will be a show stopper. It works - sometimes its a bit ugly, but it does work. In my last company, I often had to do this - and occasionally was pulling data from Access and Oracle via VBA in Excel.

这是相当直截了当的。与使用其他工具做事相比，您受到的限制更多，因为 VBA 和 Excel 表单比大多数完整的编程语言更具限制性，但没有任何东西会成为阻碍。它有效 - 有时它有点难看，但它确实有效。在我上一家公司，我经常不得不这样做 - 偶尔会通过 Excel 中的 VBA 从 Access 和 Oracle 中提取数据。

Do I pay a performance penalty (vs.using forms in Access as the UI)?

我是否支付性能损失（与使用 Access 中的表单作为 UI）？

My experience is that there is definitely a perf. penalty in doing this. I never cared (in my use case, things were small enough that it was reasonable), but going Excel<->Access is a lot slower than just working in Access directly. Part of it depends on what you want to do....

我的经验是肯定有性能。这样做的惩罚。我从不关心（在我的用例中，事情足够小，这是合理的），但是使用 Excel<->Access 比直接在 Access 中工作要慢得多。部分取决于你想做什么......

In my case, the thing that seemed to be the absolute slowest (and most painful) was trying to fill in Excel spreadsheets based on Access data. This wasn't fun, and was often very slow. If you have to go down this road, make sure to do everything with Excel hidden/invisible, or the redrawing will absolutely kill you.

就我而言，似乎绝对最慢（也是最痛苦）的事情是尝试填写基于 Access 数据的 Excel 电子表格。这并不有趣，而且通常很慢。如果您必须走这条路，请确保在 Excel 隐藏/不可见的情况下执行所有操作，否则重绘绝对会杀死您。

Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?

假设将始终使用 Excel VBA 中的 ADO/DAO 命令更新数据库，这是否意味着我可以让多个 Excel 用户使用同一个 Access 数据库而不会遇到任何并发问题等？

You're pretty much using Excel as a client - the same way you would use a WinForms application or any other tool. The ADO/DAO clients for Access are pretty good, so you probably won't run into any concurrency issues.

您几乎将 Excel 用作客户端 - 与使用 WinForms 应用程序或任何其他工具的方式相同。Access 的 ADO/DAO 客户端非常好，因此您可能不会遇到任何并发问题。

That being said, Access does NOT scale well. This works great if you have 2 or 3 (or even 10) users. If you are going to have 100, you'll probably run into problems. Also, I tended to find that Access needed regular maintenance in order to not have corruption issues. Regular backups of the Access DB are a must. Compacting the access database on a regular basis will help prevent database corruption, in my experience.

话虽如此，Access 不能很好地扩展。如果您有 2 或 3 个（甚至 10 个）用户，这很有效。如果你打算有 100 个，你可能会遇到问题。此外，我倾向于发现 Access 需要定期维护，以免出现损坏问题。必须定期备份 Access DB。根据我的经验，定期压缩访问数据库将有助于防止数据库损坏。

Any other things I should be aware of?

还有什么我应该注意的事情吗？

You're doing this the hard way. Using Excel to hit Access is going to be a lot more work than just using Access directly.

你这样做很困难。使用 Excel 访问 Access 将比直接使用 Access 要多得多。

I'd recommend looking into the Access VBA API - most of it is the same as Excel, so you'll have a small learning curve. The parts that are different just make this easier. You'll also have all of the advantages of Access reporting and Forms, which are much more data-oriented than the ones in Excel. The reporting can be great for things like this, and having the Macros and Reports will make life easier in the long run. If the user's going to be using forms to manage everything, doing the forms in Access will be very, very similar to doing them in Excel, and will look nearly identical, but will make everything faster and smoother.

我建议您查看 Access VBA API - 其中大部分与 Excel 相同，因此您的学习曲线很小。不同的部分只是让这更容易。您还将拥有 Access 报告和表单的所有优势，它们比 Excel 中的更面向数据。报告对于此类事情可能非常有用，从长远来看，拥有宏和报告将使生活更轻松。如果用户要使用表单来管理所有内容，那么在 Access 中制作表单将与在 Excel 中制作它们非常非常相似，并且看起来几乎相同，但会使一切变得更快、更顺畅。

回答by Dick Kusleika

I do this all the time. If you're using ADO, you're not really using Access, but Jet, the underlying database. That means anybody with Excel can use the app - Access not required. Oh I should mention, the place I work bought a bunch of Office Small Business licenses - no Access. Prior to working here, I would have assumed that anyone who had Excel would also have Access. Not so.

我一直这样做。如果您使用的是 ADO，那么您实际上并不是在使用 Access，而是在使用底层数据库 Jet。这意味着任何拥有 Excel 的人都可以使用该应用程序 - 不需要访问权限。哦，我应该提一下，我工作的地方买了一堆 Office 小型企业许可证 - 没有访问权限。在这里工作之前，我会假设任何拥有 Excel 的人也将拥有 Access。不是这样。

I create one class for every table in Access. I very rarely run queries through ADO, instead I keep that logic in the class modules. I read in with a SELECT statement and write out with and UPDATE or INSERT using the Execute method of the ADODB.Connection object.

我为 Access 中的每个表创建一个类。我很少通过 ADO 运行查询，而是将该逻辑保留在类模块中。我使用 SELECT 语句读入并使用 ADODB.Connection 对象的 Execute 方法使用 UPDATE 或 INSERT 写出。

See http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/

见http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/

if you want to see how I set up my code.

如果你想看看我是如何设置我的代码的。

To answer your questions: It will be a small learning curve for you if you already know Excel VBA, but there will be some learning to do; you will pay a performance penalty over doing it all in Access, but it's not that bad and only you can decide if it's worth it; and you can have multiple people accessing the database.

回答您的问题：如果您已经了解 Excel VBA，这对您来说将是一个很小的学习曲线，但是会有一些学习要做；在 Access 中执行所有操作会导致性能下降，但这并没有那么糟糕，只有您可以决定是否值得；并且您可以让多个人访问数据库。

回答by DJ.

Just skip the excel part- the excel user forms are just a poor man's version of the way more robust Access forms. Also Access VBA is identical to Excel VBA - you just have to learn Access' object model. With a simple application you won't need to write much VBA anyways because in Access you can wire things together quite easily.

只需跳过 excel 部分- excel 用户表单只是更强大的 Access 表单方式的穷人版本。此外，Access VBA 与 Excel VBA 相同 - 您只需要学习 Access 的对象模型。使用简单的应用程序，您无论如何都不需要编写太多的 VBA，因为在 Access 中您可以很容易地将事物连接在一起。

回答by Scott

If the end user has Access, it might be easier to develop the whole thing in Access. Access has some WYSIWYG form design tools built-in.

如果最终用户拥有 Access，则在 Access 中开发整个内容可能会更容易。Access 内置了一些 WYSIWYG 表单设计工具。

回答by Renaud Bompuis

Unless there is a strong advantage to running your user form in Excel then I would go with a 100% Access solution that would export the reports and data to Excel on an ad-hoc basis.

除非在 Excel 中运行您的用户表单有很大的优势，否则我会使用 100% Access 解决方案，该解决方案可以临时将报告和数据导出到 Excel。

From what you describe, Access seems the stronger contender as it is built for working with data:
you would have a lot more tools at your disposal to solve any data problems than have to go around the limitations of Excel and shoehorn it into becoming Access...

根据您的描述，Access 似乎是更强大的竞争者，因为它是为处理数据而构建的：
您可以使用更多的工具来解决任何数据问题，而不必绕过 Excel 的限制并将其硬塞进 Access。 ..

As for your questions:

至于你的问题：

1. Very easy. There have been some other questions on SO on that subject.
   See for instance this oneand that one.

2. Don't know, but I would guess that there could be a small penalty.
   The biggest difficulty I see is trying to get all the functionalities that Access gives you and re-creating some of these in Excel.

3. Yes, you can have multiple Excel users and a single Access database.
   Here again, using Access as a front-end and keeping the data in a linked Access database on your network would make more sense and it's easy as pie, there's even a wizard in Access to help you do that: it's just 1 click away.

1. 好简单。关于该主题，还有一些关于 SO 的其他问题。
   例如，参见这个和那个。

2. 不知道，但我想可能会有一个小惩罚。
   我看到的最大困难是尝试获得 Access 为您提供的所有功能，并在 Excel 中重新创建其中的一些功能。

3. 是的，您可以拥有多个 Excel 用户和一个 Access 数据库。
   同样，使用 Access 作为前端并将数据保存在网络上的链接 Access 数据库中会更有意义，而且这很容易，Access 中甚至有一个向导可以帮助您做到这一点：只需单击 1 次即可。

Really, as most other people have said, take a tiny bit of time to get acquainted with Access, it will save you a lot of time and trouble.
You may know Excel better but if you've gone 80% of the way already if you know VBA and are familiar with the Office object model.

确实，正如大多数其他人所说，花一点时间来熟悉 Access，它将为您节省大量时间和麻烦。
您可能更了解 Excel，但如果您了解 VBA 并熟悉 Office 对象模型，那么您已经掌握了 80% 的知识。

Other advantages of doing it in Access: the Access 2007 runtime is free, meaning that if you were to deploy to app to 1 or 30 PC it would cost you the same: nothing.
You only need one full version of Access for your development work (the Runtime doesn't have the designers).

在 Access 中执行此操作的其他优势：Access 2007 运行时是免费的，这意味着如果您要将应用程序部署到 1 或 30 台 PC，您将花费相同的费用：无需任何费用。
您的开发工作只需要一个完整版本的 Access（运行时没有设计器）。

回答by Martin

It really depends on the application. For a normal project, I would recommend using only Access, but sometimes, the needs are specific and an Excel spreadsheet might be more appropriate.

这真的取决于应用程序。对于普通项目，我建议仅使用 Access，但有时，需求是特定的，Excel 电子表格可能更合适。

For instance, in a project I had to develop for a former employer, the need was to give access to different persons on forms(pre-filled with some data, different for each person) and have them complete them, then re-import the data.

例如，在我必须为前雇主开发的一个项目中，需要在表单上访问不同的人（预先填写一些数据，每个人不同）并让他们完成它们，然后重新导入数据。

Since the form was using heavy number crunching, it made more sense to build it in Excel.

由于表单使用了大量的数字运算，因此在 Excel 中构建它更有意义。

The Excel workbooks for the different persons were built from a template using VBA, then saved in a proper location, with the access rights on the folder.

不同人员的 Excel 工作簿是使用 VBA 从模板构建的，然后保存在适当的位置，并具有文件夹的访问权限。

All workbooks were attached as External tables to the workbooks, using named ranges. I could then query the workbooks from the Access Application. All administrative stuff was made from the db, but the end users only had access to their respective workbook.

所有工作簿都使用命名范围作为外部表附加到工作簿。然后我可以从 Access 应用程序查询工作簿。所有管理工作都是从数据库中制作的，但最终用户只能访问他们各自的工作簿。

Developping an Excel/Access application this way was a pleasant experience and the UI was more user-friendly than it would have been using Access.

以这种方式开发 Excel/Access 应用程序是一种愉快的体验，而且用户界面比使用 Access 时更加用户友好。

I have to say that in this case, it would have taken a lot more time doing it in Access than it took using Excel. Also, the Application Object Model seems better though in Excel than in Access.

我不得不说，在这种情况下，在 Access 中执行此操作比使用 Excel 花费的时间要多得多。此外，应用程序对象模型在 Excel 中似乎比在 Access 中更好。

If you plan to use Excel as a front-end, do not forget to lock all the cells, but the editable ones and don't be affraid to use masked rows and columnns (to construct output tables for the access database, to perform intermediate calculations, etc).

如果您打算使用 Excel 作为前端，请不要忘记锁定所有单元格，但要锁定可编辑的单元格，并且不要害怕使用屏蔽的行和列（为访问数据库构建输出表，执行中间计算等）。

You should also turn off autocalculation while importing data.

您还应该在导入数据时关闭自动计算。

回答by Patrick Honorez

It's quite easy and efficient to use Excel as a reporting tool for Access data. A quick "non programming" approach is to set a List or a Pivot Table, linked to your External Data source. But that's out of scope for Stackoverflow.
A programmatic approach can be very simple:

将 Excel 用作 Access 数据的报告工具非常简单有效。一种快速的“非编程”方法是设置链接到外部数据源的列表或数据透视表。但这超出了 Stackoverflow 的范围。
程序化方法可以非常简单：

strProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";"
Set cnn = New ADODB.Connection  
cnn.Open strProv
Set rst = New ADODB.Recordset
rst.Open strSql, cnn
myDestRange.CopyFromRecordset rst

That's it !

就是这样 ！

回答by Alexander Galkin

I did it in one project of mine. I used MDB to store the data about bills and used Excel to render them, giving the user the possibility to adapt it.

我在我的一个项目中做到了。我使用 MDB 来存储有关账单的数据，并使用 Excel 来呈现它们，让用户可以对其进行调整。

In this case the best solution is:

在这种情况下，最佳解决方案是：

1. Not to use any ADO/DAO in Excel. I implemented everything as public functions in MDB modules and called them directly from Excel. You can return even complex data objects, like arrays of strings etc by calling MDB functions with necessary arguments. This is similar to client/server architecture of modern web applications: you web application just does the rendering and user interaction, database and middle tier is then on the server side.

2. Use Excel forms for user interaction and for data visualisation.

3. I usually have a very last sheet with some names regions for settings: the path to MDB files, some settings (current user, password if needed etc.) -- so you can easily adapt your Excel implementation to different location of you "back-end" data.

1. 不要在 Excel 中使用任何 ADO/DAO。我在 MDB 模块中将所有内容都实现为公共函数，并直接从 Excel 调用它们。通过使用必要的参数调用 MDB 函数，您甚至可以返回复杂的数据对象，如字符串数组等。这类似于现代 Web 应用程序的客户端/服务器架构：您的 Web 应用程序只进行渲染和用户交互，然后数据库和中间层在服务器端。

2. 使用 Excel 表单进行用户交互和数据可视化。

3. 我通常有最后一张表，其中包含一些用于设置的名称区域：MDB 文件的路径、一些设置（当前用户、密码，如果需要等）——因此您可以轻松地将 Excel 实现调整到您的不同位置“back-”结束”的数据。

回答by pro3carp3

Given the ease of use of Access, I don't see a compelling reason to use Excel at all other than to export data for number crunching. Access is designed to easily build data forms and, in my opinion, will be orders of magnitude easier and less time-consuming than using Excel. A few hours to learn the Access object model will pay for itself many times over in terms of time and effort.

鉴于 Access 的易用性，除了导出数据以进行数字运算之外，我看不出有什么令人信服的理由使用 Excel。Access 旨在轻松构建数据表单，在我看来，它比使用 Excel 更容易、更省时。就时间和精力而言，花几个小时来学习 Access 对象模型会物超所值。

回答by pro3carp3

To connect Excel to Access using VBA is very useful I use it in my profession everyday. The connection string I use is according to the program found in the link below. The program can be automated to do multiple connections or tasks in on shot but the basic connection code looks the same. Good luck!

使用 VBA 将 Excel 连接到 Access 非常有用，我每天都在我的职业中使用它。我使用的连接字符串是根据下面链接中的程序。该程序可以自动执行多个连接或任务，但基本连接代码看起来相同。祝你好运！

http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao

http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao