从 VBA 使用 Access、MySQL、PostgreSQL 或 SQLite 数据库

重要提示：原始博客文章转换为 CodeProject 格式尚未完成。

您可以在此处找到原始文章。

我将尽快更新 CodeProject 文章。

引言

给吹毛求疵的人的提示：是的，Access 不是数据库引擎，只是 Jet/ACE 的图形前端，但我们会坚持这种简化。

示例应用程序（Excel + VBA）和 SQL 源代码可在此 zip 压缩包中找到。

如果您是一名高级 VBA 开发人员，并且构建了处理大量数据的应用程序，那么您很可能正在使用 Access 数据库来存储它们。

如果此设置完全符合您当前的需求，您完全掌握它，没有任何问题，并且您的需求在不久的将来不会发生变化，您可以跳过本文，继续享受您的系统。

事实上，您真的需要一个新的数据库管理系统（DBMS）吗？
迁移到其他 DBMS 的唯一论据通常是它们“更好”；虽然这对于某些技术能力来说是真的，但对于其他“指标”如简单性来说可能并非如此：Access 对于非 IT 人员来说易于理解和管理，并且通常与休息的Office 套件一起安装在默认的业务工作站设置中。

那么，假设您有充分的理由迁移到新的 DBMS，因为您已经到了需要支持以下至少一项功能的程度：互操作性、大容量存储、高并发性（数百用户）和/或高性能，而 Access 开始成为问题的一部分。
那么，如果您想提高数据库的质量，同时又不使您的总拥有成本（TCO）爆炸，您可以怎么做？

您的 TCO 主要由以下几部分组成：

• 许可成本：限制它们非常简单：使用免费的、通常是开源的数据库，并且只为支持付费。
• 管理成本：它们远超许可成本，并直接受 DBMS 复杂性的影响；因此，您需要一个简单的 DBMS，您可以像以前使用 Access 一样自行设置和管理，而无需系统或数据库管理员的帮助。
• 开发成本：您当前模式或 VBA 实现的任何额外更改以适应新的 DBMS 都将产生成本；因此，我们希望事情是透明的，并且没有额外的开发，特别是这意味着一个标准的基于 SQL 的 DBMS。

虽然这个等式可能有点复杂，但至少有三种解决方案。

• SQLite 是理想的选择，如果您对“单文件”模型满意，没有高并发限制，并且您只需要互操作性（与Mac OS、Linux、Unix…），更大的存储和/或成本节省，
• MySQL 和 PostgreSQL：如果您需要支持高并发、真正的大容量存储（例如几十 GB）、高级用户管理、性能调优和其他高级功能，您将不得不脱离单文件世界。
  如果您没有特定要求，那么 MySQL 和 PostgreSQL 对您来说将是相似的，并且同样能完成工作。然而，在这种情况下，我更倾向于 MySQL，不是因为其固有能力更优越（正如我所说，MySQL 和 PostgreSQL 对于简单设置来说大致相当），而是因为作为多年来的参考开源 DBMS，MySQL 受益于庞大的社区和工具集。此外，虽然您肯定会找到在良好条件下使用 PostgreSQL 的工具，但如果您需要向您的上级证明您的选择，选择标准解决方案而不是挑战者将使您处于更有利的位置。
  但既然我不是教条主义者，并且为了完整起见，我将同时介绍两者。

在本文中，我将简要介绍这三个 DBMS 的设置（并提供指向其他资源的链接以获取更详尽的说明），并通过一个使用 Access 的小型VBA 应用程序，一个革命性的待办事项列表管理器，来演示它们的使用。

数据模式

我们的应用程序使用的数据模式非常基础：一个具有 3 列的表。

• Description：任务的文本描述。
• Category：一个标签，有助于对您创建的任务集进行进一步分类。
• Due date：完成任务的截止日期，之后您可能会惹老板生气！

在Access中看起来是这样的。

Access 的SQL方言中对应的代码是：

CREATE TABLE Tasks
(
	Description	LONGTEXT,
	Category	VARCHAR(100),
	DueDate		DATETIME
); 

MySQL

安装

您可以从这里下载 MSI 安装程序：MySQL。

下载后，启动它并接受可能出现的任何 Windows 安全弹出窗口。
然后，您可以按照此幻灯片演示进行进一步说明。
[simple_slides set="MySQL_Setup" theme="bar" pause_time="3600000"]
或者观看此视频（如果您只想快速了解MySQL Workbench，请直接跳转到10:15）。

架构

在 MySQL SQL 方言中，我们的模式创建查询是：

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		DATETIME
); 

请注意，“Description”字段使用的是有限的“VARCHAR”类型而不是无限的“TEXT”类型，因为我注意到在使用 VBA 检索此类数据时存在一些问题。
如果您觉得 1000 个字符不够，可以随意增加最大字符数（您可以增加到 65000 多个！）。

PostgreSQL

安装

PostgreSQL 可以从EnterpriseDB 网站下载。

