1. ADO là gì?

ADO viết tắt của cụm từ ActiveX Data Objects, là cầu nối giữa các nhà cung cấp dữ liệu và người sử dụng từ các nguồn dữ liệu được tạo ra bằng “Microsoft ActiveX Data Objects”.

2. Cơ sở dữ liệu là gì?

Cơ sở dữ liệu là một tập hợp thông tin được tổ chức theo cách mà một chương trình máy tính có thể dễ dàng hiểu và đọc dữ liệu. Và hệ thống quản lý cơ sở dữ liệu được thiết kế để hiểu và tương tác với các ứng dụng máy tính khác để thực hiện các hoạt động khác nhau trên dữ liệu. MySQL, Microsoft SQL Server, Microsoft Access, Oracle, IBM DB2, PostgreSQL là một số hệ thống quản lý cơ sở dữ liệu được nhiều người biết đến.

Nói chung là thông tin được lưu trữ trong dữ liệu ở dạng bảng (Table) và bảng được thiết kế với tập hợp các bản ghi (Row - hàng) và trường (Column – Cột)

Bạn có thể sử dụng Microsoft Excel để lưu trữ dữ liệu. Trong đó File Excel sẽ hoạt động như 1 nguồn dữ liệu, mỗi trang tính sẽ là một bảng dữ liệu và các hàng, cột của trang tính sẽ là các bản ghi và các trường của bảng.

3. SQL là gì?

SQL viết tắt của cụm từ Structured Query Language, tạm dịch là Ngôn ngữ truy vấn có cấu trúc. ADO sử dụng các lệnh SQL để giao tiếp với cơ sở dữ liệu. Sau đây là các lệnh SQL được sử dụng phổ biến nhất để xử lý cơ sở dữ liệu.

Lệnh SELECT được sử dụng để truy xuất dữ liệu từ một nguồn dữ liệu.

Lệnh INSERT dược sử dụng để chèn các bản ghi vào nguồn dữ liệu.

Lệnh UPDATE được sử dụng để sửa đổi các bản ghi hiện có của nguồn dữ liệu.

Tham chiếu VBA ADODB.Connection.

Tham chiếu ADODB.Connection trong VBA giúp tham chiếu ADO trong Excel VBA. Chúng ta có thể sử dụng ADO trong Excel VBA để kết nối cơ sở dữ liệu và thực hiện các thao tác xử lý dữ liệu. Chúng ta sẽ cần thêm thư viện “Microsoft ActiveX Data Objects Library” trong Mục References để tham chiếu ADO trong VBA. Đây là ảnh chụp màn hình tham chiếu VBA ADODB.Connection.

ADO trong Excel VBA – Ví dụ thực hành: Sử dụng ADO và SQL với VBA để truy xuất dữ liệu từ bất kỳ nguồn dữ liệu nào vào Excel bằng ADO.

1. Bạn phải mở kết nối đến Data Source.

2. Bạn cần chạy lệnh SQL được yêu cầu.

3. Bạn phải sao chép tập hợp bản ghi kết quả vào trang tính và nơi mà bạn muốn.

4. Bạn cần phải đóng Record Set và Connection lại.

Chúng ta sẽ coi File Excel là nguồn dữ liệu và ta sẽ kết nối với trang tính (Bảng) để lấy dữ liệu. Trong ví dụ này chúng ta sẽ lấy dữ liệu từ Data và đưa sang Report bằng cách sử dụng ADO.

Trên đây là dữ liệu mẫu đã được mình tạo sẵn để mọi người thực hành.

Bước 1: Tools > References > Microsoft ActiveX Data Objects 2.8 Library (Bạn có thể chọn các phiên bản thấp hơn như 2.5 cũng được)

Để làm được việc này thì bạn cần phải vào VBE (Visual Basic Editor) bằng phím tắt Alt + F11. Chọn tới mục Tools > References…

Sau đó bạn hãy chọn tới mục Microsoft ActiveX Data Objects 2.8 Library.

Sau đó bạn hãy tạo ra 1 Module mới với tên như sau.

Bước 2: Tạo ra Connection String với Provider và Data Source options.

Để tham khảo các loại Connection String thì bạn có thể vào đường dẫn dưới đây.

https://www.connectionstrings.com/

Sau đó bạn hãy chọn loại cơ sở dữ liệu mà bạn muốn connect tới.

Ví dụ ở đây ta sẽ chọn tới Excel vì Database gốc của chúng ta là File Excel. Ngoài ra ở đây bạn cũng có thể connect tới các Database được nhiều người sử dụng như SQL Server, MySQL, PostgreSQL, …

Trong đây để connect tới mọi loại File Excel thì bạn có thể chọn xlsm Files và sử dụng Connection String như dưới đây.

Ở đây bạn cần lưu ý là HDR viết tắt của Header. Nếu bạn để HDR = Yes thì bạn sẽ coi dòng đầu tiên sẽ là dòng chứa các tên cột chứ không phải là dòng dữ liệu.

Giờ ta sẽ bắt đầu viết code dựa vào Connection String chúng ta đã tra cứu ở trên.

Bước 3: Kết nối với nguồn dữ liệu (Data Source)

Bước 4: Tạo ra câu lệnh SQL dạng chuỗi

Bước 5: Lấy ra các bản ghi bằng cách chạy câu lệnh truy vấn tới nguồn dữ liệu đã được kết nối.

Bước 6: Bạn nên xoá đi dữ liệu của Sheet báo cáo trước khi kết xuất ra dữ liệu mới vừa truy xuất được.

Ở đây mình sẽ xoá đi tất cả dữ liệu của Sheet Report. Trong thực tế nếu bạn muốn giới hạn lại vùng muốn xoá thì bạn cần tự sửa lại câu lệnh này.

Bước 7: Lấy ra tên các cột trong câu lệnh truy vấn của chúng ta thông qua vòng lặp For Each Next đơn giản như sau.

Bước 8: Giờ ta sẽ cần Copy dữ liệu trong RecordSet (Tập hợp các kết quả đã truy vấn được bằng câu lệnh SQL Command ở trên) ra ô mà chúng ta muốn (Thường là ô ngay dưới phần các cột tiêu đề mà bạn vừa điền)

Bước 9: Đóng tập bản ghi RecordSet và kết nối đến cơ sở dữ liệu

Bước 10: Bước này là tuỳ bạn. Vì ở đây mình chỉ tiến hành AutoFit lại các cột kết quả cho đẹp mà thôi.

Tổng kết lại chương trình của chúng ta sẽ như sau.

Trong ví dụ này chúng ta đang kết nối tới File hiện tại nên Source ta sẽ đặt là:

Source = ThisWorkbook.FullName

Trong trường hợp bạn muốn kết nối tới các File khác thì bạn chỉ cần đặt lại Source = đúng đường dẫn tới File đó.

Ví dụ: Source = “D:\Data\Data.xlsx” chẳng hạn

Bạn có thể download File gốc của bài viết tại đường dẫn dưới đây.