Bài 7: Lập trình VBA

* Hệ quản trị Cơ sở dữ liệu MS Access KHOA CÔNG NGHỆ THÔNG TIN Bài 7: Lập trình VBA * * NỘI DUNG TRÌNH BÀY Lập trình VBA căn bản 1.1 Lập trình Module 1.2 Các thành phần cơ bản của lập trình VBA 1.3 Các lệnh điều khiển cơ bản Lớp đối tượng truy cập dữ liệu DAO 1.1 Tổng quan DAO 1.2 Lớp các đối tượng truy nhập dữ liệu * * NỘI DUNG TRÌNH BÀY Lập trình VBA căn bản Lớp đối tượng truy cập dữ liệu DAO * 1. LẬP TRÌNH VBA CĂN BẢN Lập trình Module Cấu trúc của một module: Các khai báo Khai báo biến, hằng, mảng, chương trình con Các câu lệnh Các câu lệnh đơn Các câu lệnh lặp Các câu lệnh điều khiển Các thông báo Kết thúc * 1. LẬP TRÌNH VBA CĂN BẢN Lập trình Module Khởi động: C1: trong cửa sổ DATABASE/ Module/NEW C2: Mở cơ sở dữ liệu/ Chọn menu Insert/Module Giao diện lập trình VBA hiển thị như sau: * 1. LẬP TRÌNH VBA CĂN BẢN Lập trình Module Khởi động: * 1. LẬP TRÌNH VBA CĂN BẢN Lập trình Module Ví dụ: chương trình tính tổng hai số a, b nhập từ bàn phím * 1. LẬP TRÌNH VBA CĂN BẢN Các thành phần cơ bản của lập trình VBA Bộ kí tự Sử dụng 26 chữ Latin A. . Z, a . . z. Ký tự gạch nối, bộ chữ thập phân, các kí hiệu toán học: + , - , * , / . . . Từ khoá (key word) Các từ riêng của Visual Basic, được định nghĩa sẵn.Ta không thể dùng vào việc khác. Ví dụ: Dim, Sub, If . . Then . . Else, Case, Do While . . Loop . . . Tên chuẩn (Standard Identifies) Các tên chuẩn là các tên đã được định nghĩa, chúng ta có thể định nghĩa lại để dùng vào việc khác nhưng không nên. Ví dụ: Single, Doulbe, True, False . . . * 1. LẬP TRÌNH VBA CĂN BẢN Các thành phần cơ bản của lập trình VBA Khai báo các đối tượng trong chương trình: Chúng ta muốn dùng các biến, hằng, mảng…trong chương trinh . Chúng ta phải khai báo trước khi dùng. Tên biến là một chuỗi văn bản liền nhau, bắt đầu là một kí tự chữ cái theo sau có thể là kí tự số hoặc dấu gạch ngang hoặc các kí tự chữ cái khác. Ví dụ : các tên đúng: A1, Van_Ban, X1 . . . các tên sai: 1_a, Tinh Tong, Giai(x), Dim, while. . . Chú ý:khi đặt tên biến ta phải đặt sao cho càng ngắn gọn càng tốt . * 1. LẬP TRÌNH VBA CĂN BẢN Các thành phần cơ bản của lập trình VBA Hằng (Const) Hằng là một hằng số có giá trị không thay đổi trong khi chương trình thực hiện Khai báo hằng: Const Ten_hang = Gia_Tri_Hang Ví dụ: Const Pi = 3.14 Const Fi = 1 Biến (Variable) Là tên có giá trị thay đổi trong khi chương trình thực hiện Khai báo biến Dim Ten_Bien As Ten_kieu_bien Ví dụ: Dim Tong As Double Dim Text As String * 1. LẬP TRÌNH VBA CĂN BẢN Các thành phần cơ bản của lập trình VBA Các kiểu dữ liệu * 1. LẬP TRÌNH VBA CĂN BẢN Các thành phần cơ bản của lập trình VBA Các kiểu dữ liệu (tiếp) * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Câu lệnh if Dạng khuyết Cú pháp: If dk Then lệnh End If Hoạt động : Kiểm tra dk Nếu dk đúng thì thực hiện lệnh Nếu dk sai thì bỏ qua lệnh làm các câu lệnh tiếp theo * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Câu lệnh if Dạng đầy đủ Hoạt động : Kiểm tra dk Nếu dk đúng thì thực hiện câu lệnh A Nếu dk sai thì thực hiện câu lệnh B Kết thúc lệnh if và thực hiện các lệnh tiếp theo trong chương trình Chú ý: dk thường là một biểu thức logic Lệnh có thể là lệnh đơn và cùng có thể là một loạt các câu lệnh Cú pháp: If dk then lệnh A Else lệnh B End If * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Câu lệnh Select Case Cú pháp: Select Case biến_chọn Case t = 1 Câu lệnh 1 . . . Case t = n Câu lệnh n Case else Câu lệnh n+1 End Select Hoạt động Tính giá trị biến chọn So sánh biến chọn với các lựa chọn Nếu biến chọn bằng một giá trị nào từ 1 . . N thì thực hiện câu lệnh ứng với giá trị đó Nếu biến chọn không bằng giá trị nào thì thực hiện câu lệnh n+1 Kết thúc * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Các câu lệnh lặp Lặp với số lần biết trước Câu lệnh For Cú pháp: For bien =giá_trị_đầu To giá_trị_cuối Step n Các câu lệnh Next bien Trong đó: bien, giá_trị_đầu, giá_trị_cuối phải là các giá trị có nguyên. Giá_trị_đầu, giá_trị_cuối có thể là các giá trị xác định, có thể là biểu thức * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Các câu lệnh lặp Lặp với số lần biết trước Hoạt động B1:máy tính giá trị đầu và giá trị cuối B2:Gán giá trị đầu cho Bien B3:So sánh Bien với giá trị cuối Nếu Bien  Các câu lệnhLoop Hoạt động: B1: - Đầu tiên là kiểm tra biểu thức điều kiện B2: - Nếu biểu thức điều kiện đúng thì thực hiện các câu lệnh. Thay đổi biến điều khiển. Quay lại B1 - Nếu biểu thức điều kiện sai thì thoát khỏi vòng lặp * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Các câu lệnh lặp Lặp với số lần không xác định trước Do Until . . .Loop Cú pháp: Do Until  Các câu lệnh Loop Hoạt động: B1: -Kiểm tra Bt Logic B2: - Nếu biểu thức sai thì thực hiện các câu lệnh. Thay đổi biến điều khiển. Quay lại B1 Nếu Bt Logic đúng thì thoát vòng lặp * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Các câu lệnh lặp Lặp với số lần không xác định trước Do . . . Loop While Cú pháp: Do Các câu lệnh Loop While Hoạt động: B1: Thực hiện các câu lệnh. Thay đổi giá trị biến điều khiển. B2: Kiểm tra biểu thức Logic Nếu biểu thức đúng quay lại B1 Nếu biểu thức sai thoát lặp * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Các câu lệnh lặp Lặp với số lần không xác định trước Do . . . Loop Until Cú pháp: Do Các câu lệnh Loop Until Hoạt động B1: Thực hiện các câu lệnh. Thay đổi biến điều khiển B2: Kiểm tra Bt Logic Nếu biểu thức Logic sai quay lại B1 Nếu Bt Logic đúng Thoát lặp * 1. LẬP TRÌNH VBA CĂN BẢN Các lệnh điều khiển cơ bản Các câu lệnh lặp Lặp với số lần không xác định trước While . . . Wend Cú pháp: While Các câu lệnh Wend Hoạt động: B1: Kiểm tra BtLogic B2: Nếu BtLogic có giá trị đúng thì thực hiện các câu lệnh. Thay đổi biến điều khiển Nếu BtLogic có giá trị sai thì thoát lặp * * NỘI DUNG TRÌNH BÀY Lập trình VBA căn bản Lớp đối tượng truy cập dữ liệu DAO * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU DAO (Data Access Objects – Các đối tượng truy xuất dữ liệu) Là tập hợp bao gồm lớp các đối tượng có thể dùng để lập trình truy cập và xử lý dữ liệu trong các hệ CSDL. DAO được phát triển khá sớm, gần đây nhất là phiên bản DAO 3.5 và 3.51- nó có thể thực hiện tốt được trên các phiên bản Access từ 97 trở về trước. Access 2000, XP dùng phiên bản DAO 3.6 sử dụng nền Microsoft Jet 4.0 * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU DAO Nạp thư viện DAO3.6 vào làm việc: Bước 1: Mở cửa sổ lập trình VBA; Bước 2: Chọn thực đơn Tools | References .. Hộp thoại sau xuất hiện: * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU DAO Cách thức làm việc của các ứng dụng CSDL * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU DAO Lớp các đối tượng Workspaces – định nghĩa tập hợp các vùng làm việc. Databases - định nghĩa tập hợp các CSDL Access cần làm việc trên một dự án; RecordSets- định nghĩa các tập hợp bản ghi (Records) cần làm việc; QueryDefs - định nghĩa tập hợp các Query để làm việc. TableDefs - định nghĩa tập hợp các bảng (Table) cần làm việc. Relations - định nghĩa tập hợp các quan hệ (Relationship) cần làm việc; * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Database Database là đối tượng dùng làm việc với một CSDL (trong trường hợp này có thể hiểu một CSDL như một tệp Access .MDB). Khai báo * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Recordset là đối tượng dùng để miêu tả tập hợp các bản ghi của một bảng, của một query hoặc tập các bản ghi kết quả của việc thi hành một câu lệnh SQL nào đó. Khai báo Set rs = db.OpenRecordset là lệnh để tạo ra tập hợp các bản ghi từ CSDL db gán vào biến kiểu recordset rs; là một xâu ký tự chỉ ra nguồn dữ liệu sẽ trả về cho Recordset. Xâu này có thể là tên một bảng, một Query hoặc một câu lệnh SQL; * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Ví dụ 1: Gán tập hợp các bản ghi từ một bảng vào biến Recordset (ở đây là bảng canbo). Ví dụ 2: Gán tập hợp các bản ghi từ một câu lệnh chọn dữ liệu SQL vào biến Recordset (ở đây là các thông tin hoten, ngaysinh của tất cả các cán bộ nữ từ bảng canbo). * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số thuộc tính của Recordset Thuộc tính Name Trả về xâu ký tự trong tham số của lệnh gọi Recordset. Ví dụ: lệnh sau sẽ cho biết xâu ký tự tạo nguồn dữ liệu cho Recordset là gì? Thuộc tính RecordCount Cho biết tổng số bản ghi trả về trên Recordset Thuộc tính EOF Cho biết con trỏ bản ghi hiện tại có nằm ở EOF hay không? Nếu có giá trị thuộc tính này là True, trái lại là False. * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số thuộc tính của Recordset Thuộc tính Fields Dùng tham chiếu tới các trường (Fields) trên tập hợp các bản ghi mà Recordset trả về.. Với Field của Recordset thông thường người ta hay sử dụng thuộc tính Value. Nếu không chỉ định thuộc tính cụ thể nào cho Field, VBA vẫn hiểu ngầm định đó là Value. Ví dụ: Hiển thị giá trị trường hoten trong Recordset rs * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số thuộc tính của Recordset Thuộc tính AbsolutePosition Cho biết vị trí bản ghi hiện tại (được tính từ 0). Trong trường hợp không có bản ghi nào trên recordset hoặc con trỏ bản ghi đang nằm ở EOF- sẽ không thể lấy được giá trị thuộc tính này. Do vậy để sử dụng thuộc tính này thường phải đi kèm thuộc tính kiểm tra có tồn tại bản ghi nào hay không (RecordCount > 0) và con trỏ bản ghi có ở cuối tệp chưa (EOF = False). * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số phương thức của Recordset Phương thức Close Để đóng Recordset, giải phóng bộ nhớ. Chỉ thực hiện hành động này khi không làm việc với Recordset nào đó. Các phương thức di chuyển bản ghi của Recorset Phương thức MoveFirst Để chuyển con trỏ về bản ghi đầu tiên Phương thức MoveLast Để di chuyển con trỏ về bản ghi cuối cùng Phương thức MoveNext Dịch đến bản ghi kề sau Phương thức MovePrevious Dịch đến bản ghi kề trước * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số phương thức của Recordset Ví dụ duyệt và hiển thị toàn bộ Hoten của bảng canbo * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số phương thức của Recordset Phương thức AddNew, Update Qui trình thêm một bản ghi mới như sau: 1. Ra lệnh Addnew 2. Gán giá trị cho các trường của bản ghi mới 3. Ra lệnh Update * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Ví dụ thêm mới một hồ sơ cán bộ mới vào bảng canbo * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số phương thức của Recordset Phương thức Edit, Update Phương thức Edit để sửa dữ liệu một bản ghi nào đó trên Recordset. Qui trình để sửa một bản ghi như sau: 1. Định vị tới bản ghi cần sửa trên recordset 2. Ra lệnh Edit 3. Gán giá trị mới cho các trường cần sửa 4. Ra lệnh Update * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb '------------------------------------- 'Định vị tới bản ghi cần sửa' Set rs = db.OpenRecordset("SELECT * FROM canbo WHERE canboID='CB000565'") If rs.RecordCount > 0 Then rs.MoveFirst '--------------------------------- 'Ra lệnh sửa bản ghi ' rs.Edit '--------------------------------- 'Thực hiện sửa các trường dữ liệu cần thiết ' rs.Fields("hoten") = "Nguyen Van Hai" rs.Fields("ngaysinh") = #22/11/1975# '----------------------- 'Ra lệnh ghi lại dữ liệu vừa sửa ' rs.Update End If Ví dụ: về sửa hồ sơ cán bộ có mã CB000565 * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Recordset Một số phương thức của Recordset Phương thức Delete Để xoá bản ghi hiện tại ra khỏi Recordset. Khi đó bản ghi hiện tại sẽ bị xoá bỏ khỏi CSDL. Xoá bản ghi của một mẫu nhập liệu (nhập vào biến Recordset rs) như sau: * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng QueryDeft Querydef dùng để tham chiếu tới các Query có sẵn (Buil-in) trên CSDL Access, hoặc cũng có thể lập trình tạo các Query từ các câu lệnh SQL. Khởi tạo và kích hoạt một query trên VBA: * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng QueryDeft Một số ví dụ về sử dụng biến QueryDef để tạo ra một số loại query xử lý dữ liệu trong môi trường VBA. Ví dụ 1: Tạo DELETE query để xoá danh sách những cán bộ có tuổi lớn hơn 60 ra khỏi bảng canbo (cán bộ đã nghỉ hưu) * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng QueryDeft Ví dụ 2: Giả sử đã thêm một trường mới trên bảng cán bộ có tên luongchinh.Tạo UPDATE query để tính giá trị cho trường này = hesoluong * 290000. * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng TableDef TableDef được dùng để tham chiếu tới các bảng dữ liệu (Table) trên CSDL. Thông qua đối tượng này có thể thiết kế, chỉnh sửa được cấu trúc các bảng dữ liệu trong chế độ Run-time của VBA như trên chế độ thiết kế bảng Design View. Một số thuộc tính quan trọng của TableDef Thuộc tính Name Cho biết tên bảng được gán vào biến kiểu TableDef Thuộc tính RecordCount Cho biết tổng số bản ghi hiện có trên bảng được gán bởi biến TableDef Thuộc tính DateCreated Cho biết thời gian tạo ra bảng được gán vào biển kiểu TableDef Thuộc tính Fields Để tham chiếu tới các trường của bảng. * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng TableDef Một số thuộc tính quan trọng của TableDef Thủ tục hiển thị tên của tất các các trường trong một bảng nào đó (ngầm định trên một CSDL đã được khai báo và gán biến db - kiểu Database). * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng TableDef Một số phương thức của TableDef Phương thức CreateTableDef Để tạo ra một bảng mới bằng VBA. Cú pháp tạo bảng mới như sau: Trong đó: db – là biến kiểu Database đã được gán bởi CSDL cần làm việc (bảng mới sẽ được tạo ra trên CSDL này); là tên bảng cần tạo. Lệnh db.TableDefs.Append tbl là lệnh ghi cấu trúc bảng đang khai báo lên CSDL đã chỉ định. * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng TableDef Một số phương thức của TableDef Phương thức CreateField Để tạo ra các trường cho một bảng kiểu TableDef nào đó. Để thêm một trường mới lên bảng, sử dụng cú pháp sau: Trong đó: - tên trường mới cần tạo; - là một tuỳ chọn để khai báo kiểu dữ liệu của trường cần tạo. là một tuỳ chọn để khai báo độ lớn dữ liệu nếu cần. * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Ví dụ: Tạo cấu trúc một bảng với phương thức CreateField * 2. LỚP ĐỐI TƯỢNG TRUY NHẬP DỮ LIỆU Đối tượng Relation Relation dùng để tạo kết nối (relationship) giữa 2 bảng trong CSDL Access. Ví dụ tạo kết nối giữa 2 bảng hoadon và khach trong CSDL Quản lý bán hàng. * Hệ quản trị Cơ sở dữ liệu MS Access CÂU HỎI?