Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,
Xin trao đổi thêm nội dung còn thiếu về file ứng dụng minh họa được cập nhật hôm nay (16/7/2012):

1. Trên form chính "frmCtuNX":
+ Để nạp lại nội dung các chứng từ đã lưu trước đây, tại ô nhập số chứng từ xin bấm 1 vài ký tự số để lọc nhanh và cho sổ danh sách chứng từ xuống (với các chứng từ do tôi nhập đều có số 3 trong chuỗi số chứng từ, nên các Bạn nhập số 3), sau đó chọn số chứng từ xác định từ danh sách sổ xuống, chương trình sẽ cho nạp nội dung của chứng từ đó lên Form.

+ Để chọn khách hàng có sẵn từ danh sách: tại ô nhập khách hàng, cũng thao tác tương tự như trên, nghĩa là nhập vào 1 vài từ cần tìm rồi cho sổ danh sách xuống (thí dụ như nhập từ "Công ty"), sau đó chọn khách hàng thích hợp. Danh sách này truy xuất từ bảng dữ liệu lưu Danh bạ (tblDanhsach) ta đã xem xét trong các bài trước có sẵn trên 15.000 mẫu tin.

2. Để xóa trống các ô nhập chi tiết hàng phát sinh trong chứng từ: kích kép tại ô nhập mã số hàng hóa.
Khi chọn hoặc nhập mới số chứng từ, các ô này cũng sẽ tự động được xóa trống.

3. Với SubForm "frmCtuNXCT": xin các Bạn chú ý các thuộc tính được khai báo trong ảnh đính kèm.
Trong các thuộc tính này, các Bạn chú ý thuộc tính "Recordset-Type" đã được khai báo là kiểu "Snapshot".
Với kiểu Snapshot, Recordset sẽ được đặt ở chế độ chỉ xem, không hiệu chỉnh, không thêm, không xóa được. Access sẽ dành ít tài nguyên nhất để nạp Recordset kiểu "Snapshot"

Các Bạn có thể tham khảo các hướng dẫn của Microsoft về Recordset-Type của 1 Access Form tại link sau:
RecordsetType Property - Access - Office.com
Và các khuyến cáo nhằm tăng khả năng truy xuất dữ liệu SQL Server của ứng dụng Access từ link sau:
Optimizing Microsoft Office Access Applications Linked to SQL Server
 

Đính kèm

Sửa lần cuối:

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,

Có một Bạn đã phát hiện lỗi không cập nhật được chứng từ mới phát sinh.
Tôi đã kiểm tra và phát hiện lỗi ở thủ tục sau, nằm bên trong Class module của Form "frmCtuNX":
Mã:
Sub SaveToInvoiceFromForm(Optional InvoiceId)
    'Luu thong tin tren form vao tblctunx
    
    'UpdateOrInsert:
    '+ True: Luu thong tin thay doi vao mau tin dang hien huu
    '+ Flase: Them mau tin moi
    
    'InvoiceId: so chung tu
    '
    On Error GoTo HandleError
    
    Dim SQLst As String, tblName As String
    Dim vId
    
    Call OpenMyConnection
    
    tblName = "tblctunx"
    
    With Me
        vId = Me.txtId
        If Not IsNull(vId) Then
            If IsNull(InvoiceId) Then Exit Sub
            SQLst = "UPDATE " & GetSchemaTable(tblName) & "." & tblName & " SET "
            SQLst = SQLst & " soctu ='" & .cmbSoCtu & "',"
            SQLst = SQLst & " ngay ='" & Format$(.txtNgay, "dd-mmm-yy") & "',"
            SQLst = SQLst & " msnv ='" & .cmbNghiepvu & "',"

            '[COLOR="green"]SQLst = SQLst & " mskh ='" & .cmbKhachhang & "',"[/COLOR] 'Đây là dòng sai, vì mskh có kiểu numeric nhưng ở đây có 2 dấu nháy ở 2 đầu nên thành kiểu Text

            [COLOR="red"]SQLst = SQLst & " mskh =" & .cmbKhachhang & ","[/COLOR] 'Đây là dòng đã được hiệu chỉnh cho đúng, bỏ dấu nháy ở 2 đầu

            [COLOR="blue"]SQLst = SQLst & " nguoigiaodich =N'" & .txtNguoiGiaodich & "',"[/COLOR] 'Và sẵn tiện sửa luôn dòng này để lưu được chuỗi Unicode

            SQLst = SQLst & " tsuatvat =" & .txtTsuat
            SQLst = SQLst & " WHERE ("
            SQLst = SQLst & " soctu='" & InvoiceId & "'"
            SQLst = SQLst & ")"
        Else
            If IsNull(.cmbSoCtu) Then Exit Sub
            SQLst = "INSERT INTO " & GetSchemaTable(tblName) & "." & tblName
            SQLst = SQLst & "(soctu, ngay, msnv, mskh, nguoigiaodich, tsuatvat)"
            SQLst = SQLst & " VALUES ("
            SQLst = SQLst & " '" & .cmbSoCtu & "',"
            SQLst = SQLst & " '" & Format$(.txtNgay, "dd-mmm-yy") & "',"
            SQLst = SQLst & " '" & .cmbNghiepvu & "',"

[COLOR="red"]            SQLst = SQLst & " " & .cmbKhachhang & ","[/COLOR]

[COLOR="blue"]            SQLst = SQLst & " N'" & .txtNguoiGiaodich & "',"[/COLOR]

            SQLst = SQLst & " " & .txtTsuat
            SQLst = SQLst & ")"
        End If
    End With
    
    MyConn.Execute SQLst
    
    Call CloseMyConnection
    '
    LoadInvoiceInfoToForm Me.cmbSoCtu
    
