database.bas

一个动态调度的软件

Attribute VB_Name = "database"
Public odbcstr As String
Public conn As New ADODB.Connection

'连接数据库
Public Sub connect_db()
On Error GoTo errorhandle
   If conn.State = 0 Then '如果没有连过数据库，就允许连接数据库，避免重复连接数据库
     odbcstr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=' scheduler';Data Source=XU-DESHENG"
     conn.Open odbcstr '连接到数据库
   End If
   
   Exit Sub
errorhandle:
    MsgBox "连接数据库错误:" + Err.Description
   
End Sub

'关闭数据库连接
Public Sub close_db()
On Error GoTo errorhandle
   If conn.State = 1 Then   '数据库连接处于可用状态 ，才能关闭
      conn.Close
   End If
   
   Exit Sub
errorhandle:
   MsgBox "关闭数据库连接错误:" + Err.Description
   
End Sub

'执行sql语句，需要返回记录集
Public Sub run_sql_needRS(ByVal sql As String, ByRef rs As ADODB.Recordset)
On Error GoTo errorhandle
   
    If conn.State = 0 Then
       MsgBox "数据库连接不可用"
       Exit Sub
    End If
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sql, conn, adOpenKeyset, adLockOptimistic, adCmdText
 
    Exit Sub
    
errorhandle:
    MsgBox "执行sql语句错误:" + Err.Description

End Sub

'执行sql语句，不需要返回记录集
Public Sub run_sql_noRS(ByVal sql As String)
On Error GoTo errorhandle
    If conn.State = 0 Then
       MsgBox "数据库连接不可用"
       Exit Sub
    End If
    
    conn.Execute sql, 32
    
    Exit Sub
errorhandle:
    MsgBox "执行sql语句错误:" + Err.Description
End Sub