Kết nối access với SQL Server

Hướng dẫn sử dụng VBA Access kết nối SQL Server chi tiết từ A-Z

Thêm thư viện ADO
Chuổi kết nối đến SQL Server
- Chuổi kết nối (còn gọi là Connect String) là gì ?
- Các mẫu chuổi kết nối hay sử dụng để kết nối SQL Server
Các đối tượng trong VBA Access để kết nối đến SQL Server
Kết nối đến SQL Server
Các ví dụ ứng dụng kết nối VBA Access đến SQL Server
Một số chú ý khi kết nối VBA Access với SQL Server

Hướng dẫn cách kết nối VBA Access với SQL Server

1. Thêm thư viện ADO

Bước này chỉ làm 1 lần duy nhất

Trong cửa sổ VBA (Alt + F11):

    Vào menu: Tools → References
    Tìm và tick chọn:
    ✅ Microsoft ActiveX Data Objects 6.1 Library (hoặc 2.8 nếu không có 6.1)

2. Chuổi kết nối đến SQL Server

Chuổi kết nối (còn gọi là Connect String) là gì ?

Là câu kết nối từ ứng dụng MS Access đến SQL Server, câu này chứa thông tin gồm máy chủ, user, pass của máy chủ

Các mẫu chuổi kết nối hay sử dụng để kết nối SQL Server

- Chuỗi kết nối: sử dụng SQL Server Authentication

    strConn = "Provider=SQLOLEDB;" & _
              "Data Source=TenServerHoacIP;" & _
              "Initial Catalog=TenDatabase;" & _
              "User ID=ten_dang_nhap;" & _
              "Password=mat_khau;"

- Chuỗi kết nối: Windows Authentication (không có User/Password)
    strConn = "Provider=SQLOLEDB;" & _
          "Data Source=TenServer;" & _
          "Initial Catalog=TenDatabase;" & _
          "Integrated Security=SSPI;"

3. Các đối tượng trong VBA Access để kết nối đến SQL Server

Dim conn As ADODB.Connection

Kết nối đến SQL Server, giống như 1 sợ dây điện kết nối đến máy chủ.

Dim rs As ADODB.Recordset

Sau khi lấy dữ liệu từ table / view thì biến Recordset chứa đựng giá trị.

Dim cmd As ADODB.Command

Thực thi gởi các câu lệnh như Inser / update / delete

4. Kết nối đến SQL Server

Mở kết nối SQL Server

     Dim conn As ADODB.Connection

    Dim strConn As String

    ' Tạo đối tượng connection

    Set conn = New ADODB.Connection



    ' Chuỗi kết nối: sử dụng SQL Server Authentication

    strConn = "Provider=SQLOLEDB;" & _

              "Data Source=TenServerHoacIP;" & _

              "Initial Catalog=TenDatabase;" & _

              "User ID=ten_dang_nhap;" & _

              "Password=mat_khau;"



    ' Mở kết nối

    conn.Open strConn

Đóng kết nối SQL Server

 conn.Close

Tại sao cần phải đóng kết nối sau khi xử lý với SQL Server xong

Việc đóng kết nối sau khi mở trong lập trình (như ADO/VBA) là bắt buộc và rất quan trọng vì các lý do sau:

✅ Giải phóng tài nguyên hệ thống

    Mỗi kết nối SQL Server mở ra sẽ chiếm tài nguyên (RAM, socket TCP, thread...).
    Nếu bạn mở nhưng không đóng, tài nguyên đó không được thu hồi, gây rò rỉ (leak).
    Với nhiều người dùng, việc không đóng kết nối có thể làm quá tải SQL Server hoặc Access bị treo.

🔍 Ví dụ: mở 1 form nhập liệu → chạy Sub mở kết nối → không đóng → mỗi lần click, tạo thêm 1 kết nối nữa → 100 lần click = 100 kết nối mở.

✅ Giới hạn số kết nối của SQL Server

