Truy vấn cơ sở dữ liệu Access từ VBA excel

Euler

Administrator
Thành viên BQT
Các chủ đề trước đã trình bày:
-
-

Trong chủ đề này, mình sẽ trình bày cách để kết nối với cơ sở dữ liệu và lấy thông tin từ nó.
1. Kết nối:
Mã:
Public cnn As New ADODB.Connection
Sub Moketnoi()
Set cnn = New ADODB.Connection
Dim strCNString As String
strCNString = "C:\Users\jpnfriend.net\Desktop\VBA\NewDB33.mdb"
With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0" 'Access truoc day la: "Microsoft Jet 4.0 OLE DB Provider"
        .ConnectionString = linkdb
        .Properties("Jet OLEDB:Database Password") = "1234"
        .Open
End With

End Sub
2. Lấy dữ liệu
2.1 Lấy tất cả các cột trong bảng:

Mã:
SELECT * FROM TenBang
Cụ thể hãy xem ví dụ dưới đây:
Ta có bảng dữ liệu như sau:
Bạn cần đăng nhập để thấy hình ảnh


Mã:
Sub ketnoivalaydulieu()
    Dim cnn As Object: Dim rst As Object
    Dim lsSQL As String
    Dim linkdb  As String
    Dim arr As Variant
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    linkdb = "C:\VBA\NewDB33.mdb"
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0" 'Access truoc day la: "Microsoft Jet 4.0 OLE DB Provider"
        .ConnectionString = linkdb
        .Properties("Jet OLEDB:Database Password") = "1234"
        .Open
    End With
    
    lsSQL = "SELECT * " & _
            "FROM Test"
            
    rst.Open lsSQL, cnn
    If rst.EOF Then
        MsgBox "Khong tim thay du lieu nao"
    Else
        arr = rst.GetRows
    End If
    
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Kết quả chạy code là:
Bạn cần đăng nhập để thấy hình ảnh
 

Euler

Administrator
Thành viên BQT
2.2 Lấy cột bất kỳ
Cú pháp:
Mã:
SELECT TenCotCanLay FROM TenBang
Cụ thể:
Trong chương trình trên ta thay đổi như sau:
Mã:
 lsSQL = "SELECT SignalName " & _
            "FROM Test"
Kết quả:
Bạn cần đăng nhập để thấy hình ảnh


Bây giờ tôi lấy ví dụ là chỉ lấy 3 cột (SignalName, Giatri, SigSize) Lưu ý đối với những cột có tên đặc biệt và có cách trắng chúng ta nên thêm [] vào tên cột đó.
Mã:
 lsSQL = "SELECT SignalName, Giatri,[SigSize] " & _
            "FROM Test"
Kết quả:
Bạn cần đăng nhập để thấy hình ảnh


Lưu ý nếu ta select cột nào trước thì nó sẽ ra cột đó trước, ví dụ tôi lấy cột Giatri trước thì nó sẽ hiển thị cột này trước, mặc dù vị trí thực của nó nằm trong bảng ở sau các cột kia.
2.3. Lọc có điều kiện
Mã:
Select TenCot.....
         From TenBang
         Where  Điều kiện của TenCot cần lọc
Các phép logic thường gặp:
= So sánh bằng
<> So sánh không bằng, khác
> Lớn hơn
< Nhỏ hơn
>= Lớn hơn hoặc bằng
<= Nhỏ hơn hoặc bằng
BETWEEN Nằm giữa một khoảng
LIKE So sánh mẫu chuỗi

Ví dụ:
Mã:
lsSQL = "SELECT * " & _
            "FROM Test Where ID<5"
Ta được kết quả:
Bạn cần đăng nhập để thấy hình ảnh

Thêm một ví dụ nữa:
Mã:
lsSQL = "SELECT * " & _
            "FROM Test Where SignalName='SignalName4'"
Kết quả:
Bạn cần đăng nhập để thấy hình ảnh

Chú ý nếu là số thì viết trực tiếp vào phép logic, với string thì phải cho vào dấu ngoặc đơn như trong ví dụ trên nhé.
Mình để file access ở đây, các bạn download về và luyện tập nhé.
Mã:
http://www.mediafire.com/file/aa3c5vmmz2xbpnb/NewDB33.rar/file
 

vbano1

SMod
Thành viên BQT
DISTINCT: lọc duy nhất
Mã:
SELECT DISTINCT column1, column2, ...
FROM table_name
Chạy code:
Mã:
lsSQL = "SELECT DISTINCT SignalName " & _
            "FROM Test"
     
     
    rst.Open lsSQL, cnn
    If rst.EOF Then
        MsgBox "Khong tim thay du lieu nao"
    Else
        [A2].CopyFromRecordset rst
    End If
Kết quả: Chú ý bên phải là ảnh của cơ sở dữ liệu, bên trái là kết quả chạy code.
Bạn cần đăng nhập để thấy hình ảnh


Ta thấy cột signalName đã được lọc duy nhất.
Chúng ta thay đổi code một chút:
Mã:
lsSQL = "SELECT DISTINCT SignalName,Giatri " & _
            "FROM Test"
Kết quả như sau:
Là bởi vì lúc này ta hiểu nó nối hai chuỗi SignalName và Giatri vào làm một string để so sánh.
Bạn cần đăng nhập để thấy hình ảnh


Để làm rõ hơn, ta sửa lại cơ sở dữ liệu và chạy lại code trên ta sẽ thấy:
Bạn cần đăng nhập để thấy hình ảnh
 

Euler

