下載access

電腦內(nèi)存有限，下載了一個(gè)低版本的access2003。
51自學(xué)網(wǎng)對(duì)應(yīng)課程有個(gè)開放的下載地址
下載好后直接安裝就行。
打開access，新建一個(gè)test數(shù)據(jù)庫(kù)。

test

excel vba連接access數(shù)據(jù)庫(kù)

Option Explicit
Sub 連接數(shù)據(jù)庫(kù)()
    '第一步：告訴電腦，我們要用ADO，就是引用ADO工具。點(diǎn)擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    
    '第二步：創(chuàng)建連接對(duì)象
    '2-1：聲明連接對(duì)象變量
    Dim con As ADODB.Connection
    '2-2：創(chuàng)建對(duì)象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫(kù)連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With

    MsgBox "連接數(shù)據(jù)庫(kù)成功"
End Sub

excel vba向access數(shù)據(jù)庫(kù)插入數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫(kù)()
    '第一步：告訴電腦，我們要用ADO，就是引用ADO工具。點(diǎn)擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對(duì)象
    '2-1：聲明連接對(duì)象變量
    Dim con As ADODB.Connection, sql As String
    '2-2：創(chuàng)建對(duì)象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫(kù)連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語(yǔ)句，這里為插入一條數(shù)據(jù)
    sql = "insert into student values('001','張三',23)"
    con.Execute (sql)
    '釋放連接
    con.Close
    Set con = Nothing
    MsgBox "執(zhí)行成功"
End Sub

excel vba向access數(shù)據(jù)庫(kù)刪除數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫(kù)()
    '第一步：告訴電腦，我們要用ADO，就是引用ADO工具。點(diǎn)擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對(duì)象
    '2-1：聲明連接對(duì)象變量
    Dim con As ADODB.Connection, sql As String
    '2-2：創(chuàng)建對(duì)象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫(kù)連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語(yǔ)句，這里為刪除一條數(shù)據(jù)
    sql = "delete from student where name='張三'"
    con.Execute (sql)
    '釋放連接
    con.Close
    Set con = Nothing
    MsgBox "執(zhí)行成功"
End Sub

excel vba向access數(shù)據(jù)庫(kù)修改數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫(kù)()
    '第一步：告訴電腦，我們要用ADO，就是引用ADO工具。點(diǎn)擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對(duì)象
    '2-1：聲明連接對(duì)象變量
    Dim con As ADODB.Connection, sql As String
    '2-2：創(chuàng)建對(duì)象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫(kù)連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語(yǔ)句，這里為修改一條數(shù)據(jù)
    sql = "update student set age = 25 where name='張三'"
    con.Execute (sql)
    '釋放連接
    con.Close
    Set con = Nothing
    MsgBox "執(zhí)行成功"
End Sub

excel vba向access數(shù)據(jù)庫(kù)查詢數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫(kù)()
    '第一步：告訴電腦，我們要用ADO，就是引用ADO工具。點(diǎn)擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對(duì)象
    '2-1：聲明連接對(duì)象變量
    Dim con As ADODB.Connection, studentRecordSet As New ADODB.recordSet, sql As String, i As Integer
    '2-2：創(chuàng)建對(duì)象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫(kù)連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語(yǔ)句，這里為查詢語(yǔ)句
    sql = "select * from student"
    '生成數(shù)據(jù)庫(kù)查詢結(jié)果集
    Set studentRecordSet = con.Execute(sql)
    '循環(huán)記錄集的字段名，寫入到excel中
    For i = 0 To studentRecordSet.Fields.Count - 1
        'Fields代表獲取的所有字段名，從0開始遞增，Name屬性為字段名
        Cells(1, i + 1).Value = studentRecordSet.Fields(i).Name
    Next
    '將數(shù)據(jù)庫(kù)查詢到的數(shù)據(jù)顯示到excel中
    Range("a2").CopyFromRecordset studentRecordSet
    '釋放連接
    studentRecordSet.Close: Set studentRecordSet = Nothing
    con.Close: Set con = Nothing
    MsgBox "執(zhí)行成功"
End Sub

excel vba使用RecordSet的open方法獲得記錄集