要快速设置，您可以按照此幻灯片演示。
[simple_slides set="PostgreSQL_Setup" theme="bar" pause_time="3600000"]
如果您想获得更完整的说明，并快速了解如何使用pgAdmin（从03:40开始），您可以观看这个很棒的视频教程。

架构

在 PostgreSQL SQL 方言中，我们的模式创建查询是：

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		TIMESTAMP
); 

与 MySQL 几乎相同，除了表示日期和时间的类型的名称：TIMESTAMP。
MySQL 也有一个名为 TIMESTAMP 的类型，但含义不同。

SQLite

SQLite Expert 安装

与Access一样，SQLite数据库不使用专用服务器来工作，而是包含在一个单文件中，通过标准 API访问。
因此，与MySQL和PostgreSQL不同，您只需要设置一个管理工具。

我知道两个非常好的工具：

• SQLite Manager,
• SQLite Expert

它们都功能强大且易于使用，但由于 SQLite Manager 作为 Firefox 附加组件提供，因此我们将优先选择SQLite Expert 的个人版本（当然，如果您在专业环境中使用它，应该为完整版本付费 " class="wp-smiley" />），因为它是一个独立的应用程序；但如果您正在使用 Firefox，请随时尝试 SQLite Manager，它将更容易设置。

[simple_slides set="SQLiteExpert_Setup" theme="bar" pause_time="3600000"]

这是一系列来自SQLite Expert 网站的视频教程，将向您展示如何：

• 创建新数据库。:

  http://www.sqliteexpert.com/tutorial_create_db.swf

• 使用设计器创建新表。

  http://www.sqliteexpert.com/tutorial_create_table.swf

• 使用 SQL 创建新表（您可以执行上面提供的 SQL 脚本）。

  http://www.sqliteexpert.com/tutorial_create_more_tables.swf

SQLite ODBC 驱动程序安装

与MySQL和PostgreSQL一样，要使用您的SQLite数据库，VBA需要一个ODBC 驱动程序。然而，MySQL 和 PostgreSQL 的相应驱动程序会与服务器一起透明安装，而 SQLite 驱动程序仅作为单独的包提供。

您可以从SQLite ODBC 驱动程序网站下载此驱动程序。

然后按照指南进行安装：

[simple_slides set="SQLiteODBCDriver_Setup" theme="bar" pause_time="3600000"]

要检查安装，请启动“ODBC 数据源管理员”。
转到“驱动程序”选项卡，查找 SQLite 驱动程序条目。

架构

在 SQLite SQL 方言中，我们的模式创建查询是：

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		TIMESTAMP
);

这与 MySQL 的 SQL 代码完全相同。

应用程序

因此，从这里开始，我假设您已正确设置了一个或多个 DBMS，并且您能够使用其专用管理应用程序（分别为MySQL Workbench、pgAdmin和SQLite Expert）与它们进行交互。
现在来到有趣的部分，我们将使用这些 DBMS 来存储和检索我们的数据，这些数据来自一个Excel/VBA应用程序。

用户界面

该应用程序是一个基本的任务管理系统，只有一个工作表，允许用户查看当前的任务列表，并在最终保存到数据库之前进行更新。
这是应用程序的屏幕截图，显示了我当前的待办事项列表。

有四个输入（分别是两个基本单元格和两个ActiveX TextBox，其属性 PasswordChar 设置为“*”）

• 源：DBMS 的类型是什么；这将决定我们需要提供什么信息以及以什么格式提供连接字符串。
• 位置：数据库在哪里；对于MySQL和PostgreSQL这些基于服务器的数据库，它是其主机地址（如果是在运行 Excel 应用程序的同一台计算机上，则使用“localhost”）；对于Access和SQLite这些基于文件的数据库，它是数据库文件的路径（示例能够解释相对于当前工作簿位置的路径）。
• 用户名，以及
• 用户密码：如果您设置了基于用户的安全，您将需要提供它们来与数据库进行交互；请注意，示例仅考虑基于服务器的 DBMS（MySQL和PostgreSQL）。

以下是每种数据库的配置示例。

访问	MySQL	PostgreSQL	SQLite

对于MySQL和PostgreSQL，登录/密码是您分别使用MySQL Workbench和pgAdmin连接时使用的。

从 VBA 使用数据库

好消息是，无论您选择哪种DBMS，您都将使用一个通用的过程来与之交互。
首先，您需要知道将使用哪个驱动程序来确保您的 VBA 代码和 DBMS 之间的通信。驱动程序的目的是为特定的 DBMS 实现一个标准的 API；这样，无论底层 DBMS 是什么，使用它的代码都可以以统一的方式与之通信，这其中一个优点是允许您根据需求的变化在不同的 DBMS 之间切换。

对于MySQL、PostgreSQL和SQLite，我使用了ODBC 驱动程序，但对于Access则没有，因为它的 ODBC 驱动程序更有限，并且除其他外，不支持事务，而这是我希望在应用程序中使用的一个功能，以便进行更干净的更新。