HandleError:
        If Err > 0 Then
            GeneralErrorHandler Err.Number, Err.Description, NhapXuat_FORM, "SaveToInvoiceFromForm"
            Exit Sub
        End If
End Sub
Xin cảm ơn các Bạn đã quan tâm.
Có Bạn nào thấy sai ở chỗ nào nữa không?

---------- Post added at 10:01 ---------- Previous post was at 09:36 ----------

Và lỗi ở thủ tục sau đây, cũng ở trong Class module của form "frmCtuNX":
Mã:
Private Sub SetComboRowSource(ComboName As String, RecSourceSt As String, stFilter As String)
    'Nap RowSource cho ComboBox
    
    Dim SQLst As String
    Dim SourceRec As ADODB.Recordset
    
    SQLst = RecSourceSt & " WHERE " & stFilter 'ten LIKE N'%" & stFilter & "%'"
    Set SourceRec = ProcessRecordset(SQLst)
[COLOR="red"]   'thêm 3 dòng kế bên dưới. Tôi viết kiểu With ... End With để phòng khi phải khai báo thêm gì nữa cho ComboBox    [/COLOR]
[COLOR="green"]    With Me(ComboName)
        .RowSourceType = "Table/Query"
    End With
[/COLOR]    
    Set Me(ComboName).Recordset = SourceRec
    
    SourceRec.Close
    Set SourceRec = Nothing

End Sub
 

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,

Tối hôm qua có Bạn hỏi qua email:

Vì sao trong thủ tục "SetSourceRecForSubForm" (module modQuanlyDulieu) để gán Recordset cho SubForm tôi lại dùng câu lệnh:

Mã:
Set mForm(sForm).Form.Recordset = SQLrec
mà không phải là:

Mã:
mForm(sForm).Form.Recordset = SQLrec
Câu trả lời thật ngắn gọn là:
Theo quy ước của VBA:
+ Recordset là 1 Object (các Bạn sử dụng thư viện ADO hay DAO cũng đều như vậy cả)
+ Trong thủ tục nêu trên SQLrec là 1 Recordset
+ Câu lệnh gán giá trị cho 1 biến Object phải tuân theo cú pháp: SET <Biến Object hoặc Property của Object> = Giá trị là 1 Object
 

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,
Có một số Bạn gọi điện hỏi tôi vì sao truy xuất chậm quá, không giống như lần đầu sử dụng file minh họa?
Tôi đã kiểm tra lại và thấy tốc độ truy xuất vẫn như trước. Tôi đã cho nạp thử tiện ích VPN ảo thì thấy ứng dụng chạy chậm hẳn, lý do ở đây là khi nạp tiện ích này (và các tiện ích tương tự) máy tính của Bạn thay vì truy xuất trực tiếp đến host đang lưu file dữ liệu cần truy xuất, thì lại đi vòng qua 1 hoặc nhiều host khác nữa, nên bị chậm hẳn. Trong trường hợp này, các Bạn chỉ cần tắt hoặc DisConnect đến VPN ảo đi là nhanh trở lại.

---------- Post added at 06:15 ---------- Previous post was at 06:00 ----------

Chào các Bạn,

Có Bạn bảo tôi: đã lỡ làm được tới đó rồi sao không tiện thể cho tự động đề nghị đơn giá mỗi khi chọn 1 mặt hàng hoặc chọn lại đơn vị tính?
Thấy nhu cầu này cũng cần để thêm phần sâu sắc cho vấn đề được minh họa nên tôi đã bổ sung nhu cầu trên vào file ứng dụng được cập nhật lúc 13 giờ trưa nay. Bạn nào có nhu cầu xin tải xuống từ link sau:
qldanhba_170712.zip

Nội dung bổ sung được tôi sử dụng 1 thủ tục tự tạo thay thế cho hàm Dlookup của VBA, thủ tục này có tên là fLookup nằm trong module "modUtilities".
Nội dung thủ tục này như sau:
Mã:
Function fLookup(WhatField As String, WhatTable As String, CriSt As String)
    On Error GoTo xulynull
    Dim SrcRec As ADODB.Recordset
    Dim srcSt As String

    If Len(CriSt) = 0 Then Exit Function

    srcSt = "SELECT TOP 1 " & WhatField & " FROM " & GetSchemaTable(WhatTable) & "." & WhatTable
    srcSt = srcSt & " WHERE " & CriSt
    Set SrcRec = ProcessRecordset(srcSt)
    
    If SrcRec.RecordCount > 0 Then fLookup = Trim(SrcRec(WhatField))
    
    SrcRec.Close
    Set SrcRec = Nothing
    
    Exit Function
    
xulynull:
    If Err > 0 Then fLookup = Null
    Exit Function
End Function
 

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,

Để giúp các Bạn có căn cứ đánh giá và tối ưu hoá hiệu quả truy xuất dữ liệu của các thủ tục đang có trong file ứng dụng minh hoạ và các thủ tục do chính các Bạn viết hoặc hiệu chỉnh, tôi đã cho nạp vào file dữ liệu trên SQL SERVER:
+ Trên 12.000 chứng từ phát sinh (trong bảng "tblctunx")
+ Với trên 48.000 chi tiết hàng hoá phát sinh (trong bảng "tblctunxct")

Rất mong các Bạn cùng tham gia trao đổi để chúng ta cùng làm sáng tỏ những vấn đề đang thảo luận trong chuyên đề này.
 

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,

