MS Access 编程概述 [英] MS Access Programming Overview

问题描述

我是一名 Java EE 开发人员，刚刚有人联系我，希望我为他的业务应用程序提供报价，该应用程序可以与他们的 MS Access后端"集成.

I'm a Java EE developer and was just contacted by someone who wants me to put a quote together for an application for his business that can integrate with their MS Access "backend".

我希望发表这篇文章，只是对 MS Access 编程所涉及的最佳实践进行一般高级概述.我假设该程序将完全在 VB 中，但不知道我是否可以选择在 VB.NET 或(最好)C# 中编写一些东西.

I was hoping to post this and just get a general high-level overview of best practices involved with MS Access Programming. I assume the program would be entirely in VB, but didn't know if I would have the option of writing something in VB.NET or (preferably) C#.

另外，我鄙视表示层:任何用于 Access 应用程序的好的 GUI 构建器?

Also, I despise the presentation layer: any good GUI-builders for Access applications?

MS Access 程序员经常使用哪些常用工具和 API(单元测试框架、构建自动化系统等)?

What are some common tools and APIs (unit test frameworks, build automation systems, etc.) that MS Access programmers frequently use?

您会推荐任何链接或资源吗?

Any links or resources you would recommend?

这听起来像是一个非常简单的应用程序:获取输入的数据，将其与一些表格进行比较，然后将一些输出显示到屏幕上.我是一个相当不错的程序员，所以即使我以前从未做过 Access 程序，也不会太难上手.

It sounds like a pretty simple application: take inputted data, compare it to some tables, and throw some output to the screen. I'm a pretty decent programmer so even though I've never done an Access program before it couldn't be too difficult for me to pick up.

感谢您的任何想法或建议！

Thanks for any thoughts or suggestions!

推荐答案

作为前端访问

嗯，人们对 Access 有强烈的感觉.负面观点的主要原因是，它被没有正确数据库开发概念的非开发人员广泛使用，最终导致这些几乎无法运行、设计糟糕的应用程序让开发人员感到恐惧.

Access as a front-end

Well, people feel strongly about Access. The main reason for the negative view is that it is widely used by non developers who have no concept of proper database development and end-up with these barely working, horribly designed applications that bring dread into the heart of developers.

话虽如此，Access 只不过是一款入门门槛非常低的快速应用程序开发工具.

Having said that, Access is nothing more than a Rapid Application Development tool with a very low barrier of entry.

好与坏

Access 是一个相当古老的产品线，跨越近 20 年.许多人反对 Access 作为一项技术的原因在于其早期的历史:由于 Microsoft 认为向后兼容性很重要，因此多年来 Access 保留了它的大部分功能，无论好坏.

Access is a fairly old product line, spanning almost 20 years. A lot of people's objections to Access as a technology are grounded in its early history: since backward compatibility is something that Microsoft deems important, Access has retained most of its features, good or bad, over the years.

您可以看到选择 VBA、非 Winform 表单、提升模块而不是 OOP，这些选择是糟糕的选择，但这些早期设计决策的连续性使 Access 成为一个稳定的构建平台.

You can see the choice of VBA, non-winform forms, promotion of modules over OOP, to be bad choices, but the continuity of these early-on design decisions have made Access a stable platform to build on.

• VBA:是永不消亡的语言.它笨重、过时、缺乏语言的最新进展，但它也快速、易于学习、灵活、易于交互(例如调用 Win32 API 非常简单)，并且可以与外部库交互(事件用 .净).

• VBA: is the language that never dies. It's clunky, outdated, lacks recent advances in languages, but it's also fast, simple to learn, flexible, easy to interop with (for instance calling Win32 APIs is really simple), and it can be interfaced with external libraries (event written in .Net).

绑定表单:默认情况下，Access 可以轻松创建工作应用程序而无需一行代码.也可以创建大多数查询，而根本无需使用 SQL.如果你是一个控制狂，这可能会很烦人，但很容易从那里开始编写代码并控制一切.

Bound forms: by default Access makes it easy to create working applications without a line of code. Most queries can also be created without having to go down to SQL at all. If you're a control freak, it can be annoying, but it's easy to drop down to code and control everything from there.

第三方集成:尽管 Access 有很多附加组件，但我不建议使用大多数用户控件或第三方库，除非你真的必须这样做.它们很难在用户权限有限的环境中部署，而且版本控制可能会变得很麻烦.