在使用不同的 DBMS 时，唯一不同的地方是连接字符串，但一旦建立连接，所有其他操作（数据检索和更新）都以通用方式处理，这避免了为每个 DBMS 编写代码。

在 VBA 代码中，为给定源构建正确的连接字符串和连接由“OpenConnection”方法管理。

Private Function OpenConnection() As ADODB.connection
    ‘ Read type and location of the database, user login and password
    Dim source As String, location As String, user As String, password As String
    source = Range("Source").Value
    location = Range("Location").Value
    user = TasksSheet.UserInput.Value
    password = TasksSheet.PasswordInput.Value
    ‘ Handle relative path for the location of Access and SQLite database files
    If (source = "Access" Or source = "SQLite") And Not location Like "?:\*" Then
        location = ActiveWorkbook.Path & "\" & location
    End If
    ‘ Build the connection string depending on the source
    Dim connectionString As String
    Select Case source
        Case "Access"
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & location
        Case "MySQL"
            connectionString = "Driver={MySQL ODBC 5.2a Driver};_
            Server=" & location & ";Database=test;UID=" & user & ";PWD=" & password
        Case "PostgreSQL"
            connectionString = "Driver={PostgreSQL ANSI};_
            Server=" & location & ";Database=test;UID=" & user & ";PWD=" & password
        Case "SQLite"
            connectionString = "Driver={SQLite3 ODBC Driver};Database=" & location
    End Select
    ‘ Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    Call OpenConnection.Open(connectionString)
End Function 

这是代码中唯一一个与底层 DBMS不通用的部分。
您可以在任何可能涉及多个数据源的项目中使用它，以使其余代码与构建连接字符串和建立连接的麻烦解耦。

剩余的源代码

源代码的其余部分，在模块“mTasks”中，包含按钮单击事件的句柄。

Public Sub LoadTasksButton_Click()
    Dim output As Range
    Set output = Range(TopLeft).Offset(1).Resize(1000, 3)
    output.ClearContents
    Dim connection As connection
    Set connection = OpenConnection()
    Dim result As ADODB.Recordset
    ‘ Load all the tasks from the database
    Set result = connection.Execute("SELECT description,category,dueDate FROM tasks")
    ‘ Insert them into the dedicated area
    Call output.CopyFromRecordset(result)
    connection.Close
End Sub
Public Sub UpdateTasksButton_Click()
    Dim connection As connection
    Set connection = OpenConnection()
    ‘ Create a record-set that holds all the tasks
    Dim records As ADODB.Recordset
    Set records = New ADODB.Recordset
    Call records.Open("SELECT description,_
    category,dueDate FROM tasks", connection, , adLockOptimistic)
    ‘ Begin a transaction to avoid corrupting the database in case of error
    connection.BeginTrans
    ‘ Clean up the tasks list
    While Not records.EOF
        records.Delete
        records.MoveNext
    Wend
    ‘ If there is at least one task
    If Not IsEmpty(Range(TopLeft).Offset(1)) Then
        Dim dataRange As Range
        Set dataRange = Range(TopLeft).Offset(1).Resize(, 3)
        ‘ If there is more than one task
        If Not IsEmpty(Range(TopLeft).Offset(2)) Then
            Set dataRange = Range(Range(TopLeft).Offset(1), _
            Range(TopLeft).Offset(1).End(xlDown)).Resize(, 3)
        End If
        Dim data As Variant
        data = dataRange.Value2
        Dim i As Integer
        For i = 1 To UBound(data)
            Dim row As Variant
            ‘ Extract ith row
            row = Application.WorksheetFunction.Index(data, i, 0)
            ‘ Add a new task
            records.AddNew
            records("Description") = row(1)
            records("Category") = row(2)
            records("DueDate") = CDate(row(3))
        Next i
    End If 
    records.Save
    records.Close
    ‘ Commit all the work: cleanup + new tasks
    connection.CommitTrans
    connection.Close
End Sub 

正如您所见，没有什么特定于某个 DBMS，只是通用的数据管道代码。
您不需要（并且不想要 ）知道您正在使用哪个 DBMS，您只需要知道它符合SQL之类的标准即可与之交互。

结论

正如您所见，使用Access以外的DBMS作为存储层是一个直接的过程，如果正确实现，前期成本不高，并且可以极大地增强您的应用程序，进而增强您的业务，如果您充分利用它们来运行它。

以后，如果您的数据库确实成为您业务的关键资产，包含大量重要数据，每分钟有数百个请求，需要高可用性，并且任何短缺都可能代表真正的运营风险，您可能希望（并且确实应该）让专门的数据库管理员（DBA）来管理、优化它，并处理所有棘手的事情。

根据您的 IT 组织，您可能会在灵活性方面损失很多，例如，当您需要更改表结构或创建新表时，但这通常是提高可靠性和性能所需付出的代价。

如果您发现任何拼写错误或错误，遇到任何问题或有其他疑问，请随时发表评论，我将尽最大努力及时回答。

感谢您的阅读！