下載access

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

test

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

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

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

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

Option Explicit
Sub 連接數(shù)據(jù)庫()
    '第一步：告訴電腦，我們要用ADO吃靠，就是引用ADO工具硫眨。點擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對象
    '2-1：聲明連接對象變量
    Dim con As ADODB.Connection, sql As String
    '2-2：創(chuàng)建對象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語句，這里為插入一條數(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ù)庫刪除數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫()
    '第一步：告訴電腦撩笆，我們要用ADO捺球，就是引用ADO工具。點擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對象
    '2-1：聲明連接對象變量
    Dim con As ADODB.Connection, sql As String
    '2-2：創(chuàng)建對象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語句夕冲，這里為刪除一條數(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ù)庫修改數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫()
    '第一步：告訴電腦氮兵，我們要用ADO，就是引用ADO工具歹鱼。點擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對象
    '2-1：聲明連接對象變量
    Dim con As ADODB.Connection, sql As String
    '2-2：創(chuàng)建對象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語句泣栈，這里為修改一條數(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ù)庫查詢數(shù)據(jù)

Option Explicit
Sub 連接數(shù)據(jù)庫()
    '第一步：告訴電腦，我們要用ADO弥姻，就是引用ADO工具南片。點擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對象
    '2-1：聲明連接對象變量
    Dim con As ADODB.Connection, studentRecordSet As New ADODB.recordSet, sql As String, i As Integer
    '2-2：創(chuàng)建對象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語句，這里為查詢語句
    sql = "select * from student"
    '生成數(shù)據(jù)庫查詢結(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ù)庫查詢到的數(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ù)庫()
    '第一步：告訴電腦秧廉，我們要用ADO伞广，就是引用ADO工具。點擊工具-引用-勾選0biects 2.8Microsoft hetivex Data Obiects 6 1
    '第二步：創(chuàng)建連接對象
    '2-1：聲明連接對象變量
    Dim con As ADODB.Connection, studentRecordSet As New ADODB.recordSet, sql As String, i As Integer
    '2-2：創(chuàng)建對象并賦值
    Set con = New ADODB.Connection
    '第三步：建立數(shù)據(jù)庫連接
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = ThisWorkbook.Path & "/test.mdb"
        .Open
    End With
    '輸入執(zhí)行的sql語句疼电，這里為查詢語句
    sql = "select * from student"
    '生成數(shù)據(jù)庫查詢結(jié)果集嚼锄，獲得的對象是只讀的，不能修改記錄蔽豺，不能獲取查到數(shù)據(jù)的條數(shù)区丑。
    'Set studentRecordSet = con.Execute(sql)
    
    '通過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ù)庫查詢到的數(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)閉按鈕對應(yīng)的點擊事件，點擊后釋放連接并且卸載窗口
Private Sub CommandButton1_Click()
    '按加載順序反向關(guān)閉
    Set studentRecordSet = Nothing
    con.Close: Set con = Nothing
    Unload Me
End Sub


'第一步号杏，打開表單時將所有的部門填充到listbox中
Private Sub UserForm_Initialize()
    Dim sql As String, i As Integer
    '窗體初始化時創(chuàng)建數(shù)據(jù)庫連接對象婴氮，并建立鏈接
    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
'第二步斯棒，點擊的部門列表框，查詢該部門對應(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
'第三步，點擊人員列表框罩驻，查詢對應(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分頁查詢小案例

image.png

Dim con As ADODB.Connection, studentRecordSet As ADODB.Recordset, commonPageNum As Integer, totalPage As Integer
'選擇分頁大小
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
'點擊第一頁
Private Sub CommandButton2_Click()
    Call RefreshForm(ComboBox1.Value, 1)
End Sub
'點擊上一頁
Private Sub CommandButton3_Click()
    If commonPageNum > 1 Then
         Call RefreshForm(ComboBox1.Value, commonPageNum - 1)
    End If
End Sub
'點擊下一頁
Private Sub CommandButton4_Click()
    If commonPageNum < totalPage Then
         Call RefreshForm(ComboBox1.Value, commonPageNum + 1)
    End If
End Sub
'點擊最后一頁
Private Sub CommandButton5_Click()
    Call RefreshForm(ComboBox1.Value, totalPage)
End Sub

Private Sub UserForm_Initialize()
    Dim i As Integer
 '窗體初始化時創(chuàng)建數(shù)據(jù)庫連接對象，并建立鏈接
    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
    '初始化分頁大小選擇范圍
    For i = 1 To 20
        ComboBox1.AddItem i
    Next
    '設(shè)置默認分頁大小為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
    '查詢分頁數(shù)據(jù):分頁參考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計數(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ù)的每個字段
            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