Third-party integration: even though there are lots of add-ons to Access, I wouldn't recommend using most of the user-controls or third-party libraries unless you really have to. They can be very hard to deploy in environment with limited user rights and versioning can become hairy.

功能区:它可能是好是坏，取决于你与谁交谈，但微软已经在它上面投入了大量资金，它可能会存在一段时间.至少 Access 2007 和更高版本中的 Ribbon 和改进的控件使应用程序的外观和行为以现代方式呈现.摆脱了旧时可怕的 UI，您现在可以使用主题、HTML 布局和现代功能区做非常漂亮的事情.

Ribbon: it may be good or bad, depending on who you talk to, but Microsoft has invested a lot into it and it'll probably be there for a while. At least the Ribbon and the improved controls in Access 2007 and later make applications look and behave in a modern way. Gone the horrible UIs of old, you can now do really pretty things with themes, HTML layouts and a modern ribbon.

可靠性

大多数反对意见，尤其是关于可靠性的反对意见，根本不再正确.
一个精心设计的访问应用程序可以支持数十个并发用户.我有一个相当大的应用程序，它为一家拥有 150 个用户的制造公司管理采购/库存/质量/零件/项目，其中通常有 50 个用户在任何给定时间连接.多年来我没有任何腐败行为.

Most of the objections, especially about reliability, are simply no longer true.
A carefully designed Access Application can support dozens of concurrent users. I have a decent-size application managing Procurement/Stock/Quality/Parts/Projects for a manufacturing company that has 150 users, of which usually 50 are connected at any given time. I haven't had any corruption in years.

当然，您必须始终牢记 Access 是一个基于文件的多用户数据库，因此您不能期望它在网络不可靠或速度较慢的环境中毫无风险地工作，并通过 WiFi 连接到 Access 后端真的是自找麻烦，就像您也不会通过无线处理大型 Excel 文件一样.

Of course, you must always bear in mind that Access is a multi-user file-based database, so you cannot expect it to work without risk in environments with unreliable or slow networks, and connecting to a an Access backend through WiFi is really asking for trouble, just like you wouldn't work on large Excel file over wireless either.

维护是应用程序生命周期的一部分.预防性维护非常重要.
不要等待出现问题:在您的应用中构建一些管理工具，以帮助检查您的数据状态(确保一切一致，检测无效的用户输入等).
还定期压缩数据库(作为后端服务器上的日常自动化任务的一部分，我每晚在备份后执行此操作).

Maintenance is part of the lifecycle of an application. Preventive maintenance is extremely important.
Don't wait for something to go wrong: build some admin tools within your app that will help check the state of your data (make sure everything is coherent, detect invalid user inputs, etc).
Also compact the database regularly (I do it nightly after I make a backup as part of the daily automated tasks on the backend server).

一些随机提示

• 确保你的前端和后端是分开的:只有数据应该在后端，前端应该安装在每个用户的机器上.
• 从您的前端，打开一个指向数据库后端虚拟表的永久链接:保持始终在线的连接将大大提高性能.原因，看看这个 问题.
• 后端应位于浅层网络共享中(不要将其置于多个目录下).
• 请务必仔细考虑如何将更新自动部署到所有客户端.
  有很多方法可以做到这一点.我自己开发了，但您可以使用 ClickOnce 或 Tony Toews 的 自动前端更新程序
• 使用 运行时:如果您在他们的机器上部署访问运行时，您的前端应用程序可以运行而无需用户干预其内部.它也是免费的.
• 不要与工具抗争:Access 有一定的做事方式.只需使用可用的工具，而无需编写代码，直到您用尽 RAD 环境的功能.您可能会惊讶于无需任何代码就能取得如此多的成就.
• 没有什么能阻止您使用 OOP:定义类并将您的类手动绑定到表单、控制数据更新等.Access 中的默认行为倾向于鼓励快速开发，适用于小型项目，但如果您怀疑您的项目可能会发展，为未来做计划，就像您使用任何其他框架一样.

• Make sure your front-end and back-end are split: only data should be on the backend, and the front end should be installed on each user's machine.
• From your front-end, open a permanent link to a dummy table on the database back-end: keeping an always-on connection will greatly improve performance. For the reason why, have a look at this question.
• The backend should be in a shallow network share (don't put it deep under multiple directories).
• Make sure you think hard about how you will deploy your updates to all clients automatically.
  There are many ways to do this. I developed my own but you could use ClickOnce or Tony Toews's Auto-Front-End Updater
• Use the Runtime: your front-end application can run without users meddling with its internals if you deploy the Access Runtime on their machine. It's free too.
• Don't fight the tool: Access has a certain way of doing things. Just use the tools available without going to code until you have exhausted the capabilities of the RAD environment. You may be surprised by how much you can achieve without any code at all.
• Nothing stops you from using OOP: defining classes and binding your classes manually to your forms, controlling data updates, etc. The default behaviour in Access tends to encourage quick-development and is fine for small projects, but if you suspect that your project might grow, plan for the future, just like you would with any other framework.

现在，如果您想使用用其他东西(例如 C#)编写的前端，使用 Jet/ACE 驱动程序连接到 Access 后端数据库相当容易.

Now, if you want to use a front-end written in something else, say C#, it's fairly easy to use Jet/ACE drivers to connect to an Access back-end database.

• Jet 是较旧的 Access 驱动程序，仅支持 .mdb 文件.32 位驱动程序默认安装在 Windows 中(在 Win8 中仍然存在)，并且始终可以依赖.
• ACE 是 Access2007/2010/2013 使用的新 .accdb 格式.它比旧版本增加了限制和增强功能，但它仍然可以与 .mdb 对话.
  您必须在所有客户端上安装 Microsoft 数据库引擎(如果已安装完整版或 Access 2007/2010/2013 的运行时，则不需要).
• 不要尝试使用 64 位版本的 Access/ACE 驱动程序.这是未来，但它引入了许多新问题，特别是如果您使用 3rd-party 库或您的某些客户端安装了 32 位 Office(您不能混合和匹配 32 位和 64 位 Office 组件).
  同样，不要在 64 位模式下构建 C++/C#/Java/Python 前端应用程序并期望 32 位 ACE 驱动程序能够正常工作.将所有内容保留为 32 位，让一切正常运行，然后在需要时测试 64 位版本.
• 请始终保持至少一个从您的代码到后端数据库的连接，如上所述.
• 理想情况下，尝试抽象任何特定于 Access 的代码，以便以后更容易，以防您需要切换到基于服务器的数据库.您可以使用可以透明地与不同后端对话的 ORM，或者您至少可以将 SQL 查询分离到资源文件或分离的对象中，以便以后轻松替换.
• 根据负载(主要是需要在数据库中更改多少数据)，Access 后端可以轻松容纳 20-100 个用户而不会出现问题.如果一直有大量的插入和更新发生，事情将会恶化.有很多技巧可以用来让事情变得更好，并使 Access 可以很好地扩展，但您天生就会受到您正在构建的应用程序类型以及用户访问其数据的方式的限制.

• Jet is the older driver for Access and only supports .mdb files. 32 bits drivers are installed by default in Windows (still there in Win8) and can always be relied upon.
• ACE is the new .accdb format used by Access2007/2010/2013. It has increased limits and enhancements over the older version but it can still talk to an .mdb.
  You will have to install the Microsoft Database Engine on all the clients (not necessary if either a full version or the Runtime of Access 2007/2010/2013 is already installed though).
• Don't try to use 64bit versions of Access/ACE driver. It's the future but it introduces many new issues, especially if you are using 3rd-party libraries or some of your clients have 32bit Office installed (your can't mix and match 32bit and 64 bit office components).
  Similarly, don't build your C++/C#/Java/Python front-end app in 64bit mode and expect the 32 bit ACE driver to work. Keep everything in 32 bit, make things work, then test 64 bits versions if you really need to.
• Do always keep at least a single connection open to the back-end database from your code, as mentioned above.
• Ideally, try to abstract any Access-specific code to make it easier later in case you need to switch to a server-based database. You could use an ORM that can talk to different back-ends transparently, or you could at least separate your SQL queries into resource files or separate objects that can be easily replaced later.
• Depending on the load (mostly how much data need to be changed in the DB), an Access back-end can easily accommodate between 20-100 users without issues. Things will deteriorate if there are lots of insert and updates occurring all the time. There are lots of tricks that can be used to make things better and make Access scale well, but you are inherently limited by the type of application your are building and how users are accessing its data.

这篇关于MS Access 编程概述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！