Option Explicit
Public Function GetConnStr() As String
Dim ConnString As String
'連接Access
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\數據庫名;Persist Security Info=False"
GetConnStr = ConnString
End Function
Public Function OpenConn(ByRef Conn As ADODB.Connection) As Boolean
'打開數據庫連接，連接成功返回true，出錯時返回false
Set Conn = New ADODB.Connection
'出錯處理
On Error GoTo Error_box
Conn.Open GetConnStr
OpenConn = True
Exit Function
Error_box:
MsgBox "連接數據庫失敗！請重新連接！"
OpenConn = False
Exit Function
End Function
Public Sub ExecuteSQL(ByVal SQL As String, ByRef msg As String)
'執行SQL語句
Dim Conn As ADODB.Connection
'出錯處理
On Error GoTo Error_box
'打開數據庫連接
If OpenConn(Conn) Then
Conn.Execute SQL
msg = "操作執行成功！"
End If
Exit Sub
Error_box:
msg = "執行錯誤: " & Err.Description
Set Conn = Nothing
Exit Sub
End Sub
'*********************************************************************************************************************
'添加數據
Call ExecuteSQL("INSERT INTO 表名稱(字段1,字段2,字段N) VALUES ('值1','值2','值N')", msg)
'註意:以上字段值如果為表達式並且是文本類型應采用如此格式:'" & 表達式 &"',如果是數值應為" & 表達式 &"
'刪除數據
Call ExecuteSQL("DELETE FROM 表名稱", msg)
'修改數據
Call ExecuteSQL("Update 表名稱 SET 要修改的字段名='" & Trim(Text1.Text) & "' WHERE 條件='" & 表達式 & "'", msg)
'如:
Call ExecuteSQL("Update G借債 SET 姓名='" & Trim(Text1(0).Text) & "' WHERE 姓名='" & Lbl_str.Caption & "'", msg)