作者：虚线老母阳 | 来源：互联网 | 2023-02-10 14:38
我一直在向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)
...
有没有人知道如何使用SQLiteForExcel更改此代码,以便我可以访问SQLite数据库.
提前谢谢了
1> Parfait..：
MS Access的默认引擎,Jet/ACE和SQLite共享相同的质量,因为它们是文件级数据库,其中数据库文件位于磁盘级别的目录中,而不是服务器级数据库(SQL Server,Oracle,MySQL,Postgres).
要在后端数据库之间进行流畅交换,请考虑使用ADO在Excel中连接数据库.现在您使用DAO,这是MS Access的默认连接层.
您需要的第一件事是下载一个SQLite ODBC驱动程序,一个匹配您的版本(最有可能是SQLite 3)和Windows位级别(32/64位).相比之下,您的计算机很可能已经安装了MS Access ODBC驱动程序.完成后,只需设置连接字符串:
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
作为比较,使用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
京公网安备 11010802041100号