SQL Server có giới hạn số kết nối đồng thời (concurrent connections).
Nếu bạn mở kết nối mà không đóng, chương trình khác không thể truy cập vì hết slot.

✅ Tăng hiệu năng với Connection Pooling

Khi bạn đóng kết nối đúng cách, .NET/ADO sẽ giữ lại kết nối trong pool, sẵn sàng dùng lại nhanh hơn.
Nếu bạn không đóng, hệ thống không biết khi nào nên tái sử dụng kết nối → gây chậm.

✅ Tránh lỗi "Too many connections" hoặc "Timeout"

    Khi số kết nối vượt mức, bạn có thể gặp lỗi như:
        Timeout expired.
        Cannot open database requested...
        Connection limit exceeded.

4. Các ví dụ ứng dụng kết nối VBA Access đến SQL Server

Lấy dữ liệu từ table SQL Server từ VBA Access

Sub KetNoiSQL()

    Dim conn As ADODB.Connection   

    Dim strConn As String   



    ' Tạo đối tượng connection

    Set conn = New ADODB.Connection



    ' Chuỗi kết nối: sử dụng SQL Server Authentication

    strConn = "Provider=SQLOLEDB;" & _

              "Data Source=TenServerHoacIP;" & _

              "Initial Catalog=TenDatabase;" & _

              "User ID=ten_dang_nhap;" & _

              "Password=mat_khau;"



    ' Mở kết nối

    conn.Open strConn



    Dim sql As String

    Dim rs As ADODB.Recordset

    ' Câu truy vấn ví dụ

    sql = "SELECT * FROM HocSinh"



    ' Mở recordset

    Set rs = New ADODB.Recordset

    rs.Open sql, conn, adOpenStatic, adLockReadOnly



    ' Hiển thị dữ liệu (ví dụ in ra Immediate Window - Ctrl+G)

    Do While Not rs.EOF

        Debug.Print rs!TenHS

        rs.MoveNext

    Loop



    ' Đóng kết nối

    rs.Close

    conn.Close

    Set rs = Nothing

    Set conn = Nothing



    MsgBox "Xong!"

End Sub

Thêm dữ liệu vào table sử dụng biến Recordset

    Dim conn As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim strConn As String



    ' 1. Kết nối SQL Server

    strConn = "Provider=SQLOLEDB;" & _

              "Data Source=TEN_SERVER;" & _

              "Initial Catalog=TEN_DATABASE;" & _

              "User ID=ten_dang_nhap;" & _

              "Password=mat_khau;"



    Set conn = New ADODB.Connection

    conn.Open strConn



    ' 2. Mở Recordset cho phép ghi

    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM HocSinh", conn, adOpenDynamic, adLockOptimistic



    ' 3. Thêm dòng mới

    rs.AddNew

    rs!TenHS = "Trần Văn C"

    rs!NgaySinh = #3/15/2012#

    rs.Update



    ' 4. Đóng kết nối

    rs.Close: conn.Close

    Set rs = Nothing: Set conn = Nothing



    MsgBox "Đã thêm học sinh!"

Chèn dữ liệu vào table SQL Server từ VBA Access

Dim conn As ADODB.Connection    

    Dim strConn As String



    ' Chuỗi kết nối SQL Server (có thể dùng Windows Auth hoặc SQL Auth)

    strConn = "Provider=SQLOLEDB;" & _

              "Data Source=TEN_SERVER;" & _

              "Initial Catalog=TEN_DATABASE;" & _

              "User ID=ten_dang_nhap;" & _

              "Password=mat_khau;"



    ' Tạo đối tượng connection và command

    Set conn = New ADODB.Connection

    conn.Open strConn
    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command

    cmd.ActiveConnection = conn

    cmd.CommandText = "Insert into HocSinh(TT,HoTen) values(15,'A')"

    cmd.Execute



    conn.Close

    Set cmd = Nothing

    Set conn = Nothing



    MsgBox "Đã chèn xong học sinh!"