Administrator
Thành viên BQT
Trong tất cả các hàm thường gặp của VBA ta đều có thể sử dụng trong câu lệnh truy vấn cơ sở dữ liệu.
Ví dụ: INSTR
Chú ý: với số thì ta viết trực tiếp, với string ta phải cho vào dấu ngoặc đơn 'string'
Mã:
lsSQL = "SELECT * " & _
            "FROM Test WHERE instr(1,SignalName,'abc')>0"
            
    rst.Open lsSQL, cnn
    If rst.EOF Then
        MsgBox "Khong tim thay du lieu nao"
    Else
        [A2].CopyFromRecordset rst
        
    End If
Kết quả được thể hiện như ảnh dưới đây.
Bạn cần đăng nhập để thấy hình ảnh
 

tuhocvba

Administrator
Thành viên BQT
Lệnh sắp xếp: ORDER BY
Mã:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC
Trong đó ASC là sắp xếp tăng dần, DESC là sắp xếp giảm dần.
Ví dụ:
Mã:
lsSQL = "SELECT * " & _
            "FROM Test Order by ID ASC"
Kết quả:
Ở đây mình cố tình để ID lộn xộn và kết quả sau khi sắp xếp là rất ổn.
Cái này rất hay sử dụng, đặc biệt là khi các bạn , nhiều khi thứ tự dòng bị đảo lung tung. Vì vậy lệnh sắp xếp này là không thể thiếu khi xử lý dữ liệu như thế.

Bạn cần đăng nhập để thấy hình ảnh
 

Euler

Administrator
Thành viên BQT
Các lệnh logic AND, ORNOT.
Logic AND
Mã:
SELECT column1, column2, ...
FROM table_name
WHERE điều kiện 1 AND điều kiện 2 AND điều kiện 3 ...
Logic OR
Chú ý: condition dịch ra là điều kiện.
Mã:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...
Logic NOT
Mã:
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition
Ví dụ:
Mã:
lsSQL = "SELECT * " & _
            "FROM Test Where (((ID>3) AND (ID<20)) OR (Giatri=182)) AND(NOT(SigSize Mod 2 = 0))"
Kết quả: Bên phải là cơ sở dữ liệu, bên trái là kết quả chạy code.
Bạn cần đăng nhập để thấy hình ảnh
 

tuhocvba

Administrator
Thành viên BQT
Câu lệnh INSERT:
Cú pháp:
Mã:
INSERT INTO table_name
VALUES (value1, value2, value3, ...)
Được dùng khi bạn muốn thêm một bản ghi vào cơ sở dữ liệu.
Cụ thể hãy nhìn hình minh họa ở dưới:
Bạn cần đăng nhập để thấy hình ảnh

Trạng thái 1: Là trạng thái ban đầu của cơ sở dữ liệu.
Trạng thái 2: là trạng thái sau khi chạy code dưới đây:
Mã:
 lsSQL = "INSERT INTO DATA (SOCT, maso, name)" & _
            "VALUES ('A005','tuhocvba','website VBA')"
Ở đây tôi chỉ định cụ thể từng trường dữ liệu (tên cột dữ liệu).
Việc này khá vất vả, tôi lược bỏ tên trường dữ liệu đi và kết quả vẫn ổn.

Trạng thái 3: là trạng thái sau khi chạy code dưới đây:
Mã:
Sub update()
    Dim cnn As Object: Dim rst As Object
    Dim lsSQL As String
    Dim linkdb  As String
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    linkdb = ThisWorkbook.Path & Application.PathSeparator & "tuhocvba.mdb"
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0" 'Access truoc day la: "Microsoft Jet 4.0 OLE DB Provider"
        .ConnectionString = linkdb
'        .Properties("Jet OLEDB:Database Password") = ""
        .Open
    End With
    'INSERT INTO table_name (column1, column2, column3, ...)
'    VALUES (value1, value2, value3, ...)
    lsSQL = "INSERT INTO DATA " & _
            "VALUES ('A006','tuhocvba.net','website VBA')"
            
    rst.Open lsSQL, cnn

    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
 

tuhocvba

Administrator
Thành viên BQT
Update: Khi bạn cần cập nhật lại dữ liệu, chỉnh sửa lại số liệu.
Cú pháp:
Mã:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
Ví dụ:
Mã:
Sub update()
    Dim cnn As Object: Dim rst As Object
    Dim lsSQL As String
    Dim linkdb  As String
 
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    linkdb = ThisWorkbook.Path & Application.PathSeparator & "tuhocvba.mdb"
    
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0" 'Access truoc day la: "Microsoft Jet 4.0 OLE DB Provider"
        .ConnectionString = linkdb
'        .Properties("Jet OLEDB:Database Password") = ""
        .Open
    End With
    
    lsSQL = "UPDATE DATA SET maso='chungtoila tuhocvba.net',Name ='VBA'" & _
            " WHERE SOCT ='A006'"
            
    rst.Open lsSQL, cnn
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Kết quả được thể hiện như sau @Snow24 :
Bạn cần đăng nhập để thấy hình ảnh
 

Euler

Administrator
Thành viên BQT
DELETE: Xóa một bản ghi
Cú pháp:
Mã:
DELETE FROM table_name WHERE condition
Ví dụ:
Mã:
lsSQL = "DELETE FROM DATA WHERE tb_maso=7"
Chú ý: Khi xây dựng cơ sở dữ liệu phải đảm bảo có một trường (cột) thông tin mang tính duy nhất, ta thường có cột mã số, định dạng là số.
Chú ý, với text thì ta phải có nháy đơn tên_cột='string', còn với số ta viết trực tiếp tên_cột=số
Kết quả:
Bạn cần đăng nhập để thấy hình ảnh
 
Sửa lần cuối:
Top