Access 编程的最佳实践
我想知道一些可以改进使用 Access 和 VBA 编程语言设计解决方案的过程的想法。当然，我并不是在谈论一般的最佳编程实践，而只是与 Access 和 VBA 直接相关的这些实践。
大家都知道，VBA对面向对象编程的支持很差，没有继承、多态性等。那么如何同时保证DRY和KISS呢？有一些解决方案如何在 VBA 中实现其他语言中常见的模式和策略，但坦率地说，它们往往过于复杂。其中哪些值得实施？
在开始一个新的 Access 项目（如果有的话；））之前，我希望收集最佳实践的集合，因为根据我的经验，我知道使用 Access 中的 VBA（以及 Access 本身），避免不良的设计概念和以混乱、不可读且重复多次的代码结束。
I'm wondering about some ideas which can improve process of designing solutions using Access and VBA programming language. Of course I'm not talking about best programming practices in general, but only these directly related to Access and VBA.
Everybody knows, that VBA has poor object-oriented programming support, there is no inheritance, polymorphism and so on. So how to ensure DRY and KISS at one time? There are some solutions how to implement common in other languages patterns and strategies in VBA, but frankly speaking, they are often overcomplicated. Which of those are worth to implement?
Before I start a new Access project (if any ;) ), I wish to gather collection of best practices, because from my experience I know that with VBA in Access (and with Access in itself) it's very challenging to avoid bad design concepts and to end with messy, unreadable and repeated multiple times code.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我想在这里添加一些与同一问题以某种方式相关的其他问题和答案。这些指针可能会导致我自己对这些问题的答案，但请不要犹豫，浏览其他人的答案！
MS Access 作为企业软件
测试 MS-Access 应用程序的最佳方法
在 MS-Access 上与多名程序员合作
有关使用 MS-Access 中的 SQL Server GUID 的建议
我必须承认其中之一Access 的主要限制是有限的对象模型。我对无法将自己的属性和方法添加到 Form 对象感到特别恼火。我最近通过创建 2 个额外的对象找到了解决此问题的有效方法：
“AllMyForms”对象，
它实际上维护 2 个对象集合：一个是标准 Access 表单集合，另一个是“customForm”对象的所有实例的集合。这两个集合都使用打开的表单的 hwnd 属性进行索引（或者更具体地说，是表单实例的 hwnd 属性，允许我打开同一表单的多个实例）。
“customForm”对象，
其中列出了表单实例的自定义属性和方法
通过这种方式，我可以引用以下属性：
accessForms：引用标准属性和方法
通过以下方式引用访问表单的 .name 属性其 .hwnd 值
顺便说一句，以下 debug.print 将给出相同的结果：
自定义表单：属性
将引用用于创建表单实例的基础记录集的 SELECT 子句
自定义表单：方法
.agregate 方法可用于 customForm 对象，将计算表单“列”的 sum/min/max/avg（即连续表单中控件的值的总和）：
将为我提供表单当前/活动实例上显示的所有“lineAmount”值的总和。
I'd like to add here some other questions and answers related in a way or another to the same issue. The pointers might lead to my own answer to these questions, but do not hesitate to browse other's answers!
MS Access as enterprise software
Best way to test an MS-Access application
Working with multiple programmers on MS-Access
Recommendations on using SQL server GUIDS from MS-Access
I must admit that one of the main constraints of Access is the limited object model. I was specifically annoyed by the lack of possibilities to add my own properties and methods to the Form object. I recently found an efficient turnaround to this problem by creating 2 extra objects:
the "AllMyForms" object,
which in fact maintain 2 object collections: one is the standard Access forms collection, the other one is a collection of all the instances of the "customForm" object. Both collections are indexed with the hwnd property of an opened form (or, to be more specific, the hwnd property of the instance of a form, allowing me to open multiple instances of the same form).
the "customForm" object,
which lists my custom properties and methods of a form's instance
In this way, I can refer to properties such as:
accessForms:referring to the standard properties and methods
refers to the .name property of the access form through its .hwnd value
By the way, the following debug.print will then give me the same result:
Custom forms:properties
will refer to the SELECT clause used to create the underlying recordset of the form's instance
Custom forms:methods
The .agregate method, available for a customForm object, will calculate the sum/min/max/avg of a form "column" (ie sum of values for a control in a continuous form):
Will give me the sum of all "lineAmount" values displayed on the current/active instance of a form.
Access 编程最佳实践的权威来源是这本书：
Access 2002 桌面开发人员手册
http://www.amazon.com/Access-2002-Desktop-Developers-Handbook/ dp/0782140092
如果您认真对待 Access 编程，您应该获得一份副本。这些人是专家。
我意识到这本书似乎过时了，但其中的所有信息仍然适用。我想它永远不会更新，因为这种开发是一个小众领域。但 Access 的内部变化并没有太大（它是仅存的仍使用 VB6 方言的软件开发工具之一），并且书中的大部分信息仍然很好。
重点介绍客户端/服务器开发的配套书籍位于：
Access 2002 Enterprise Developer's Handbook
http://www.amazon.com/Access-2002-Enterprise-Developers-Handbook/ dp/0782140106
The definitive source for best practices in Access programming is this book:
Access 2002 Desktop Developer's Handbook
http://www.amazon.com/Access-2002-Desktop-Developers-Handbook/dp/0782140092
You should get a copy if you're serious about programming in Access. These guys are the experts.
I realize the book seems dated, but all of the information in it still applies. I suppose it never got updated because this kind of development is a bit of a niche area. But Access has not changed all that much internally (it's one of the only remaining software development tools left that still uses what amounts to a dialect of VB6), and most of the information in the book is still good.
The companion book that focuses on Client/Server development is here:
Access 2002 Enterprise Developer's Handbook
http://www.amazon.com/Access-2002-Enterprise-Developers-Handbook/dp/0782140106
当我进行 Access 编程时，我总是必须做的一件事是出于绑定原因使用大量隐藏字段。我确保使该字段不可见，并将该字段的颜色更改为前景白色和背景红色，以便人们知道这是一个隐藏字段。
我使用的另一个最佳实践是对所有共享代码使用模块。养成将大量可重用代码放入模块的习惯。
One thing i always had to do when I did Access programming was the use of a lot of hidden fields for binding reasons. I made sure that i made the field invisible and also changed the color of the field to foreground white and background red so that people knew this was a hidden field.
Another best practice I used was using modules for all of my shared code. Get into the habit of putting a lot of your reusable code in modules.