VBA和SQLite数据库交互

fxl447098457

SQLite是一个轻量级的数据库，对于单机的用户还是不错的。要在excel VBA里使用SQLite，首先肯定是需要相关的驱动的。熟悉ADO的人应该知道。我也不多说了。首先安装驱动：http://www.ch-werner.de/sqliteodbc/下载。在里面的Current Version(也就是最新的稳定版).注意驱动跟软件版本走。32位的office就下载sqliteodbc.exe,64位的office就下载sqliteodbc_w64.exe。
由于没有gui.我们无法查看数据文件。这样我们还需要一个图形化的界面来看一下数据结果，这里我推荐一下SQLiteSpy.下载地址：https://www.yunqa.de/delphi/products/sqlitespy/index。点击网页右边的Download Win32或者win64版本下载吧。

SQLiteSpy的界面如下：

可以新建SQLite数据库，也可以打开已有的数据库查看数据文件。这对我们使用vba的人就够了。主要是查看vba是否得到想要的数据结果。下面看一下相关代码：

Sub 创建数据库()
Dim conn As New ADODB.Connection '引用ADO
Dim Connstr As String
Connstr = "Driver={SQLite3 ODBC Driver};Database=" & ThisWorkbook.Path & "\用户数据.db"
conn.Open Connstr
conn.Close
End Sub
上面的代码会打开用”户数据”这个数据库文件，如果不存在会在这个路径上创建一个同名文件。

下面的代码用于在数据库文件里创建一个表。
Sub 创建表()
Dim conn As New ADODB.Connection '引用ADO
Dim Connstr As String
Connstr = "Driver={SQLite3 ODBC Driver};Database=" & ThisWorkbook.Path & "\用户数据.db"
conn.Open Connstr
conn.Execute "Create table 用户清单(用户编号,用户姓名,用户地址,联系电话)"  '在用户数据库文件下创建用户清单表，并创建4个字段名
conn.Close
End Sub

用SQLiteSpy查看效果如下图：


下面的代码是插入excel数据到清单数据表。
Sub 更新数据到DB()
Dim conn As New ADODB.Connection '引用ADO
Dim Connstr As String
Connstr = "Driver={SQLite3 ODBC Driver};Database=" & ThisWorkbook.Path & "\用户数据.db"
conn.Open Connstr
conn.Execute "Create table 用户清单(ID,NAME,AGE,ADDRESS,SALARY)"  '在用户数据库文件下创建用户清单表，并创建5个字段名,如果表已存在会报错
arr = [A1].CurrentRegion
For i = 2 To UBound(arr)
conn.Execute "Insert into 用户清单 values('" & Join(Application.Rept(Application.Index(arr, i, 0), 1), "','") & "')"
Next
conn.Close
End Sub

运行结果如下图：

fxl447098457

查询数据基本就是select。和excel里面的SQL查询也是大体类似的。当然毕竟不同的软件，肯定语法子句有些差别。如下：
Sub 查询数据()
Dim conn As New ADODB.Connection '引用ADO
Dim Connstr As String
Connstr = "DSN=SQLite3 Datasource;Database=" & ThisWorkbook.Path & "\用户数据.db"
conn.Open Connstr
Set rst = conn.Execute("Select * from 用户清单 limit 2")
[A1].CopyFromRecordset rst
conn.Close
End Sub
返回结果如图：

上面sql语句中limit用于限制由 SELECT 语句返回的数据数量。说明了其中的一些差异。不过我在这里主要是起到一个引导的作用。相关的学习可以看看这个：http://www.runoob.com/sqlite/sqlite-limit-clause.html。SQLite教程做的挺不错的。

fxl447098457

昨天使用的是ODBC驱动查询。代码里介绍了2种连接的字符串。1.Connstr = "Driver={SQLite3 ODBC Driver};Database=" & ThisWorkbook.Path & "\用户数据.db"
2.Connstr = "DSN=SQLite3 Datasource;Database=" & ThisWorkbook.Path & "\用户数据.db"
今天介绍一下OLEDB驱动方面的查询。附上驱动的附件： sqlite-oledb-3515.rar (790.46 KB, 下载次数: 764)

2017-11-26 14:45 上传

点击文件名下载附件

。对应自己的office版本使用win32/X64.例如我用的是win7的64位操作系统，但我用的是32位的office，所以我就用win32的dll文件。注册方法，32位操作系统复制文件到system32下,64位就复制到SysWow64文件夹下。然后以管理员权限运行命令提示符。然后命令提示符窗口输入:"cd .."返回到父目录windows文件夹，再根据自己系统进入 system32/sysWow64文件夹下，我是64位的操作系统，就进入sysWow64下，然后输入" regsvr32 sqlite-oledb.dll ",就可以注册成功了，（注意win7以上的新系统的命令提示符一定要已管理员权限来运行，否则很容易因为权限问题注册失败）如下图：

OLEDB的连接字符串如下：
Connstr = "Provider=SQLITEDB;Data Source=" & ThisWorkbook.Path & "\用户数据.db"
vba里面知道了连接的字符串基本就知道怎么用了。其他更多的是SQL语句方面的研究和变化。
下面就演示一下简单的记录查询

好了有关VBA使用SQLite方面就先写到这里了。

ivccav

电脑上装了office就有access了，还装一个多余的SQLite？这两者十分相似，且网上测评认为access性能优于SQLite

ivccav

也不是不了解，学习Python的时候就使用过SQLite了，在应用程序中作为嵌入式轻型数据库是可以用用，SQLite类似于Access，也是将所有数据库的定义（包括定义、表、索引和数据本身）都保存在一个单一的文件中。SQLite没有自带操作界面，使用便利性肯定无法跟Access比，也没有看到SQLite性能优于Access的权威测评，但是SQLite免费，也比Access更轻一点，这个是最大的优点吧？一般认为，在微软平台下，access和sqlite性能基本相当，而在linux平台下，sqlite的并发性能要高一些。本人不搞IT行业，的确没有用SQLite搞过东西，但是对MySQL还是很熟悉的，熟练掌握了MySQL或者MSSQL，SQLite根本无需去学。

fxl447098457

今天补充一下excel下Power query如何查询SQLite数据表的数据。在建立在已经安装过了ODBC驱动的基础下,我使用Odbc.DataSource函数连接查询。具体函数式如图：

fxl447098457

Power query下ODBC查询如果还要使用SQL语句处理筛选一下数据，可以用Odbc.Query语句。表达式如下图：

fxl447098457

下面说明在Power Query中使用Oledb的方式来查询数据。需OLEDB驱动。第一种，是用OleDb.DataSource函数查询。查询语句见下图：

fxl447098457

Power Query中使用Oledb的方式来查询数据第二种方法，使用OleDb.Query函数查询。方法如下图：

hyh8887

楼主，能帮我看看我的文件吗