Theo dõi thấy có nhiều Bạn đọc chuyên đề này, nhưng sao không thấy ý kiến gì trao đổi thêm, làm tôi thấy băn khoăn. Không biết những gì tôi trao đổi có mang đến cho các Bạn điều gì ích lợi không? Có gì chưa đúng hay sai chăng?

Thật tình, tôi cũng chỉ muốn chứng minh rằng Microsoft Access giúp ta được rất nhiều việc, trong đó có những việc mà bấy lâu nay chúng ta tưởng, và cũng có rất nhiều người chê Access cũng tưởng lầm rằng Access chỉ làm được ba cái ứng dụng "lẹt đẹt" mang tính "local" thôi, chứ đụng tới NET là chào thua.

Rất mong các Bạn cùng tham gia trao đổi.
 

tuannhcs

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào bạn lehongduc
Mình cũng làm ms access project kết nối sql server trong môi trường nhiều người dùng
Và vẫn đang vướng khâu nhiều nhiều người cùng truy cập vào 1 table
Khi nhiều người cùng tạo báo cáo và đẩy kết quả vào 1 table để đưa dữ liệu vào báo cáo dẫn tới việc đụng độ
Ví dụ:Mình tạo báo cáo tồn kho
User 1 thực hiện
Delete from TB_KHO
Insert into TB_Kho (...)
docmd.openreport "rpKho"
User 2 thực hiện
Delete from TB_KHO
Insert into TB_Kho (...)
docmd.openreport "rpKho"
..........
Trong TB_KHO mình đã có thêm cột User1,User2 để phân biệt báo cáo đc tạo bởi user nào
Khi 1 user chạy báo cáo thì kết quả luôn đúng
Khi nhiều user cùng chạy báo cáo kết quả lúc đúng lúc sai
---------
Vậy theo bạn mình phải giải quyết việc đụng độ khi nhiều user dùng chung 1 table như thế nào trong sql server
Rất mong học hỏi thêm access+sql server từ bạn
 

tuannhcs

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Tôi cũng là người gắn bó nhiều với project Access. Thấy chủ đề của lehongduc rất hay nhưng thú thật không tham gia được gì vì cũng đang trong quá trình mày mò, tìm hiểu chuyển đổi sang SQL, chủ yếu là học hỏi kinh nghiệm từ các bạn.

Vấn đề của bạn tuannhcs đưa nêu ra tôi nghỉ bạn nên tạo riêng cho mỗi User một table TB_KHO riêng (TB_KHO1, TB_KHO2,... tương ứng với các user) hi vọng sẽ giải quyết được triệt để.
Hiện tại mình cũng đã giải quyết theo cách của bạn vừa nêu
Nhưng số user ít thì cũng chấp nhận được
Hiện tại mình cũng đang nghĩ cách chỉ dùng 1 table cho nhiều user
Theo bạn có cách nào dùng 1 table cho nhiều user dc không
 

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào bạn lehongduc
Mình cũng làm ms access project kết nối sql server trong môi trường nhiều người dùng
Và vẫn đang vướng khâu nhiều nhiều người cùng truy cập vào 1 table
Khi nhiều người cùng tạo báo cáo và đẩy kết quả vào 1 table để đưa dữ liệu vào báo cáo dẫn tới việc đụng độ
Ví dụ:Mình tạo báo cáo tồn kho
User 1 thực hiện
Delete from TB_KHO
Insert into TB_Kho (...)
docmd.openreport "rpKho"
User 2 thực hiện
Delete from TB_KHO
Insert into TB_Kho (...)
docmd.openreport "rpKho"
..........
Trong TB_KHO mình đã có thêm cột User1,User2 để phân biệt báo cáo đc tạo bởi user nào
Khi 1 user chạy báo cáo thì kết quả luôn đúng
Khi nhiều user cùng chạy báo cáo kết quả lúc đúng lúc sai
---------
Vậy theo bạn mình phải giải quyết việc đụng độ khi nhiều user dùng chung 1 table như thế nào trong sql server
Rất mong học hỏi thêm access+sql server từ bạn
Chào các Bạn,
Các Bạn không phải mất công như vậy, chỉ cần chú ý những nội dung mang tính nguyên tắc sau đây thì sẽ giải quyết được nỗi lo ngay:

1. Việc mở các bảng dữ liệu luôn có nhiều tùy chọn, ta có thể kể ra đây các tùy chọn thông dụng như sau:
- Mở ra chỉ để đọc dữ liệu
- Mở ra không chỉ để đọc mà còn để hiệu chỉnh dữ liệu hoặc ghi thêm, xóa dữ liệu, ...
Và nguyên tắc truy xuất dữ liệu tối ưu là: cần đến đâu thì mở đến đó. Nếu chỉ cần để ghi thêm mẫu tin (record) mới vào bảng dữ liệu thì tại sao ta lại mở hết trọi dữ liệu trong bảng ra? Và cần gì phải mở hết trọi với chế độ sẵn sàng hiệu chỉnh (bao gồm cả: edit, add và delete)?

2. Khi thiết kế Form, lúc ban đầu mới làm quen với Microsoft Access ta hay bị Bác Bill "dụ khị" bằng cách thiết kế Form với kiểu gắn liền với 1 nguồn dữ liệu (là bảng dữ liệu đơn hoặc 1 truy vấn phức tạp hơn) ở chế độ sẵn sàng cho hiệu chỉnh (bao gồm cả: edit, add và delete). Cái này thuật ngữ thiết kế ứng dụng gọi là thiết kế 1 Bound Form. Bác Bill làm vậy là có lý do, vì ở giai đoạn sơ khởi làm quen với Microsoft Access chủ yếu ta làm ra những ứng dụng chỉ để 1 người dùng trên máy đơn, nó đơn giản nên dễ tiếp thu và dễ làm, vậy mới "dụ khị" được chứ.

