在 Excel 中的 VBA 中访问 SQLite 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42509154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Accessing a SQLite Database in VBA in Excel
提问by Ollie
I have been adding an MS Access database to VBA in order to conduct some analysis of ships. However the database has now changed to SQlite, which I have no idea how to access from VBA. I have tried the use GitHub's SQLiteForExcel, but I don't understand how it works, even with the examples. The code I have for accessing the Access database is below: (the db_path is the hyperlink to my Access database)
我一直在向 VBA 添加一个 MS Access 数据库，以便对船舶进行一些分析。但是数据库现在已更改为 SQlite，我不知道如何从 VBA 访问。我试过使用 GitHub 的 SQLiteForExcel，但我不明白它是如何工作的，即使是例子。我访问 Access 数据库的代码如下：（db_path 是我的 Access 数据库的超链接）
Sub query_db()
On Error GoTo Errorhandler
Dim v_db As DAO.Database
Dim rst As DAO.Recordset
Dim vessels_db As Variant
Dim strSQL As String
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("results")
ws.Select
vessels_db = [db_path]
Set v_db = OpenDatabase(vessels_db)
Worksheets("results").Select
[x_0].Select
Range(Selection, Selection.Offset(40000, 1)).ClearContents
strSQL = " SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
" GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "
Set rst = v_db.OpenRecordset(strSQL)
...
Does anyone have any idea how I can change this code using SQLiteForExcel so I can access the SQLite database.
有谁知道我如何使用 SQLiteForExcel 更改此代码，以便我可以访问 SQLite 数据库。
Many thanks in advance
提前谢谢了
回答by Parfait
MS Access' default engine, Jet/ACE, and SQLite share the same quality in that they are file-level databases where database files reside at disk level in directories as opposed to server level databases (SQL Server, Oracle, MySQL, Postgres).
MS Access 的默认引擎、Jet/ACE 和 SQLite 具有相同的质量，因为它们是文件级数据库，其中数据库文件位于目录中的磁盘级，而不是服务器级数据库（SQL Server、Oracle、MySQL、Postgres）。
To fluidly interchange between backend databases, consider connecting databases in Excel using ADO. Right now you use DAO which is the default connection layer for MS Access.
要在后端数据库之间流畅地交换，请考虑使用 ADO 在 Excel 中连接数据库。现在您使用 DAO，它是 MS Access 的默认连接层。
The first thing you require is to download an SQLite ODBC Driver, one that matches your version (SQLite 3 most likely) and your Windows bit level (32/64 bit). As comparison, your machine most likely already has installed an MS Access ODBC Driver. Once done, simply set up your connection string:
您需要做的第一件事是下载SQLite ODBC 驱动程序，它与您的版本（最有可能是 SQLite 3）和您的 Windows 位级别（32/64 位）相匹配。作为比较，您的机器很可能已经安装了 MS Access ODBC 驱动程序。完成后，只需设置连接字符串：
SQLite
SQLite
Dim conn As Object, rst As Object
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
' OPEN CONNECTION
conn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Path\To\SQLite\Database.db;"
strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
" GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "
' OPEN RECORDSET
rst.Open strSQL, conn
' OUTPUT TO WORKSHEET
Worksheets("results").Range("A1").CopyFromRecordset rst
rst.Close
' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing
MS Access
微软访问
As comparison, with ADO you can just simply switch connection strings referencing the ODBC Driver for different database backends. Notice like above, the database source is a directory path:
作为比较，使用 ADO，您可以简单地切换引用不同数据库后端的 ODBC 驱动程序的连接字符串。注意像上面一样，数据库源是一个目录路径：
Dim conn As Object, rst As Object
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
' OPEN CONNECTION
conn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Path\To\Access\DB.accdb;"
strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
" GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "
' OPEN RECORDSET
rst.Open strSQL, conn
' OUTPUT TO WORKSHEET
Worksheets("results").Range("A1").CopyFromRecordset rst
rst.Close
' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing
回答by pcamadini
Great solution, thanks Parfait!
很好的解决方案，谢谢冻糕！
Just one small quick correction, you actually need to make:
只需一个小的快速更正，您实际上需要进行：
rst.Open strSQL, conn, 1, 1
This way, the complete solution would be:
这样，完整的解决方案将是：
Dim conn As Object, rst As Object
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
' OPEN CONNECTION
conn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Path\To\SQLite\Database.db;"
strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ;"
' OPEN RECORDSET
rst.Open strSQL, conn, 1, 1
' OUTPUT TO WORKSHEET
Worksheets("results").Range("A1").CopyFromRecordset rst
rst.Close
' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing
This will make rst contain the entire table you got from the query.
这将使 rst 包含您从查询中获得的整个表。
