快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

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

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

重要说明:原始博客文章到CodeProject格式的转换尚未完成。

您可以在这里找到原始文章。

我将尽快更新CodeProject文章。

介绍

给学究的人注意:是Access不是数据库引擎,只是Jet / ACE的图形前端,但我们会坚持这种简化。<img src =

zip归档文件中提供了示例应用程序(Excel + VBA)和SQL源代码

如果您是高级VBA开发人员,并且构建的应用程序可以管理大量数据,那么使用Access数据库存储它们的可能性就很大。

如果此设置完全适合您的当前需求,您可以完全掌握它,那么您不会遇到任何问题,并且您的需求在不久的将来也不会改变,您可以跳过本文,继续享受您的系统。 ;)

确实,您真的需要一个新的数据库管理系统DBMS)吗?
通常,支持迁移到其他DBMS的唯一论据是它们“更好”。尽管某些技术功能确实如此,但它可能与其他“度量标准”无关,例如简单性:对于非IT员工而言,访问易于理解和管理,并且通常与默认的商务工作站安装程序以及Office套件的其余部分一起安装。

因此,假设您有充分的理由迁移到新的DBMS,因为您已经感觉到至少需要以下功能之一:互操作性大存储量高并发性(数百个用户)和/或高性能,然后Access开始成为问题的一部分。
因此,如果您想提高数据库的质量而又不使总拥有成本TCO)爆炸,该怎么办?

您的TCO基本上由以下材料组成:

  • 许可费用:限制费用非常简单:使用免费的(通常是开源的)数据库,并仅支付支持费用
  • 管理成本:它们远比许可成本大,并直接受到DBMS复杂性的影响;因此,您需要一个简单的DBMS,无需系统或数据库管理员的帮助,就可以像过去使用Access一样进行设置和管理
  • 开发成本:为适应新的DBMS而对当前架构或VBA实现进行的所有其他更改都将产生成本;因此我们希望在零额外开发的情况下保持透明,这尤其意味着标准的基于SQL的DBMS。

尽管该方程似乎有些复杂,但它至少具有三个解决方案:

  • 如果您对“单文件 ”模型感到满意,并发约束不并且仅需要互操作性(与 Mac OS Linux Unix等),更大的存储空间和/或 SQLite, SQLite是理想的选择。节省成本
  • MySQL PostgreSQL:如果您需要支持高并发,非常大的存储(例如数十GB),高级用户管理性能微调和其他高级功能,则必须跳出单个文件的世界。
    如果您没有特定的要求,那么 MySQL PostgreSQL看起来将与您相似,并且同样可以完成工作。但是,在这种情况下,我偏爱 MySQL,不是因为它的固有功能会更好(正如我已经说过的,MySQL和PostgreSQL在简单设置上大致等效),而是因为作为多年的参考开源DBMS,MySQL受益于庞大的社区和工具箱。此外,虽然您肯定会找到可以在PostgreSQL上良好运行的工具,但是如果您需要根据层次结构证明选择的正确性,那么如果选择标准解决方案而不是挑战者,则将处于更好的位置。
    但是由于我不是宗派主义者,为了完整起见,我将同时介绍这两个方面。

在本文中,我将快速介绍这三个DBMS的设置(带有指向其他资源的链接以获取更广泛的说明),并说明它们在小型VBA应用程序(一种革命性的待办事项列表管理器)中的用法,该应用程序也使用Access。

数据架构

我们的应用程序使用的数据模式实际上是基本的:一个表包含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”]
和/或观看此视频(如果只想快速浏览,请直接转到10:15介绍MySQL工作台):

架构图

在MySQL SQL方言中,我们的架构创建查询为:

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

请注意,“ Description”字段使用的是受限“ VARCHAR”类型,而不是无限制的“ TEXT”类型,因为在使用VBA检索此类数据时,我注意到了一些问题。
如果您觉得1000个字符太少(可以增加到65000个以上!),请随意增加最大字符数。

PostgreSQL的

安装

可以从EnterpriseDB网站下载PostgreSQL

