Access中国
电 话：0760-88315075
热 线：0760-88315075
邮 编：528463
咨询QQ：1918333016
营销QQ：4008553990
Email：1918333016@qq.com
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 删除
删除最简单。提供一个表名，和一个条件即可。
以上就是完整的教程，希望对大家有用。参考了网上相关的教程和资料
