vba的一个DB操作类

Option Explicit
'--------------------------------------------------------
'[Class Name]: DBHelper
'[Description]: databse access class
'-------------------------------------------------------- 'variable
Private mConn As ADODB.Connection
Private mIsOpen As Boolean
Private mIsBeginTran As Boolean
Private Const MAX_TIME_OUT As Integer = 'command timeout(unit:second) '--------------------------------------------------------
'[Function name]: OpenConnection
'[Description]: DB connection
'[Parameter]: (1)Server name (2)Database name (3)User name (4)Password
'--------------------------------------------------------
Public Sub OpenConnection(ByVal Server As String, ByVal Database As String, _
ByVal Username As String, ByVal Password As String) Dim connString As String
connString = OleDbConnectionString(Server, Database, Username, Password) Set mConn = New ADODB.Connection
mConn.CommandTimeout = MAX_TIME_OUT
mConn.Open connString
mIsOpen = True
End Sub '--------------------------------------------------------
'[Function name]: Dispose
'[Description]: Dispose
'--------------------------------------------------------
Public Sub Dispose() If mIsOpen Then
mConn.Close
End If
mIsOpen = False
Set mConn = Nothing
End Sub '--------------------------------------------------------
'[Function name]: ExecuteNoQuery
'[Description]: SQL execute
'[Parameter]: (1)SQL statement
'--------------------------------------------------------
Public Sub ExecuteNoQuery(ByVal strSQL As String) If mIsOpen Then
mConn.Execute (strSQL)
End If
End Sub '--------------------------------------------------------
'[Function name]: ExecuteRecordset
'[Description]: SQL excute
'[Parameter]: (1)SQL
'[Return Value]: ADODB.Recordset
'--------------------------------------------------------
Public Function ExecuteRecordset(ByVal strSQL As String) As ADODB.recordSet Dim rs As New ADODB.recordSet
If mIsOpen Then
rs.CursorLocation = adUseClient
rs.Open strSQL, mConn, adOpenForwardOnly, adLockReadOnly
End If Set ExecuteRecordset = rs
End Function '--------------------------------------------------------
'[Function name]: BenginTrans
'[Description]: begin trans
'--------------------------------------------------------
Public Sub BeginTrans() If mIsOpen Then
mConn.BeginTrans
mIsBeginTran = True
End If
End Sub '--------------------------------------------------------
'[Function name]: CommitTrans
'[Description]: commint trans
'--------------------------------------------------------
Public Sub CommitTrans() If mIsOpen And mIsBeginTran Then
mConn.CommitTrans
mIsBeginTran = False
End If
End Sub '--------------------------------------------------------
'[Function name]: RollbankTrans
'[Description]: rollback trans
'--------------------------------------------------------
Public Sub RollbackTrans() If mIsOpen And mIsBeginTran Then
mConn.RollbackTrans
mIsBeginTran = False
End If
End Sub '--------------------------------------------------------
'[Function name]: OleDbConnectionString
'[Description]: create DB conn string
'[Parameter]: (1)server (2)database (3)username (4)password
'[Return Value]: DB conn string
'--------------------------------------------------------
Private Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, _
ByVal Username As String, ByVal Password As String) As String If Username = "" Then
OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
& ";Initial Catalog=" & Database _
& ";Integrated Security=SSPI;Persist Security Info=False;"
Else
OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
& ";Initial Catalog=" & Database _
& ";User ID=" & Username & ";Password=" & Password & ";"
End If End Function '--------------------------------------------------------
'[Function name]: Class_Initialize
'[Description]: class initialize
'--------------------------------------------------------
Private Sub Class_Initialize() End Sub '--------------------------------------------------------
'[function name]: Class_Initialize
'[description]: terminate
'--------------------------------------------------------
Private Sub Class_Terminate() Call Dispose
End Sub
上一篇:一种消息和任务队列——beanstalkd


下一篇:NSPredicate过滤数组数据