VBA Access Database Connection Guide

2 years ago
Olivia Parker
1 minute

Here is a simple example of accessing an Access database using VBA.

Sub AccessDBExample()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    ' 连接到Access数据库
    Set db = OpenDatabase("C:\path\to\your\database.accdb")
    
    ' 构建SQL查询语句
    strSQL = "SELECT * FROM TableName"
    
    ' 执行查询语句并获取结果集
    Set rs = db.OpenRecordset(strSQL)
    
    ' 遍历结果集并输出数据
    Do Until rs.EOF
        Debug.Print rs.Fields("FieldName").Value
        rs.MoveNext
    Loop
    
    ' 关闭结果集和数据库连接
    rs.Close
    db.Close
    
    ' 释放对象变量
    Set rs = Nothing
    Set db = Nothing
End Sub

Before using this example, make sure to replace “C:\path\to\your\database.accdb” with the actual file path of your Access database, replace “TableName” with the actual table name, and “FieldName” with the actual field name.

This example utilizes the DAO object model (Data Access Objects), so you will need to add a reference to “Microsoft DAO 3.6 Object Library” in the “References” under the “Tools” menu in the VBA editor.

bannerAds