Thật sự, có tới 2 chế độ thiết kế Form:
- Thiết kế Bound Form như trên đã nói
- Hoặc thiết kế 1 UnBound Form. Với 1 UnBound Form, ta không cần gán 1 nguồn dữ liệu thường trực như với 1 Bound Form, chỉ khi nào cần tác động đến 1 bảng dữ liệu nào đó ta mới cho chạy lệnh tác động tương ứng (thông qua công cụ VBA code hoặc SQL code). Đây chính là kiểu Form mà tôi đã trình bày trong các bài trước đây.

3. Nguyên tắc của việc thiết kế Form trong 1 ứng dụng có nhiều người dùng qua mạng cùng truy xuất 1 nguồn dữ liệu là: Nên thiết kế UnBound Form. Đó chính là bảo đảm an toàn nhất để ta khỏi phải đối đầu với nỗi lo đau cả đầu về xung đột như các Bạn đang lo ở đây. Làm vậy sẽ thêm được cái lợi là ứng dụng chạy nhanh nữa, vì tiêu tốn ít tài nguyên đó mà.

Những điều nêu trên đều đã được tôi trình bày cụ thể trong các bài viết trước đây, các Bạn có thể đọc lại được ngay trên diễn đàn này.
Các Bạn cũng có thể tham khảo thêm lời khuyên của Bác Bill bằng cách dùng Google với từ khóa "UnBound Form"

4. Về vấn đề cụ thể Bạn nêu, tôi sơ bộ có nhận xét và ý kiến thế này:
- Bảng TB_KHO là 1 bảng dữ liệu mang tính chất tạm thời để nhằm mục đích làm nguồn dữ liệu cho 1 Report theo tuỳ chọn riêng của từng User xác định.

Có 2 cách quản lý cái bảng tạm thời này:
+ Có thể cho gắn liền với từng User đang làm việc theo kiểu "xong việc rồi bỏ" (chứ lưu lại làm chi cho nó nặng bụng mà chẳng để làm gì?)
+ Hoặc cho ghi bảng này ngay trên file ứng dụng tại máy tính của User đang làm việc (client), với điều kiện ta không thiết kế 1 Access Project File mà là 1 MDB file. Hoặc lưu trên 1 SQL SERVER cục bộ hay lưu thành file XML tại máy client.

Cách đầu luôn luôn rối và chậm hơn cách 2.
 
Sửa lần cuối:

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,
Xin trao đổi thêm để các Bạn tiện tham khảo file ứng dụng làm mẫu minh hoạ tôi mới cập nhật.

1. Theo đề nghị của nhiều Bạn, tôi đã cho phục hồi file SQL Server database truy xuất được qua internet, đồng thời bổ sung thêm số lượng dữ liệu nhiều hơn trước để các Bạn có thể kiểm tra dễ dàng hơn.
Đồng thời tôi cũng đã chỉnh lý lại file minh hoạ cho dễ hiểu hơn theo yêu cầu của các Bạn. Xin mời đọc lại bài đầu của chuyên đề này để lấy link tải xuống file này.

2. Tôi đã chỉnh lý các form nhập dữ liệu với nội dung như sau:
- Chỉnh lý form frmContacts giúp cho việc tìm kiếm dữ liệu đã có được thuận tiện hơn.
Cách tìm kiếm như sau: khi muốn tìm dữ liệu theo 1 chi tiết thông tin nào đó ta chỉ cần nhập vào 1 vài ký tự có trong dòng thông tin đó (mà không cần nhập toàn bộ dòn thông tin cần tìm) tại ô tương ứng rồi bấm nút lệnh "Tìm kiếm"
Thí dụ: để tìm những khách hàng nào có địa chỉ ở tại "Cam Ranh" (có từ này trong dòng ghi địa chỉ) ta nhập từ "Cam Ranh" vào ô ghi địa chỉ rồi bấm nút lệnh tìm kiếm.
Code phục vụ cho việc tìm kiếm theo kiểu này như sau:
Function BuildSQLWhere(blnPriorWhere As Boolean, strPriorWhere As String, strValue As String, strDbFieldName As String) As String


On Error GoTo HandleError

Dim strWhere As String

If blnPriorWhere Then
'add to the existing where clause
strWhere = strPriorWhere & " AND "
Else
'create the where clause for the first time
strWhere = " WHERE "
End If

If strDbFieldName = "Ngaysinh" Then
strWhere = strWhere & strDbFieldName & " = '" & Format$(strValue, "dd-mmm-yy") & "' "
Else
'build where clause using LIKE so will find both exact
'matches and those that start with value input by user
If strDbFieldName = "Gioitinh" Then
strWhere = strWhere & strDbFieldName & " = " & PadQuotes(strValue) & " "
Else
strWhere = strWhere & strDbFieldName & " LIKE N'%" & PadQuotes(strValue) & "%' "
End If
End If

blnPriorWhere = True

'return where clause
BuildSQLWhere = strWhere

Exit Function


HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_QUANLY, "BuildSQLWhere"
Exit Function


