vb6或Access或VBA如何对sqlite数据库操作

vb6或Access或VBA如何对sqlite数据库操作

利用sqlite3的dll文件，你用vb6或Access可简单调用LoadLibrary函数就可以直接操作sqlite数据库

转自：红色代码网 小松

sqlite3.dll 可百度搜索一下，很多下载的路径

以下是如何使用Vb6或Access或VBA 操作数据库的类模块代码

在类模块添加一个新的类模块命名为ClsSqlite

将以下所代码粘贴到类中：

Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long

Dim oDB As Object '定义数据库对象变量

Public dbPath As String '存储Sqlite 数据库完整路径

Private Sub Class_Initialize()  '类初始化

    Dim lb As Long

    lb = LoadLibrary(App.Path & "\sqlite3.dll")

    Set oDB = CreateObject("LiteX.LiteConnection")

End Sub

Private Sub openDB() '打开数据库

    oDB.Open (dbPath)

End Sub

'插入数据 '插入数据库的操作，返回受影响的行数

'tableName为表名,arr为插入字段名和值的二维数组

Public Function Insert(ByVal tableName As String, ByRef arr() As String) As String 

    Dim str_Fields As String, str_Values As String, strSql As String

    strSql = "insert into " & tableName

    For I = 0 To UBound(arr)

        str_Fields = str_Fields & "'" & arr(I, 0) & "',"

        str_Values = str_Values & "'" & Trim(Replace(arr(I, 1), "'", "''")) & "',"

    Next I

    str_Fields = Mid(str_Fields, 1, Len(str_Fields) - 1)

    str_Values = Mid(str_Values, 1, Len(str_Values) - 1)

    strSql = strSql & "(" & str_Fields & ") values (" & str_Values & ")"

    openDB (dbPath)

    oDB.Execute (strSql)

    Insert = oDB.changes

    oDB.Close

End Function

'查询数据库

Public Function Query(ByVal tableName As String, Optional ByVal Fields As String = "", Optional Condition As String = "", Optional ByVal Exp As String = "") As String() '查询数据。返回结果数组，tableName 表名，fields是你要查询哪些字段,condition是查询条件也就是where，exp 为其它条件。例如order by ，group by，limit，offset等

    Dim arr() As String, sqlStr As String, K As Integer

    Dim odbRS As Object

    Set odbRS = CreateObject("LiteX.LiteStatement")

    Fields = IIf(Fields = "", "*", Fields)

    odbRS.ActiveConnection = oDB

    sqlStr = "select " & Fields & " from " & tableName & IIf(Condition = "", "", " where " & Condition & " ") & IIf(Exp = "", "", " " & Exp & " ")

    Debug.Print sqlStr

    openDB (dbPath)

    d = odbRS.prepare(sqlStr)

    If odbRS.Rows.Count = 0 Then

        Query = arr

    Else

        ReDim arr(odbRS.Rows.Count - 1, odbRS.Columns.Count - 1)

        For Each Row In odbRS.Rows

            For I = 0 To odbRS.Columns.Count - 1

                arr(K, I) = Row(I)

            Next I

            K = K + 1

        Next

    End If

    Query = arr

    odbRS.Close

    oDB.Close

End Function

'修改数据库

Public Function Modify(ByVal tableName As String, ByRef arr() As String, Optional Condition As String = "") As Integer'实现数据修改功能。tableName表名，arr和insert方法一样的。Condition 为条件，返回受影响的行数

    Dim str_ As String, strSql As String

    strSql = "update " & tableName & " set "

    For I = 0 To UBound(arr)

        str_ = str_ & "`" & Trim(arr(I, 0)) & "`='" & Trim(Replace(arr(I, 1), "'", "''")) & "',"

    Next I

    strSql = strSql & Mid(str_, 1, Len(str_) - 1) & IIf(Condition = "", "", " where " & Condition & " ")

    Debug.Print strSql

    openDB (dbPath)

    oDB.Execute (strSql)

    Modify = oDB.changes

    oDB.Close

End Function

'删除数据库

Public Function Delete(ByVal tableName As String, Optional ByVal Condition As String = "") As Integer'删除数据的功能。tableName为表名，condition为条件，返回受影响的行数。

    Dim str_ As String

    strSql = "delete from " & tableName & IIf(Condition = "", "", " where " & Condition)

    Debug.Print strSql

    openDB (dbPath)

    oDB.Execute (strSql)

    Delete = oDB.changes

    oDB.Close

End Function

以上类模块的 使用的方法与示例代码：

1、sqlite 查询

    Dim sqlite As New ClsSqlite

    sqlite.dbPath = 这里搞上你的数据库的路径

    arr = sqlite.Query("这里是表名", "", "`username`='小松'")

'将返回查询结果数组

2、sqlite 添加记录

Private Function makeArr() As String()'本函数为制作一个要插入数据的数组。

    Dim arr() As String

    ReDim arr(2, 1)

    arr(0, 0) = "name" '这个是字段名

    arr(0, 1) = txtName.Text  '这个是字段值，下同

    arr(1, 0) = "oldname"

    arr(1, 1) = txtOldname.Text

    arr(2, 0) = "sex"

    arr(2, 1) = cmbSex.Text

    makeArr = arr

End Function

Private Sub cmdAdd_Click()

Dim pSqlite As New ClsSqlite

arr = makeArr  '这里调用makeArr得到要插入的数据的数组

pSqlite.Insert("populations", arr) '执行插入

End Sub

3、sqlite 修改

修改和添加记录基本上差不多。也需要makeArr，唯一不同的是一个调用类里面的insert方法，一个调用类里面的modify方法，还有就是既然你要修改数据，需要指定条件，要修改哪个ID的数据。

pSqlite.Modify("populations", arr, "`uid`='" & Uid & "'") 'uid就是指定要修改uid为UID的这一条数据。

4、sqlite 删除

删除最简单。提供一个表名，和一个条件即可。

以上就是完整的教程，希望对大家有用。参考了网上相关的教程和资料