要快速设置它,您可以按照以下幻灯片显示:
[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

作为AccessSQLite数据库不使用专用服务器来工作,而是包含在通过标准API访问的单个文件中。 因此,与MySQLPostgreSQL相反,您只需要设置一个管理工具

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

  • SQLite的经理
  • SQLite专家

它们既强大又易于使用,但是由于SQLite Manager是作为Firefox附加组件提供的,因此我们希望使用SQLite Expert个人版本(当然,如果您在专业环境中使用它,则需要付费)在完整版 <img src = “类=” WP-笑脸” />),因为它是自包含的,但随时尝试SQLite的经理,如果你使用Firefox,它会更容易安装。

[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驱动程序

至于MySQLPostgreSQL,要使用您的SQLite数据库,VBA需要一个ODBC驱动程序,而对于MySQL和PostgreSQL,它们各自的驱动程序与服务器一起透明安装,SQLite驱动程序仅作为单独的软件包提供。

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

然后按照指南进行安装:

[simple_slides set =“ SQLiteODBCDriver_Setup” theme =“ bar” pause_time =“ 3600000”]

要检查安装,请启动“ ODBC数据源管理器 ”。
转到“ Drivers ”选项卡,然后查找SQLite驱动程序条目:

SQLiteODBC_check_administrator

架构图

在SQLite SQL方言中,我们的架构创建查询为:

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

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

应用程序

因此,从这里开始,我假设您已经正确设置了一个或多个DBMS,并且能够使用其专用的管理应用程序(分别为MySQL WorkbenchpgAdminSQLite Expert)与它们进行交互。
这是一个有趣的部分,我们将使用这些DBMS从Excel / VBA应用程序存储和检索数据。

用户界面

该应用程序是具有唯一工作表的基本任务管理系统,允许用户查看其当前任务列表并对其进行更新,然后再将其最终保存到数据库中。
这是应用程序的屏幕快照,其中包含我当前的待办事项列表:

任务管理应用程序屏幕截图

任务管理应用程序屏幕截图

有四个输入(分别将两个基本单元和两个ActiveX TextBox的属性PasswordChar设置为“ *”):

  • 资料来源:使用的DBMS是什么类型?这将确定我们需要提供的信息以及连接字符串的格式
  • 位置:在哪里找到数据库;对于MySQL的PostgreSQL的基于服务器的,这是他们的主机(如果是同一台机器,你正在使用运行Excel应用程序使用的“地址localhost“),用于访问SQLite的基于文件的,它是数据库文件路径(该示例能够解释相对于当前工作簿位置的路径
  • 用户名和
  • 用户密码:如果您已经设置了一些基于用户的安全性,则需要提供它们以与数据库进行交互;请注意,该示例仅将它们用于基于服务器的DBMS(MySQLPostgreSQL

以下是每个数据库的配置示例:

访问 的MySQL PostgreSQL的 SQLite的
图片7 图片8 图片9 图片10

对于MySQLPostgreSQL来说,登录名/密码分别是用于与MySQL WorkbenchpgAdmin连接的密码。

使用VBA中的数据库

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

对于MySQLPostgreSQLSQLite,我使用了ODBC驱动程序,但没有使用Access,因为它的ODBC驱动程序受到更多限制,并且除其他外,不支持事务,这是我希望在应用程序中使用的功能,以使其更清洁。更新。

使用不同的DBMS时只有一件事会有所不同:connection-string,但是一旦建立连接,所有其他操作(检索和数据更新)将以一种通用的方式进行处理,从而避免了必须编写一个代码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”中,包含按钮上的click事件的处理程序:

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.CloseCommit all the work: cleanup + new tasks
    connection.CommitTrans
    connection.Close
End Sub

如您所见,给定的DBMS并没有特定的东西,只有通用的数据管道代码。
您不需要(也不希望<img src =)知道您使用的是哪个DBMS,只需要知道它符合某些标准(例如SQL)即可与之交互。

结论

如您所见,为存储层使用与Access不同的DBMS是一个简单的过程,如果正确实施,将不会花费您很多前期费用,并且如果您充分利用它们,则可以极大地增强您的应用程序以及您的业务运行它。

后来,如果您的数据库确实成为您的业​​务的关键资产,则拥有大量重要数据,每分钟有数百个请求,高可用性要求,而任何短缺都可能代表真正的运营风险,您可能想要(并且确实应该),让专门的数据库管理员DBA)对其进行管理,优化和处理所有棘手的问题。

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

如果您发现任何错别字或错误,遇到任何问题或有其他问题随时发表评论,我会尽力及时答复。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638