End Function
Các Bạn chú ý đoạn code trên có dòng ghi:
strWhere = strWhere & strDbFieldName & " LIKE N'%" & PadQuotes(strValue) & "%' "
Đây là điều kiện lọc dữ liệu để tìm. Ta chú ý 2 dấu % đặt ở 2 đầu trong dòng trên có ý nghĩa "dữ liệu cần tìm bắt đầu và kết thúc bằng gì cũng được miễn là có sự hiện diện của từ được cung cấp bởi biến strValue là được".
Việc sử dụng ký tự % như vậy có khác với thông thường trong Access là hay dùng dấu ? hay *, đây chính là quy ước của câu lệnh SQL trong SQL Server. Ta phải viết theo đúng quy ước của SQL Server vì ứng dụng này chủ yếu là gửi các câu lệnh SQL truy xuất dữ liệu đến SQL Server.

- Chỉnh lý form nhập chứng từ nhập xuất phát sinh frmCtuNX:
+ Khi cần tìm chứng từ đã nhập theo số chứng từ: tại ô nhập số chứng từ (là kiểu comboBox) ta chỉ cần nhập 1 vài ký tự có trong số chứng từ đã nhập rồi bấm phím F4 sẽ được 1 danh sách sổ xuống liệt kê các số chứng từ có chứa ký tự đã nhập trong đó.
+ Khi cần chọn 1 khách hàng xác định: ta cũng làm tương tự tại ô khách hàng (cũng là 1 comboBox)
Vấn đề cần chú ý ở đây là ta đã cho hạn chế dữ liệu hiển thị trong các comboBox trong giới hạn vừa đủ với nhu cầu.

Còn nữa các Bạn ạ. Xin hẹn bài kế tiếp ta lại tiếp tục với những chú ý khi thiết kế form có subform theo dạng Unbound form thông qua form frmCtuNX vừa nêu ở trên.
 
Sửa lần cuối:

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Xin chào các Bạn,

Trong bài này xin trao đổi với các Bạn về thiết kế Unbound Form có chứa Subform. SubForm là Form nằm bên trong 1 Form khác.
Như tôi đã trao đổi tại bài #18, trái ngược với Bound Form luôn gắn liền với 1 nguồn dữ liệu xác định (được khai báo tại thuộc tính Record Source), Unbound Form là Form không gắn với một nguồn dữ liệu nào cả.
Đó chính là căn nguyên khiến 1 Unbound Form tránh được xung đột dữ liệu trong quá trình có nhiều người cùng truy xuất dư liệu, hoặc tuy chỉ có mỗi mình ên mần công chuyện với dữ liệu đó nhưng hổng dè đã “mở” nó ra mà quên “đóng” nó lại.
Những điều cần chú ý khi thiết kế 1 Unbound Form tôi đã trình bày tại #18, trong bài này chỉ tập trung vào việc thiết kế 1 Unbound Form nhưng lại có SubForm.
Một ví dụ điển hình cho nhu cầu này là thiết kế Form nhập chứng từ nhập xuất (dưới đây gọi là Main Form), với 1 Subform trình bày chi tiết các mặt hàng phát sinh.

1. Việc đầu tiên ta cần làm là làm sao để nạp thông tin của 1 chứng từ xác định xuống ô dữ liệu tương ứng trên Form khi cần (vì Unbound Form không duy trì thường trực 1 nguồn dữ liệu gắn kết với nó mà).
Đây chính là trường hợp ta cần làm việc với thông tin của 1 chứng từ xác định đã lập trước.
Trong file minh hoạ, công việc này được thực hiện thông qua các thủ tục:
+ LoadInvoiceInfoToForm: Nạp thông tin chứng từ lên Form
Sub LoadInvoiceInfoToForm(SoCtuSt, Optional NoSetSourceRecForSubForm)
Dim sqlSt As String, SQLrec As ADODB.Recordset
Dim KHrec As ADODB.Recordset
Dim tblName As String, MSKH, vIdKH As Long
‘Xác định nguồn dữ liệu chứa thông tin của chứng từ cần nạp
tblName = "tblctunx"
If IsNull(SoCtuSt) Then Exit Sub
sqlSt = "SELECT * FROM " & GetSchemaTable(tblName) & "." & tblName
sqlSt = sqlSt & " WHERE soctu ='" & SoCtuSt & "'"

Set SQLrec = ProcessRecordset(sqlSt)
'Nếu chứng từ hiện hữu, cho ghi thông tin chi tiết của chứng từ lên các ô dữ liệu tương ứng của Form
If SQLrec.RecordCount > 0 Then
Set objKhachHang = New clsDanhba
With Me
.txtId = SQLrec!id
.txtNgay = SQLrec!Ngay
.cmbNghiepvu = SQLrec!MSNV
.txtTsuat = SQLrec!tsuatvat
.txtNguoiGiaodich = SQLrec!NguoiGiaodich

MSKH = SQLrec!MSKH

sqlSt = "SELECT * FROM " & GetSchemaTable("tblDanhsach") & ".tblDanhsach"
sqlSt = sqlSt & " WHERE MSKH = '" & MSKH & "'"
Set KHrec = ProcessRecordset(sqlSt)
objKhachHang.PopulatePropertiesFromRecordset KHrec

.cmbKhachhang.RowSourceType = "Value List"
.cmbKhachhang.RowSource = objKhachHang.HoChulot & ";" & objKhachHang.MaKhachHang
.cmbKhachhang = objKhachHang.MaKhachHang

.txtDiachi = objKhachHang.Diachi
.txtPhone = objKhachHang.Dtvp
.txtMasoThue = objKhachHang.Msthue

KHrec.Close
Set KHrec = Nothing
' Và nạp nguồn dữ liệu chi tiết các mặt hàng phát sinh cho Subform
If IsMissing(NoSetSourceRecForSubForm) Then SetSourceRecForSubForm Me, "frmCtuNXCT"

End With
End If
'
SQLrec.Close
Set SQLrec = Nothing
End Sub
+ Để nạp nguồn dữ liệu chi tiết các mặt hàng phát sinh cho Subform ta dùng thủ tục SetSourceRecForSubForm
Sub SetSourceRecForSubForm(mForm As Form, sForm As String)
Dim sqlSt As String
Dim SQLrec As ADODB.Recordset
Dim tblName As String
Dim vSoCtu, stChema As String

vSoCtu = mForm!cmbSoCtu
‘Xác định nguồn dữ liệu chứa thông tin chi tiết hàng hoá phát sinh của chứng từ cần nạp
If Not IsNull(vSoCtu) Then
tblName = "tblctunxct"
stChema = GetSchemaTable(tblName)
sqlSt = "SELECT " & stChema & ".tbldmhanghoa.tenhanghoa, " & stChema & ".tblctunxct.*"
sqlSt = sqlSt & " FROM " & stChema & ".tbldmhanghoa INNER JOIN " & stChema & ".tblctunxct"
sqlSt = sqlSt & " ON " & stChema & ".tbldmhanghoa.mahang=" & stChema & ".tblctunxct.mahang"
sqlSt = sqlSt & " WHERE " & stChema & ".tblctunxct.soctu = '" & vSoCtu & "'"

Set SQLrec = ProcessRecordset(sqlSt)

Set mForm(sForm).Form.Recordset = SQLrec
‘Nạp thông tin chi tiết lên các ô dữ liệu tương ứng trên SubForm
With mForm(sForm).Form
.Requery
!txtId.ControlSource = "id"
!txtMahang.ControlSource = "mahang"
!txtTenHanghoa.ControlSource = "tenhanghoa"
!txtCapDvt.ControlSource = "dvt"
!txtDvt.ControlSource = "=IIF(not isnull(dvt),flookup('kihieu','tbldonvitinh','cap=' & [dvt]),'')"
!txtSoluong.ControlSource = "soluong"
!txtDongia.ControlSource = "dongia"
!chkCKTL.ControlSource = "lacktyle"
!txtMucCK.ControlSource = "mucck"
End With
SQLrec.Close
Set SQLrec = Nothing
End If
End Sub
Như vậy, khi ta chọn 1 số chứng từ xác định, ứng dụng sẽ cho chạy các thủ tục nêu trên để nạp nguồn dữ liệu tương ứng cho Main Form và SubForm.
Ta gán các thủ tục cần thực hiện với sự kiện ngay sau khi số chứng từ được cập nhật (cmbSoCtu_AfterUpdate)
Private Sub cmbSoCtu_AfterUpdate()
Dim vSoCtu
ClearInputCTHH ‘Xoá trống các ô nhập chi tiết hàng hoá
vSoCtu = Trim(Me.cmbSoCtu.Text)
LoadInvoiceInfoToForm vSoCtu ‘Nạp thông tin chứng từ đã chọn lên MainForm và SubForm
End Sub
Vậy khi cần hiệu chỉnh chi tiết chứng từ đã lập và đang hiển thị trên Form thì làm sao?
Thật đơn giản các Bạn ạ:
+ Đối với thông tin là chi tiết hàng hoá phát sinh: ta chỉ cần chuyển con trỏ đến dòng ghi mặt hàng cần hiệu chỉnh là ứng dụng sẽ copy các thông tin đó lên các ô có nền sẩm màu sẵn sàng cho ta hiệu chỉnh (hoặc xoá). Hiệu chỉnh xong ta bấm nút lệnh ghi bên phải (có hình chiếc đĩa mềm) để cho ghi lại nội dung vừa ddiiefu chỉnh.
Việc này được thực hiện thông qua thủ tục SaveToInvoiceDetailFromForm
Sub SaveToInvoiceDetailFromForm(Optional InVoiceDetailId)
'Luu thong tin tren form vao tblctunxCT
'UpdateInvoiceDetail
On Error GoTo HandleError

Dim sqlSt As String, tblName As String
Dim vId
Dim MucCK As Double, CKTL As Byte

Call OpenMyConnection

tblName = "tblctunxct"
With Me
vId = Me.txtDetailId
MucCK = Nz(.txtMucCK)
If IsNull(.chkCKTL) Then
CKTL = 0
Else
If .chkCKTL.Value = True Then
CKTL = 1
Else
CKTL = 0
End If
End If
‘Phân biệt là trường hợp nhập mới hay hiệu chỉnh lại dữ liệu đã có.
If Not IsNull(vId) Then
sqlSt = "UPDATE " & GetSchemaTable(tblName) & "." & tblName & " SET "
sqlSt = sqlSt & " soctu ='" & Trim(.cmbSoCtu) & "',"
sqlSt = sqlSt & " mahang ='" & .cmbMSHH & "',"
sqlSt = sqlSt & " dvt =" & .cmbDvt & ","
sqlSt = sqlSt & " soluong =" & .txtSoluong & ","
sqlSt = sqlSt & " dongia =" & .txtDongia & ","
sqlSt = sqlSt & " lacktyle =" & CKTL & ","
sqlSt = sqlSt & " mucck =" & MucCK
sqlSt = sqlSt & " WHERE ("
sqlSt = sqlSt & " soctu='" & Trim(Me.cmbSoCtu) & "'"
sqlSt = sqlSt & " AND id=" & InVoiceDetailId
sqlSt = sqlSt & ")"
Else
sqlSt = "INSERT INTO " & GetSchemaTable(tblName) & "." & tblName
sqlSt = sqlSt & "(soctu, mahang, dvt, soluong, dongia, lacktyle, mucck)"
sqlSt = sqlSt & " VALUES ("
sqlSt = sqlSt & " '" & Trim(.cmbSoCtu) & "',"
sqlSt = sqlSt & " '" & .cmbMSHH & "',"
sqlSt = sqlSt & " " & .cmbDvt & ","
sqlSt = sqlSt & " " & .txtSoluong & ","
sqlSt = sqlSt & " " & .txtDongia & ","
sqlSt = sqlSt & " " & CKTL & ","
sqlSt = sqlSt & " " & Nz(MucCK)
sqlSt = sqlSt & ")"
End If
End With


MyConn.Execute sqlSt

Call CloseMyConnection ‘Dùng xong rồi thì đóng lại cho đỡ tốn tài nguyên và khỏi gặp xung đột dữ liệu đó các Bạn.

HandleError:
If Err > 0 Then
GeneralErrorHandler Err.Number, Err.Description, NhapXuat_FORM, "SaveToInvoiceDetailFromForm"
Exit Sub
End If
End Sub
Ở đây chúng ta chú ý: có 2 trường hợp cần phân biệt là nhập mới và hiệu chỉnh thông tin đang có.
Xem trong thủ tục trên chúng ta thấy thủ tục có phân biệt 2 trường hợp này bằng cách xet giá trị của ô txtDetailId, đây là ô chứa giá trị Id của chi tiết hàng hoá phát sinh. Trong thiết kế, ta cho ô này ẩn đi (bằng cách khai báo thuộc tính Visible = False). Nếu ô này có chứa nội dung xác định thì là trường hợp hiệu chỉnh dữ liệu đang có, ngược lại nếu nó rổng không (IsNull) là trường hợp nhập mới.
Trường hợp muốn xoá dòng ghi chi tiết hàng phát sinh xác định: ta cho nạp dòng ghi chi tiết hàng hoá đó lên các ô sẩm màu rồi bấm nút lệnh Xoá (có hình gạch chéo) nằm bên trái dòng của các ô sẩm màu này.
Thủ tục tương ứng như sau:
Dim vHoi As Long, sqlSt As String, tblName As String
Dim DetailId
DetailId = Me.txtDetailId
If Not IsNull(DetailId) Then
vHoi = Eval("msgbox('" & "Ban vua ra lenh cho xoa dong ghi mat hang nay" & vbCrLf & "Co phai Ban chac chan muon Xoa hay khong?" & "@" & "Bam YES de xoa, bam NO de huy bo lenh nay" & "@" & "',36,'Xoa chi tiet hang hoa')")
If vHoi = vbYes Then
tblName = "tblctunxct"
sqlSt = "DELETE " & GetSchemaTable(tblName) & "." & tblName
sqlSt = sqlSt & " WHERE Id =" & DetailId
'
Call OpenDbConnection
ExecuteSQLCommand sqlSt
Call CloseDbConnection
'Xoá xong thì cho cập nhật lại nội dung hiển thị trên SubForm
SetSourceRecForSubForm Me, "frmCtuNXCT"
End If
End If
+ Đối với thông tin chung của chứng từ: cũng tương tự như trên, ta hiệu chỉnh thông tin tại các ô tương ứng; và cũng phân biệt 2 trường hợp: nhập mới và hiệu chỉnh thông tin đang có. Việc ghi lại các thông tin đã cập nhật vào bảng dữ liệu ghi chứng từ phát sinh được thực hiện bằng thủ tục SaveToInvoiceFromForm
Sub SaveToInvoiceFromForm(Optional InvoiceId, Optional NoLoadInfo)
'Luu thong tin tren form vao tblctunx

'UpdateOrInsert:
'+ True: Luu thong tin thay doi vao mau tin dang hien huu
'+ Flase: Them mau tin moi

'InvoiceId: so chung tu
'
On Error GoTo HandleError

Dim sqlSt As String, tblName As String
Dim vId

Call OpenMyConnection

tblName = "tblctunx"

With Me
vId = Me.txtId
If Not IsNull(vId) Then
If IsNull(InvoiceId) Then Exit Sub
sqlSt = "UPDATE " & GetSchemaTable(tblName) & "." & tblName & " SET "
sqlSt = sqlSt & " soctu ='" & .cmbSoCtu & "',"
sqlSt = sqlSt & " ngay ='" & Format$(.txtNgay, "dd-mmm-yy") & "',"
sqlSt = sqlSt & " msnv ='" & .cmbNghiepvu & "',"
sqlSt = sqlSt & " mskh ='" & .cmbKhachhang & "'"
If Not IsNull(.txtNguoiGiaodich) Then sqlSt = sqlSt & ", nguoigiaodich ='" & .txtNguoiGiaodich & "'"
If Not IsNull(.txtTsuat) Then sqlSt = sqlSt & ", tsuatvat =" & .txtTsuat
sqlSt = sqlSt & " WHERE ("
sqlSt = sqlSt & " soctu='" & InvoiceId & "'"
sqlSt = sqlSt & ")"