Gọi 1 thủ tục từ SQL Server

    Dim conn As ADODB.Connection

    Dim cmd As ADODB.Command

    Dim strConn As String



    ' 1. Chuỗi kết nối

    strConn = "Provider=SQLOLEDB;" & _

              "Data Source=TEN_SERVER;" & _

              "Initial Catalog=TEN_DATABASE;" & _

              "User ID=ten_dang_nhap;" & _

              "Password=mat_khau;"



    ' 2. Tạo kết nối

    Set conn = New ADODB.Connection

    conn.Open strConn



    ' 3. Tạo lệnh gọi thủ tục

    Set cmd = New ADODB.Command

    With cmd

        .ActiveConnection = conn

        .CommandType = adCmdStoredProc

        .CommandText = "sp_ThemHocSinh"



        ' Truyền tham số

        .Parameters.Append .CreateParameter("@TenHS", adVarWChar, adParamInput, 100, "Nguyễn Văn B")

        .Parameters.Append .CreateParameter("@NgaySinh", adDate, adParamInput, , #1/1/2009#)



        ' Gọi thủ tục

        .Execute

    End With



    ' 4. Đóng kết nối

    conn.Close

    Set cmd = Nothing

    Set conn = Nothing



    MsgBox "Đã chèn học sinh bằng Stored Procedure!"

Gọi thủ tục có trả về 1 bảng dữ liệu

    Dim conn As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim cmd As ADODB.Command

    Dim strConn As String



    ' 1. Chuỗi kết nối SQL Server

    strConn = "Provider=SQLOLEDB;" & _

              "Data Source=TEN_SERVER;" & _

              "Initial Catalog=TEN_DATABASE;" & _

              "User ID=ten_dang_nhap;" & _

              "Password=mat_khau;"



    ' 2. Mở kết nối

    Set conn = New ADODB.Connection

    conn.Open strConn



    ' 3. Tạo và cấu hình command

    Set cmd = New ADODB.Command

    With cmd

        .ActiveConnection = conn

        .CommandType = adCmdStoredProc

        .CommandText = "sp_DanhSachHocSinh"

    End With



    ' 4. Mở recordset để nhận kết quả trả về (giống DataTable)

    Set rs = New ADODB.Recordset

    rs.Open cmd, , adOpenStatic, adLockReadOnly



    ' 5. Duyệt dữ liệu (giống DataTable.Rows)

    Do While Not rs.EOF

        Debug.Print rs!MaHS & " - " & rs!TenHS & " - " & rs!NgaySinh

        rs.MoveNext

    Loop



    ' 6. Đóng kết nối

    rs.Close: Set rs = Nothing

    conn.Close: Set conn = Nothing



    MsgBox "Đã đọc xong danh sách!"

End Sub

4. Một số chú ý khi kết nối VBA Access với SQL Server

Khi table có cột dạng identity thì sử dụng câu kết nối là

Set rst = dbs.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

* Thêm thư viện vào trong file Access

*** Viết code VBA Access kết nối với SQL Server

    Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=HONGNHAT2\SQLEXPRESS;DATABASE=nhanvien;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 30
    cn.Mode = adModeReadWrite
    cn.Open

    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandTimeout = 60
        .CommandText = "Select * From dbo.tbNhanVien"
     End With
     Dim prm As Object

    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd


    Set rcs = cmd.Execute

    Do While (Not rcs Is Nothing)
        If rcs.State = adStateClosed Then Exit Do
        While Not rcs.EOF
            'For i = 0 To rcs.Fields.Count - 1
            '    rStr = rStr & " : " & rcs(i)
           ' Next i
            MsgBox rcs!HoTen

            rcs.MoveNext
        Wend
        Set rcs = rcs.NextRecordset
    Loop

** Thay thế các chữ màu đỏ lại cho phù hợp trong đoạn code VBA trên,

Tìm kiếm: