MS Access编程概述

作为前端访问

好吧，人们对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作为一项技术的反对都建立在其早期历史的基础上：由于向后兼容性是微软认为重要的，因此多年来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成为一个稳定的构建平台on。

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非常简单），并且它可以与外部库（事件写入。 Net）。

• 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及更高版本中的改进控件使应用程序以现代方式显示和运行。走了可怕的旧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运行时，您的前端应用程序可以在没有用户干涉其内部的情况下运行在他们的机器上。它也是免费的。


• 不要对抗工具：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 文件。 Windows中默认安装32位驱动程序（仍然存在于Win8中）并且始终可以依赖。


• ACE是Access2007 / 2010/2013使用的新 .accdb 格式。它比旧版本增加了限制和增强功能，但它仍然可以与 .mdb 对话。
  
  您必须安装 Microsoft数据库引擎（如果是完整版或虽然已安装Access 2007/2010/2013的运行时。）


• 不要尝试使用64位版本的Access / ACE驱动程序。这是未来，但它引入了许多新问题，特别是如果您使用第三方库或某些客户安装了32位Office（您不能混用和匹配32位和64位办公组件）。
  
  同样，不要在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.