VBA 连接sqlite3

Step 1  官网下载sqlite3.dll与宏文件放到同一个目录。

Step 2 新建一个xlsm的宏文件，通过API的方式调用dll 文件。

' 需要先声明API函数 
Private Declare Function sqlite3_open Lib "sqlite3.dll" (ByVal filename As String, ByRef ppDb As LongPtr) As Long
Private Declare Function sqlite3_exec Lib "sqlite3.dll" (ByVal pDb As LongPtr, ByVal sql As String, ByVal callback As LongPtr, ByVal callback_arg As LongPtr, ByRef errMsg As LongPtr) As Long
Private Declare Function sqlite3_close Lib "sqlite3.dll" (ByVal pDb As LongPtr) As Long
Private Declare Function sqlite3_errmsg Lib "sqlite3.dll" (ByVal pDb As LongPtr) As LongPtr
Private Declare Function lstrcpyA Lib "kernel32" (ByVal lpString1 As String, ByVal lpString2 As LongPtr) As Long

Sub TestSQLite()
    Dim db As LongPtr
    Dim rc As Long
    Dim errMsg As LongPtr
    Dim result As String * 255
    

    On Error Resume Next
    
    ' 打开数据库 (会在当前目录创建test.db)
    rc = sqlite3_open("test.db", db)
    
    If rc <> 0 Then
        lstrcpyA result, sqlite3_errmsg(db)
        MsgBox "无法打开数据库: " & result
        Exit Sub
    End If
    
    ' 执行SQL语句
    rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, name TEXT);", 0, 0, errMsg)
    
    If rc <> 0 Then
        lstrcpyA result, errMsg
        MsgBox "SQL错误: " & result
    Else
        MsgBox "表创建成功!"
    End If
    
    ' 关闭数据库
    sqlite3_close db
End Sub