【代码】EXCEL VBA操作ACCESS数据库，实现增删改查【基础篇】（免费）！

Dim cnn As ObjectDim rs As ObjectDim strCnn As String '//连接字符串Dim dbs As StringDim tbl As StringDim sql As StringDim temp()
Sub sqlQuery(sql As String)    '//创建连接对象    Set cnn = CreateObject("ADODB.Connection")        '//ACCESS数据库文件路径    dbs = ThisWorkbook.Path & "\DataBase1019.accdb"    '    '//表名'    tbl = "tb员工"'    '//创建连接字符串    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbs            '//打开数据库连接        cnn.Open strCnn    '    '//sql语句，选择tbl表中所有内容'    sql = "select * from " & tbl        Set rs = cnn.Execute(sql)
        '//把记录集存到数组    temp = rs.GetRows    Sheet2.UsedRange.Clear    Sheet2.Range("A1").Resize(UBound(temp) + 1, UBound(temp, 2) + 1) = temp        '//把记录集指针移到开头（前面getrows，指针已经移到了末尾）    rs.MoveFirst
    '// 遍历结果集    Do Until rs.EOF    Debug.Print rs.Fields("姓名").Value    rs.MoveNext    Loop        '//把记录集指针移到开头（前面遍历时，指针已经移到了末尾）    rs.MoveFirst        '//把记录集输出到工作表    Sheet1.UsedRange.Clear    Sheet1.Range("A1").CopyFromRecordset rs        '//清理对象    rs.Close    Set rs = Nothing    cnn.Close    Set cnn = Nothing        MsgBox "查询成功!"    End Sub

Sub sqlDelete(sql As String)        '//创建连接对象    Set cnn = CreateObject("ADODB.Connection")        '//ACCESS数据库文件路径    dbs = ThisWorkbook.Path & "\DataBase1019.accdb"        '//表名    tbl = "tb员工"        '//创建连接字符串    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbs        '//打开数据库连接        cnn.Open strCnn    cnn.Execute (sql)        cnn.Close    Set cnn = Nothing        MsgBox "删除成功!"End Sub
Sub sqlInsert()    '//创建连接对象    Set cnn = CreateObject("ADODB.Connection")        '//ACCESS数据库文件路径    dbs = ThisWorkbook.Path & "\DataBase1019.accdb"        '//表名    tbl = "tb员工"        '//创建连接字符串    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbs        '//打开数据库连接        cnn.Open strCnn        '//sql语句，插入新记录    sql = "Insert Into " & tbl & " (姓名, 出生日期, 部门, 住址) " & _             "VALUES ('李四', #1981-10-19#, '财务部', '江苏省南京市中山路1019号')"        cnn.Execute (sql)        cnn.Close    Set cnn = Nothing        MsgBox "插入成功!"
End Sub

Sub resetAge()    '//创建连接对象    Set cnn = CreateObject("ADODB.Connection")        '//ACCESS数据库文件路径    dbs = ThisWorkbook.Path & "\DataBase1019.accdb"        '//表名    tbl = "tb员工"        '//创建连接字符串    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbs            '//打开数据库连接        cnn.Open strCnn    '//sql语句，把所有年龄重置为0        sql = "update " & tbl & " set 年龄=0"        cnn.Execute (sql)        cnn.Close    Set cnn = Nothing''    MsgBox "年龄重置成功!"    End Sub
Sub sqlUpdate(sql)    '//创建连接对象    Set cnn = CreateObject("ADODB.Connection")        '//ACCESS数据库文件路径    dbs = ThisWorkbook.Path & "\DataBase1019.accdb"        '//表名    tbl = "tb员工"        '//创建连接字符串    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbs            '//打开数据库连接        cnn.Open strCnn    cnn.Execute (sql)        cnn.Close    Set cnn = Nothing        MsgBox "更新成功!"    End Sub