Option Explicit
Sub 連接數(shù)據(jù)庫(kù)()
    '第一步：告訴電腦，我們要用ADO，就是引用ADO工具。點(diǎn)擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對(duì)象
    '2-1：聲明連接對(duì)象變量
    Dim con As ADODB.Connection, studentRecordSet As New ADODB.recordSet, sql As String, i As Integer
    '2-2：創(chuàng)建對(duì)象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫(kù)連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語(yǔ)句，這里為查詢語(yǔ)句
    sql = "select * from student"
    '生成數(shù)據(jù)庫(kù)查詢結(jié)果集，獲得的對(duì)象是只讀的，不能修改記錄，不能獲取查到數(shù)據(jù)的條數(shù)。
    'Set studentRecordSet = con.Execute(sql)
    
    '通過(guò)RecordSet的open方法獲得記錄集，獲得的記錄集可以修改，并且可以獲取查詢到數(shù)據(jù)的總條數(shù)
    studentRecordSet.Open sql, con, adOpenKeyset, adLockOptimistic
    
    '獲取記錄集的條數(shù)
    MsgBox studentRecordSet.RecordCount
    '數(shù)據(jù)寫入到excel前，將excel表格清空
    '循環(huán)記錄集的字段名，寫入到excel中
    For i = 0 To studentRecordSet.Fields.Count - 1
        'Fields代表獲取的所有字段名，從0開始遞增，Name屬性為字段名
        Cells(1, i + 1).Value = studentRecordSet.Fields(i).Name
    Next
    '將數(shù)據(jù)庫(kù)查詢到的數(shù)據(jù)顯示到excel中
    Range("a2").CopyFromRecordset studentRecordSet
    '釋放連接
    studentRecordSet.Close: Set studentRecordSet = Nothing
    con.Close: Set con = Nothing
    MsgBox "執(zhí)行成功"
End Sub

excel vba增刪改查小案例

image.png

Dim con As ADODB.Connection, studentRecordSet As ADODB.Recordset, itemDataArr As Variant
'關(guān)閉按鈕對(duì)應(yīng)的點(diǎn)擊事件，點(diǎn)擊后釋放連接并且卸載窗口
Private Sub CommandButton1_Click()
    '按加載順序反向關(guān)閉
    Set studentRecordSet = Nothing
    con.Close: Set con = Nothing
    Unload Me
End Sub


'第一步，打開表單時(shí)將所有的部門填充到listbox中
Private Sub UserForm_Initialize()
    Dim sql As String, i As Integer
    '窗體初始化時(shí)創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象，并建立鏈接
    Set con = New ADODB.Connection
    Set studentRecordSet = New ADODB.Recordset
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    sql = "select distinct apartment from student"
    studentRecordSet.Open sql, con, adOpenKeyset, adLockOptimistic
    For i = 1 To studentRecordSet.RecordCount
        ListBox1.AddItem studentRecordSet("apartment")
        studentRecordSet.MoveNext
    Next
    studentRecordSet.Close
End Sub
'第二步，點(diǎn)擊的部門列表框，查詢?cè)摬块T對(duì)應(yīng)的人員，填充到人員列表框中,itemDataArr為條目附加信息數(shù)組
Private Sub ListBox1_Click()
    Dim sql As String, i As Integer
    sql = "select id,name from student where apartment='" & ListBox1.Value & "'"
    '執(zhí)行查詢
    studentRecordSet.Open sql, con, adOpenKeyset, adLockOptimistic
    '重新定義附加數(shù)組大小
    ReDim itemDataArr(studentRecordSet.RecordCount - 1)
    ListBox2.Clear
    For i = 1 To studentRecordSet.RecordCount
        '將查詢到的數(shù)據(jù)塞到人員列表框中，附加信息添加到附加數(shù)組中
        ListBox2.AddItem studentRecordSet("name")
        itemDataArr(i - 1) = studentRecordSet("id")
        studentRecordSet.MoveNext
    Next
    studentRecordSet.Close
End Sub
'第三步，點(diǎn)擊人員列表框，查詢對(duì)應(yīng)人員信息，填充到文本框中
Private Sub ListBox2_Click()
    Dim sql As String
    sql = "select * from student where id = '" & itemDataArr(ListBox2.ListIndex) & "'"
     '執(zhí)行查詢
    studentRecordSet.Open sql, con, adOpenKeyset, adLockOptimistic
    TextBox1.Value = studentRecordSet("name")
    TextBox2.Value = studentRecordSet("age")
    TextBox3.Value = studentRecordSet("apartment")
    studentRecordSet.Close
