Access 2010:链接数据库、参考还是加载项? [英] Access 2010: linked database, reference, or add-in?

问题描述

环顾四周，找到了各种各样的答案，但没有什么最近能真正比较这些选项的利弊.所以我想我会要求社区权衡你喜欢哪条路线以及为什么.

背景

这就是我们所拥有的:

• 常见的访问模块集和在众多协议数据库中使用的类(Access 2010 *.accdb 拆分前端/后端)
• 前端链接到后端数据库表 &代码(链接的数据库)
• 后端包含特定于协议的数据和代码
• 公共模块/类数据库不应由用户直接编辑

已知

加载项和db 参考数据库:

• 每次更改时都需要重新分发(即使其中没​​有更改代码).
• 必须在他们的 IDE 与协议数据库的 IDE 中进行编辑(否则您将丢失您的编辑，因为该数据库不是公共代码的)

问题

公共模块/类数据库应该如何连接到协议数据库?

• 链接数据库就像后端一样
• 将其附加为 IDE 中的参考(工具 > 参考)
• 创建加载项并将其添加为参考

你会怎么做，为什么?

优点/缺点是什么?

哪个选项可以最大限度地提高性能?

解决方案

首先感谢您@Erik 和 @Gustav 供您输入.

解决方案

在我的用例中选择的解决方案是使用包含模块和类的通用参考库数据库.

虽然我考虑了 @Erik's 解决方案，但最终将模块和类复制到每个协议中数据库有点像发送它们的副本，然后可以从主模块/类中转移.这在一定程度上是转移到单一参考数据库的一个原因 - 避免传播到其他数据库，因此它们只有一个代码源.

警告:如果参考库解决方案存在性能问题，类和模块可能必须从公共库数据库中提取，ala @Erik's 解决方案.>

数据库作为参考库

公共模块和类包含在数据库 (*.accdb) 中.每当需要另一个数据库中的代码时，都会添加一个引用，就像引用任何其他代码库一样(VB IDE - Tools > References).唯一的区别是您浏览... 到库并确保在搜索过滤器中选择Microsoft Access Databases (*.accdb).

如果参考图书馆数据库保存在一个公共位置，重新参考应该不会出现问题，尽管重新连接参考很容易(与首先连接一样).

我还将常见的版本控制和开发模块分离到类似的独立数据库中，这些数据库可以在开发期间需要时引用，并在给定数据库投入生产时取消引用.

开发陷阱"

实际上，这些比真正的陷阱"更需要考虑/要记住:

- 编辑库类 &图书馆数据库中的模块

如果您从引用库数据库的数据库中编辑类或模块，一旦关闭您正在使用的数据库，您所做的编辑就会丢失.

您可以从您正在使用的数据库中尝试编辑 - 代码将运行，但它不会保存.

我经常打开 Notepad++ 或 OneNote 来复制 &将我正在测试的代码粘贴到库数据库中.这样我就可以继续在其他领域工作，并进行一系列更改以更新图书馆数据库.

- 在引用数据库中使用其组件之前编译库数据库

这更像是一个工作流程问题 - 确保您保存 &在库数据库中编译，然后在完成更改后打开引用数据库并先关闭库数据库.

资源

以下是一些被证明有用的链接:

• 在多访问数据库中使用集中式 vba 模块
• 在 Access 数据库应用程序中使用 VBA 代码库
• 将 VBA 项目引用为库
• 演示应用程序&VBA 代码库
• VBA 中的类
• 使用数据库库访问应用程序中的文件
• 面向对象的 VBA:设计模式:简单工厂
• 面向对象的 VBA:静态类

第 6 篇参考文献(仅可通过 Internet 存档获得)在理清如何正确实例化类方面特别有用.最后，我选择创建一个具有多个函数(每个类一个 - 例如 NewClassABC())的单个工厂类(静态类)，用于实例化公共参考库数据库中的类.

到目前为止，一切都很好.

参考库数据库将代码放在一个地方，其他协议数据库可以在需要时更新到它.无需处理同一模块或类的多个副本和版本.

希望这有助于其他寻找共同点"的人库类型解决方案.

Looked around and found a variety of answers, but nothing recent that really compares these options pro and con. So I thought I'd ask the community to weigh in on which route you prefer and why.

Background

This is what we have:

• Common set of Access modules & classes used in numerous protocol databases (Access 2010 *.accdb split front/back-ends)
• Front-ends link to back-end database tables & code (linked dbs)
• Back-ends contain protocol specific data & code
• Common module/class database shouldn't be directly edited by users

Knowns

Add-ins & db reference databases:

• Require re-distribution each time they are changed (even if no code is changed within them).
• Must be edited within their IDE vs. the IDE of the protocol database (or you'll lose your edits since that db isn't the common code's)

Questions

How should the common module/class database be connected to the protocol databases?

• linked database just like back-ends are
• attach it as a reference in the IDE (Tools > Reference)
• create an add-in and add it as a reference

How would you do it and why?

What are the pros/cons?

Which option would maximize performance?

解决方案

First a big "thank you" to @Erik and @Gustav for your inputs.

Solution

The solution opted for in my use case was to use a common reference library database that houses modules and classes.

While I considered @Erik's solution, in the end copying over the modules and classes into each protocol database was a bit too much like sending out copies of them which could then devolve from the master modules/classes. This in part was one reason to shift to the single reference database - to avoid propagation into the other databases so there would be a single code source for them.

Caveat: If there are performance issues with the reference library solution, classes & modules may have to be pulled from the common library database ala @Erik's solution.

Database as Reference Library

The common modules and classes are contained within a database (*.accdb). Whenever the code is desired in another database, a reference is added like referencing any other code library (VB IDE - Tools > References). The only difference is that you Browse... to the library and make sure you select Microsoft Access Databases (*.accdb) in the search filter.

If the reference library database is kept in a common location, there shouldn't be issues with re-referencing, although re-connecting the reference is easily done (same as connecting in the first place).

I've also separated out common version control and development modules into similar separate databases that can be referenced when desired during development and de-referenced when a given database goes to production.

Development "Gotchas"

Actually these are more considerations/things to remember than true "gotchas":

- Edit library classes & modules in the library database

If you edit a class or module from the database that references the library database you will lose the edits as soon as you close the database you were working in.

You can try out edits from the database you're working in - the code will run, but it doesn't save.

I often have Notepad++ or OneNote open to copy & paste over code I'm testing to the library database. That way I can continue working on other areas and have a set of changes to update the library database.

- Compile the library database before using its components in the referencing database

This is more a workflow issue - make sure you save & compile in the library database, then open your referencing database after you've completed your changes and closed the library database first.

Resources

Here are some links which proved helpful:

• Using a centralized vba module in multiple access databases
• Using VBA Code Libraries in Access Database Applications
• Referencing VBA Projects as Libraries
• Demonstration Applications & VBA Code Libraries
• Classes in VBA
• Using Database Library Files in Your Access Application
• Object Oriented VBA: Design Patterns: Simple Factory
• Object Oriented VBA: Static Classes

The 6th reference (available only via the internet archive) has been particularly helpful in sorting out how to properly instantiate classes. In the end I opted to create a single Factory class (static class) with multiple functions (one per class - e.g. NewClassABC()) that instantiates the class within the common reference library database.

So far, so good.

The reference library database has the code in one place and other protocol databases can be updated to it when desired. No dealing with multiple copies and versions of the same module or class.

Hope this helps others looking for a "common" library type solution.

这篇关于Access 2010:链接数据库、参考还是加载项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！