使用Excel作为前端访问数据库（使用VBA） [英] Using Excel as front end to Access database (with VBA)
问题描述
我正在为一个朋友构建一个小应用程序，他们希望能够使用Excel作为前端。 （UI将基本上是Excel中的用户形式）。他们在Excel中有一堆数据，他们希望能够查询，但是我不想使用excel作为数据库，因为我不认为它适合于这个目的，正在考虑使用Access。 [BTW，我知道Access有它的缺点，但没有预算可用，Access已经在朋友的PC上]
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]
总而言之，我正在考虑将一堆数据转储到Access然后使用Excel作为前端来查询数据库并在用户形式样式环境中显示结果。
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.
问题：
- 使用ADO / DAO从Excel中链接到Access有多容易？在功能方面是否相当有限，或者可以获得创意吗？
- 我是否支付性能损失（与Access中的表单作为UI）？
- 假设数据库将始终使用Excel VBA中的ADO / DAO命令进行更新，这是否意味着我可以使用该单个Access数据库的多个Excel用户，而不会遇到任何并发问题等。 >
- 我应该注意的其他任何事情？
我拥有强大的Excel VBA技能，认为我可以克服Access VBA很快，但从来没有真正做过Excel / Access链接。我可以将数据输入Excel并用作准数据库，但这似乎比值得（但不是强大的长期解决方案）更为困难。
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)
任何建议赞赏。
Alex
推荐答案
我相信你会得到一吨不要这样做的答案，我必须说，有很好的理由。这不是一个理想的解决方案....
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:
使用ADO / DAO从Excel中链接到Access有多简单？在功能方面相当有限，还是可以获得创意？
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?
这是相当相似的。你比使用其他工具做的更有限，因为VBA和Excel表单比大多数完整的编程语言有一些限制，但是没有什么可以显示停止。它有效 - 有时它有点丑，但它的工作。在我最后一个公司，我经常不得不这样做 - 偶尔会通过Excel中的VBA从Access和Oracle中提取数据。
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.
支付性能损失（与Access中的表单作为UI）？
Do I pay a performance penalty (vs.using forms in Access as the UI)?
我的经验是，绝对是一个perf。这样做的惩罚我从来没有关心（在我的用例中，事情足够小，这是合理的），但是，直接在Access中直接运行Excel？一部分取决于你想做什么....
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....
在我的情况下，似乎是绝对最慢（最痛苦的）的事情是试图根据Access数据填写Excel电子表格。这不是很有趣，而且往往很慢。如果你必须走这条路，请确保使用Excel隐藏/隐藏的所有内容，否则重画将绝对杀死你。
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.
假设数据库将始终使用Excel VBA中的ADO / DAO命令进行更新，这是否意味着我可以使用该单个Access数据库的多个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作为客户端 - 与使用WinForms应用程序或任何其他工具相同。 Access的ADO / DAO客户端是非常好的，所以你可能不会遇到任何并发问题。
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.
就是说，Access不能很好地扩展。如果你有2或3（甚至10）的用户，这很好。如果你有100，你可能遇到问题。此外，我倾向于发现Access需要定期维护，以避免出现腐败问题。 Access DB的定期备份是必须的。在我的经验中，压缩访问数据库有助于防止数据库损坏。
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.
我应该注意的其他任何事情？ / p>
Any other things I should be aware of?
你这样做很困难。使用Excel打开Access将比使用Access直接更多的工作。
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.
我建议查看Access VBA API - 大部分是与Excel相同，所以你会有一个小的学习曲线。不同的部分只是使这更容易。您还将具有Access报表和表单的所有优势，它们比Excel中的数据更加面向数据。报告对于这样的事情来说可以是非常好的，并且宏和报告将长期使生活更容易。如果用户正在使用表单来管理所有内容，那么在Access中执行表单将非常非常类似于在Excel中执行的操作，并且将看起来几乎相同，但会使所有内容更加快速和平稳。
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.
这篇关于使用Excel作为前端访问数据库（使用VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