End Sub

excel vba分頁(yè)查詢小案例

image.png

Dim con As ADODB.Connection, studentRecordSet As ADODB.Recordset, commonPageNum As Integer, totalPage As Integer
'選擇分頁(yè)大小
Private Sub ComboBox1_Change()
    Call RefreshForm(ComboBox1.Value, 1)
End Sub

'釋放連接關(guān)閉窗口
Private Sub CommandButton1_Click()
'按加載順序反向關(guān)閉
    Set studentRecordSet = Nothing
    con.Close: Set con = Nothing
    Unload Me
End Sub
'點(diǎn)擊第一頁(yè)
Private Sub CommandButton2_Click()
    Call RefreshForm(ComboBox1.Value, 1)
End Sub
'點(diǎn)擊上一頁(yè)
Private Sub CommandButton3_Click()
    If commonPageNum > 1 Then
         Call RefreshForm(ComboBox1.Value, commonPageNum - 1)
    End If
End Sub
'點(diǎn)擊下一頁(yè)
Private Sub CommandButton4_Click()
    If commonPageNum < totalPage Then
         Call RefreshForm(ComboBox1.Value, commonPageNum + 1)
    End If
End Sub
'點(diǎn)擊最后一頁(yè)
Private Sub CommandButton5_Click()
    Call RefreshForm(ComboBox1.Value, totalPage)
End Sub

Private Sub UserForm_Initialize()
    Dim i As Integer
 '窗體初始化時(shí)創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象，并建立鏈接
    Set con = New ADODB.Connection
    Set studentRecordSet = New ADODB.Recordset
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '初始化分頁(yè)大小選擇范圍
    For i = 1 To 20
        ComboBox1.AddItem i
    Next
    '設(shè)置默認(rèn)分頁(yè)大小為5
    ComboBox1.Value = 5
    Call RefreshForm(5, 1)
End Sub

'刷新表單
Public Sub RefreshForm(pageSize As Integer, pageNum As Integer)
    Dim sql As String, i As Integer, listItem As listItem, j As Integer
    '記錄pageNum
    commonPageNum = pageNum
    '查詢分頁(yè)數(shù)據(jù):分頁(yè)參考https://blog.csdn.net/lfq761204/article/details/127555263
    sql = "select top " & pageSize & " * from (select top " & pageNum * pageSize & " * from student order by id desc) order by id asc"
    '執(zhí)行查詢
    studentRecordSet.Open sql, con, adOpenKeyset, adLockOptimistic
    '生成表頭
    With ListView1
        .ColumnHeaders.Clear
        .ListItems.Clear
        .View = lvwReport
        .FullRowSelect = True
        .Gridlines = True
        '遍歷查詢到的數(shù)據(jù)表表頭
        For i = 0 To studentRecordSet.Fields.Count - 1
        'Fields計(jì)數(shù)從零開始
        .ColumnHeaders.Add , , studentRecordSet.Fields(i).Name, .Width / studentRecordSet.Fields.Count
        Next
    End With
    '填入數(shù)據(jù)到表單
    With ListView1
        .ListItems.Clear
        '遍歷查詢到的數(shù)據(jù)
         For i = 1 To studentRecordSet.RecordCount
            Set listItem = .ListItems.Add
            listItem.Text = studentRecordSet.Fields(0).Value
            '遍歷每條數(shù)據(jù)的每個(gè)字段
            For j = 1 To studentRecordSet.Fields.Count - 1
                listItem.SubItems(j) = studentRecordSet.Fields(j).Value
            Next
            studentRecordSet.MoveNext
         Next
    End With
    studentRecordSet.Close
    '查詢總條數(shù)
    sql = "select count(*) as totalRecord from student"
    Set studentRecordSet = con.Execute(sql)
    totalPage = Application.WorksheetFunction.Ceiling(studentRecordSet("totalRecord") / pageSize, 1)
    TextBox1.Value = pageNum & "/" & totalPage
    studentRecordSet.Close
End Sub