VB6-操作資料庫

標籤：des   style   blog   color   使用   os   資料   io   

 

平常搞資料庫操作多了就想把經常用的內容放在一起，我也懶，在一本書裡的工程例子挑了一個bas，修修改改，湊合這用吧。

 

 1 Public strCnn As String  ‘資料庫連接字串 2 Public AdoCnn As ADODB.Connection ‘資料庫連接 3 Public IsConnect As Boolean  ‘判斷是否串連 4  5  6 Private Sub Connect() ‘串連資料庫 7     On Error GoTo Err: 8     If IsConnect = True Then  ‘如果串連標記為真，則返回。否則會出錯 9         Exit Sub10     End If11     12     Set AdoCnn = New ADODB.Connection ‘關鍵New用於建立新對象cnn13     With AdoCnn14         .ConnectionString = strCnn15         .ConnectionTimeout = 1016         .Open17     End With18     IsConnect = True  ‘設定串連標記，表示已經串連到資料庫19     Exit Sub20 Err:21     If Err = -2147467259 Then22         Set Cnn = Nothing23         MsgBox Err.Description & "請檢查資料庫配置!", vbOKOnly + vbInformation, "Connect"24     Else25         MsgBox Err.Description & "請檢查資料庫配置!", vbExclamation, "Connect"26     End If27     28 End Sub29 30 Public Sub Disconnect() ‘斷開與資料庫的串連31     Dim rc As Long32     If IsConnect = False Then Exit Sub ‘如果串連標記為假，標明已經中斷連線，則直接返回33     AdoCnn.Close  ‘關閉串連34     35     Set AdoCnn = Nothing36     IsConnect = False37 End Sub38 39 Public Sub DB_Connect() ‘使用Connect_Num控制資料庫連接40     Connect_Num = Connect_Num + 141     Connect42 End Sub43 44 Public Sub DB_Disconnect()45     If Connect_Num >= CONNECT_LOOP_MAX Then46         Connect_Num = 047         Disconnect48     End If49 End Sub50 51 Public Sub DBapi_Disconnect() ‘強制關閉api方式訪問的資料庫，計數器複位52     Connect_Num = 053     Disconnect54 End Sub55 56 Public Sub ExecSql(ByVal TmpSql As String)   ‘執行資料庫動作陳述式57     On Error GoTo Err:58     Dim cmd As New ADODB.Command  ‘建立Command對象cmd59     DB_Connect    ‘串連到資料庫60     Set cmd.ActiveConnection = AdoCnn  ‘設定cmd的ActiveConnection屬性，指定與其關聯的資料庫連接61     cmd.CommandText = TmpSql  ‘設定要執行的命令文本62     cmd.Execute63     Set cmd = Nothing64     DB_Disconnect65     Exit Sub66 Err:67     MsgBox Err.Description, 64, "ExecSql"68 End Sub69 70 Public Function QuerySql(ByVal TmpSql As String) As ADODB.Recordset ‘執行資料庫查詢語句71     On Error GoTo Err:72     Dim rst As New ADODB.Recordset73     DB_Connect    ‘串連到資料庫74     If IsConnect = False Then Exit Function75     Set rst.ActiveConnection = AdoCnn    ‘設定rst的ActiveConnection屬性，指定與其關聯的資料庫連接76     rst.CursorType = adOpenKeyset77     rst.LockType = adLockOptimistic   ‘設定鎖定類型78     rst.Open TmpSql    ‘開啟記錄集79     Set QuerySql = rst    ‘返回記錄集80     Exit Function81 Err:82     MsgBox Err.Description, 64, "QuerySql"83 End Function84 85 Public Function GetFieldValue(FieldValue As Variant) As String86     GetFieldValue = IIf(Not IsNull(FieldValue), FieldValue, "")87 End Function