VB6/VBA 连接access数据库--类模块代码

Option Explicit

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

''数据库连接字符

Private Function CNSTR() As String

    Dim STR As String, PDER As String, SOUR As String

   '' PDER = "Microsoft.ace.OLEDB.12.0" ''数据库引擎accdb格式
    PDER = "Microsoft.Jet.OLEDB.4.0" ''数据库引擎

    SOUR = App.Path & "\db_gzb.mdb"  ''数据库地址


    STR = STR + "Provider=" + PDER + ";"

    STR = STR + "Data Source=" + SOUR + ";"

    STR = STR + "Persist Security Info=False;"

    CNSTR = STR


End Function



''初始化数据库

Public Function db_con() As Boolean

    On Error GoTo er

    Set cn = New ADODB.Connection

    cn.Open CNSTR

    db_con = True

    Exit Function

er:

    db_con = False

    MsgBox "数据库连接失败:" & Err.Description

End Function





Public Function db_cmd(sql As String) As Boolean

    If cn.State <> 1 Then db_con   '检查数据库是否连接成功

    '执行SQL语句

    On Error GoTo er

    cn.Execute sql

    db_cmd = True

    Exit Function

er:

   db_cmd = False

   MsgBox "sql执行失败:" & Err.Description

End Function



Public Function db_rs(sql As String) As ADODB.Recordset



    If cn.State <> 1 Then db_con   '检查数据库是否连接成功

    '返回记录集

    On Error GoTo er

    

    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseClient

    rs.Open sql, cn, adOpenKeyset, adLockPessimistic

    Set db_rs = rs

    Exit Function

er:

    MsgBox "记录集返回失败:" & Err.Description

End Function

dim cn as new cls_db
sub con()
  cn.db_con
  cn.db_cmd("insert into ......")
  cn.db_rs("select * from ......")
end if