Else
If IsNull(.cmbSoCtu) Then Exit Sub
sqlSt = "INSERT INTO " & GetSchemaTable(tblName) & "." & tblName
sqlSt = sqlSt & "(soctu, ngay, msnv, mskh"
If Not IsNull(.txtNguoiGiaodich) Then sqlSt = sqlSt & ", nguoigiaodich"
If Not IsNull(.txtTsuat) Then sqlSt = sqlSt & ", tsuatvat"
sqlSt = sqlSt & ")"
sqlSt = sqlSt & " VALUES ("
sqlSt = sqlSt & " '" & .cmbSoCtu & "',"
sqlSt = sqlSt & " '" & Format$(.txtNgay, "dd-mmm-yy") & "',"
sqlSt = sqlSt & " '" & .cmbNghiepvu & "',"
sqlSt = sqlSt & " '" & .cmbKhachhang & "'"
If Not IsNull(.txtNguoiGiaodich) Then sqlSt = sqlSt & ", '" & .txtNguoiGiaodich & "'"
If Not IsNull(.txtTsuat) Then sqlSt = sqlSt & ", " & .txtTsuat
sqlSt = sqlSt & ")"
End If
End With

MyConn.Execute sqlSt

‘Lưu xong thì cho cập nhật lại các thông tin đã lưu lên Form.
If IsMissing(NoLoadInfo) Then
LoadInvoiceInfoToForm Me.cmbSoCtu
Else
LoadInvoiceInfoToForm Me.cmbSoCtu, True
End If

Call CloseMyConnection ‘Mở ra xài xong thì đóng lại

HandleError:
If Err > 0 Then
GeneralErrorHandler Err.Number, Err.Description, NhapXuat_FORM, "SaveToInvoiceFromForm"
Exit Sub
End If
End Sub
Trong thủ tục trên ta chú ý đoạn
If IsMissing(NoLoadInfo) Then
LoadInvoiceInfoToForm Me.cmbSoCtu
Else
LoadInvoiceInfoToForm Me.cmbSoCtu, True
End If
Cho Lưu xong thì cho cập nhật lại các thông tin đã lưu lên Form. Cái này cần để ghi bổ sung những thông tin chỉ phát sinh khi dữ liệu được ghi vào bảng dữ liệu, chẳng hạn như chỉ số Id tự động của bản ghi, hoặc các giá trị tính toán cần thiết khác.
Khi ghi dữ liệu vào bảng dữ liệu, chúng ta cần chú ý đến 1 thực tế là có những thông tin chi tiết của chứng từ không nhất thiết lúc nào cũng có. Do vậy khi ta viết các thủ tục cập nhật phải chú ý đến các trường hợp này. Các Bạn có thể thấy điều này được thể hiện ở những dòng sau đây trong thủ tục nêu trên:
If Not IsNull(.txtNguoiGiaodich) Then sqlSt = sqlSt & ", '" & .txtNguoiGiaodich & "'"
If Not IsNull(.txtTsuat) Then sqlSt = sqlSt & ", " & .txtTsuat
Ở đây tôi xác định các chi tiết: Người trực tiếp giao dịch, thuế suất VAT là những chi tiết thông tin không phải lúc nào cũng bắt buộc phải có khi lập chứng từ nên đã dự liệu bằng các statement IF... THEN ...

Bài đã dài. Xin hẹn các Bạn trong bài sau.
 

lehongduc

Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,
Có Bạn vừa gửi email cho tôi góp ý rằng sao ta không phát triển thủ tục SetSourceRecForSubForm lên để áp dụng cho việc nạp RecordSource cho subform trong mọi trường hợp, chứ không phải chỉ riêng cho 1 trường hợp như tôi đã làm.
Đây là một góp ý rất chí lý. Vậy xin mời các Bạn tham gia viết lại thủ tục SetSourceRecForSubForm theo hướng phát triển được đề nghị nêu trên.
 

Thanhtandofico

New Member
Hội viên mới
Ðề: Sử dụng Class Module và Kết nối dữ liệu SQL SERVER trong Access VBA

Chào các Bạn,
Xin nói thêm về chuyện ứng dụng và dữ liệu còn "thô":

Nói chúng "thô" bởi lẽ:

1. File dữ liệu SQL SERVER chỉ mới có các bảng dữ liệu thôi. Như vậy chúng chỉ mới là chỗ để lưu dữ liệu phát sinh, chưa làm được việc xử lý dữ liệu (ta dễ thấy một phần những việc đơn giản trong việc xử lý dữ liệu này như: lưu, xóa, trích xuất thông tin, lọc thông tin).
Bản thân SQL SERVER là 1 hệ thống quản trị cơ sở dữ liệu mạnh, chứ không chỉ đơn thuần là nơi để lưu dữ liệu. Ta sẽ bàn tới cách giao nhiệm vụ xử lý dữ liệu cho cái file dữ liệu SQL SERVER đã tạo ở trên. Hiện nay việc xử lý dữ liệu vẫn còn do file ứng dụng đãm trách thông qua các câu lệnh SQL trong các module.

2. Nếu chạy file ứng dụng đang có ta sẽ thấy khi mở Form "frmContact" (dùng để cập nhật và xem dữ liệu) sẽ còn mất 1 ít thời gian mà ta có thể cảm nhận được. Mục tiêu của chúng ta là phải làm sao cho nhanh đến mức không cảm thấy phải chờ một chút nào.
Tôi đã kiểm tra thử mở form nói trên với kết nối internet qua 1 USB 3G của Viettel (loại 7.2 Mbps) trên xe hơi đang chạy: thời gian nạp xong form mất khoảng 25 giây.

Có Bạn nào tìm được lý do nào khác không?
Em đã thử copy các form và module của bác sang file khác nhưng không chạy được ạ. Xin chỉ giúp. Tks!
 

CẨM NANG KẾ TOÁN TRƯỞNG


Liên hệ: 090.6969.247

KÊNH YOUTUBE DKT

Kỹ thuật giải trình thanh tra BHXH

Đăng ký kênh nhé cả nhà

SÁCH QUYẾT TOÁN THUẾ


Liên hệ: 090.6969.247

Top