Quản lý recordset trong Access Data Project

Đã có rất nhiều bài viết về quản lý tập hợp bản ghi (recordset) với đối tượng form (biểu mẫu), control (điều khiển) và report (báo cáo) trong cơ sở dữ liệu Microsoft Access. Nhưng gần đây, tôi buộc phải phát triển một ứng dụng với khuynh hướng khá mới, đòi hỏi một số thao tác sáng tạo trên recordset. Các yêu cầu để thực hiện:

1. Chương trình phải là một ADP (Access Data Project), không phải là MDB.

2. Dự án có thể tiếp tục kết nối sang cơ sở dữ liệu ‘config’ của SQL Server.

3. Tất cả dữ liệu phải được lưu trữ trong các cơ sở dữ liệu ‘client’ SQL Server.

4. Các form phải kết nối tới cơ sở dữ liệu client chính xác trong thời gian chạy.

Bạn có thực hiện điều này? Tất cả cơ sở dữ liệu (CSDL) được đặt trên một SQL Server đơn, nhưng ứng dụng có thể cần lấy dữ liệu từ bất kỳ một nguồn trong số đó. Ở một số trường hợp, người dùng yêu cầu trích lấy dữ liệu từ một CSDL cụ thể. Họ có thể khởi phát hoạt động sử dụng CSDL riêng và ứng dụng phải được trỏ động tới nơi lưu trữ dữ liệu chính xác.

Ý nghĩ đầu tiên của tôi là phải thay đổi thuộc tính kết nối của ứng dụng ADP mỗi khi người dùng chọn một CSDL khác nhau. Nhưng điều này không đơn giản mà cũng chẳng dễ dàng gì. Thay đổi kết nối trong thời gian chạy cũng giống như bắt con ngựa đua phải quay ngược lại giữa đường đua. Khi một thuộc tính kết nối của ADP bị đóng, tất cả form đều bị huỷ.

Giải pháp cuối cùng rất dễ chịu và có thể dùng lại được. Trong một nutsell, nó gồm những phần sau:

• Kết nối ADP được gắn với CSDL config.

• CSDL config quản lý đăng nhập người dùng.
• Config bao gồm một danh sách CSDL client.
• Nhiệm vụ cho tất cả client nằm trong CSDL Config.

• ADP có hai thủ tục đơn giản hoá chuyển đổi.

• Thủ tục con: LoadDbsConnectString(ClientID).
• Hàm: GetRecordset(SQL) As Recordset.

• Dữ liệu Form và Control được load qua thuộc tính Recordset.

• Thuộc tính Form.RecordSource phải để trống.
• Thuộc tính Form.RecordSet phải được thiết lập ở mã VBA.
• Phải dùng toán tử Set.
• Bảng, bảng ảo (view) hoặc thủ tục lưu trữ có thể được sử dụng.

Ví dụ bên dưới (hình minh hoạ) là frmAddress ở mô hình thiết kế. Form này sẽ đòi hỏi thiết lập bốn recordset: một cho dữ liệu của form và ba cho các hộp combo box. Chú ý là recordset cho một trong ba hộp đó, cboStateCode sẽ phụ thuộc vào giá trị được chọn trong tuỳ chọn country (đất nước), cboCountryID. Chúng ta sẽ xem có thể load form này với dữ liệu như thế nào.

Chú ý: Hiện nay không có mã nguồn để download. Bởi để làm việc với các kết nối đòi hỏi phải có lượng rất lớn mã nguồn. Do người đọc thường có máy chủ và CSDL riêng nên điều này trở thành một vấn đề không đơn giản để triển khai.

Thiết lập kết nối ADP

Như đã đề cập tới ở trên, bước đầu tiên là thiết lập toàn diện kết nối cho ADP. Trong trường hợp của chúng ta, đây là thiết lập cho CSDL Config chứa thông tin về các database client khác nhau mà ứng dụng có thể trỏ tới.

Thuộc tính Connection của ADP có thể được thiết lập dễ dàng trong thời gian thiết kế mà không cần phải chỉnh sửa bởi người dùng. Đơn giản chỉ cần chọn Connection từ menu File và điền đầy đủ giá trị yêu cầu trong hộp thoại.

Hộp thoại này có một nút "Test Connection" (kiểm tra kết nối), nhưng nó là một placebo (hộp để giữ chỗ). Bạn chỉ có thể chọn một CSDL nếu nhập một server hợp lệ. Nếu bạn có cả tên server và database hợp lệ, sẽ chẳng có cái gì để phải kiểm tra cả. Nếu muốn yên tâm hơn, kích vào nút Test Connection để kiểm tra lại trước khi đóng hộp thoại.

Tạo các thủ tục

Theo các yêu cầu ở trên, nguồn cho client data (dữ liệu cho máy khách) phải nằm ở dạng JIT (just in time, tức tạm thời). Những nguồn này có thể là khi người dùng lựa chọn tìm kiếm một CSDL cụ thể hoặc là kết quả sau khi thực hiện một nhiệm vụ gắn với một cơ sở dữ liệu client cụ thể. Nhưng kết luận cuối cùng thì, bạn sẽ không biết CSDL nào được trỏ tới trước khi form được mở.

Để đáp ứng yêu cầu này, chúng tôi tạo một giá trị xâu tổng thể (g_ClientCnn), chia nhỏ xâu kết nối đầy đủ vào CSDL client được yêu cầu. Do đó, khi một yêu cầu dữ liệu được thực hiện, chúng tôi sẽ xác định ClientID phù hợp và đưa nó vào thủ tục con thiết lập xâu kết nối. (Xem đoạn mã bên dưới để hiểu chi tiết).

' Client connect string is saved in this public variable.
Public g_ClientCnn As String
Public Sub LoadDbsConnectString(ByVal lClientID As Long)
On Error GoTo Err_Handler
Dim rstTemp As New ADODB.Recordset
Dim strSQL As String
Dim strDatabase As String
Dim strProjCnn As String
' You must hard-code the name of the config database. It will
' be used below to simplify the creation of the new connect string.
Const strProjDBS As String = "RecreationConfigDB"
' The config database must include a table to manage client
' database names based on an identifier, such as ClientID.
strSQL = "SELECT [DatabaseName] FROM tblClient " & _
"WHERE [ClientID]=" & lClientID
rstTemp.Open strSQL, CurrentProject.Connection
' If the client record isn't found, throw an error.  Otherwise,
' use the database name to update the connection string variable.
If rstTemp.BOF And rstTemp.EOF Then
MsgBox "Couldn't locate the database.", vbCritical, "ERROR"
Else
' The Replace function simplifies the connection string edit.
' We know that the format of CurrentProject.Connection is
' correct.  Simply replace the database name (assuming the
' user's login has permission to both databases.)
strDatabase = rstTemp!DatabaseName
strProjCnn = CurrentProject.Connection
g_ClientCnn = Replace(strProjCnn, strProjDBS, strDatabase)
End If
Exit_Here:
Set rstTemp = Nothing
Exit Function
Err_Handler:
Resume Next
End Sub

Ngoài ra còn cần một thủ tục con để thiết lập xâu kết nối, với một hoặc nhiều hàm tải dữ liệu vào recordset. Bên dưới là một trong các hàm trả ra recordset ADO. Tôi sử dụng một số thực thi khác cho thủ tục lưu trữ và chạy các lệnh INSERT, UPDATE DELETE. Tuy nhiên, để thực hiện nhiệm vụ tiếp theo, chúng ta cần trả lại một recordset ADO, thực hiện như sau:

Public Function GetRecordset(ByVal sSQL As String) As ADODB.Recordset
    On Error GoTo Err_Handler
   
    Dim rstTemp As New ADODB.Recordset
    Dim cnnTemp As New ADODB.Connection
   
    ' First open a temporary connection, and then load the recordset.
    cnnTemp.Open g_ClientCnn
    rstTemp.Open sSQL, cnnTemp, adOpenDynamic, adLockOptimistic
    ' This doesn't include any error handling
    ' You will have to add that to meet your needs.
    Set GetRecordset = rstTemp
Exit_Here:
    Set rstTemp = Nothing
    Set cnnTemp = Nothing
    Exit Function
Err_Handler:
    Resume Exit_Here
End Function 

Đặt tất cả lại với nhau

Bây giờ chúng ta đã có tất cả các thành phần (một kết nối dự án, một form và bốn thủ tục). Vấn đề chỉ còn là làm sao ghép chúng lại với nhau trong sự kiện Form_Open(). Hãy chắc chắn rằng form của bạn không có lệnh SELECT trong thuộc tính RecordSource, vì nó sẽ cạnh tranh với mã nguồn chúng ta sẽ chạy. Với mục đích đó, form này sẽ không có nguồn record khi thiết kế. Thực tế, nó không thể trỏ tới bất kỳ bảng nào vì các bảng client chúng ta tìm kiếm thậm chí không nằm trong CSDL Config, CSDL hiện ADP đang được kết nối. Dataset sẽ được gán tự động vào form và các điều khiển của nó nằm trong mã sau:

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Handler
    Dim strSQL As String
    Dim lngEmployeeID As Long
    Dim lngCountryID As Long
       
    ' Call a 'Property Get' function to grab EmployeeID
    ' This will be used to filter the recordset.  (You will need to adapt
    ' this part of the code to match your SQL requirements.)
    lngEmployeeID = GetEmployeeID()
    ' Identify SQL to be passed, a stored proc in this case, and
    ' remember to use the SET command when assigning the recordset.
    strSQL = "spc_uclAddress @EmployeeID=" & EmployeeID
    Set Me.Recordset = GetRecordset(strSQL)
   
    ' Load record sources for all combo boxes
    strSQL = "spc_ddlAddressType"
    Set Me!cboAddressTypeID.Recordset = GetRecordset(strSQL, client)
    strSQL = "spc_ddlCountry"
    Set Me!cboCountryID.Recordset = GetRecordset(strSQL, client)
   
    ' The list of states depends upon the country.  This param
    ' is passed to the stored proc.  (our default is 1 for USA)
    lngCountryID = Nz(Me.Recordset!CountryID, 1)
    strSQL = "spc_ddlState @CountryID=" & lngCountryID
    Set Me!cboStateProvince.Recordset = GetRecordset(strSQL, client)
   
Exit_Here:
    Exit Sub
Err_Handler:
    Resume Next
End Sub

Nhiều năm nay tôi biết rằng, bạn có thể đưa một dataset vào form hay control, nhưng chưa bao giờ hiểu tại sao lại như thế. Khi tiếp cận với phương thức này, đột nhiên tôi hiểu ra lý do. Nó cũng hữu ích một cách đặc biệt khi bạn muốn sử dụng SQL Server để lưu trữ các thủ tục trong Access Data Project. Chương trình này chắc chắn cũng hoạt động được cho MDB mặc dù chưa có một kiểmt tra chính thức nào. Có một số quan điểm phản ứng mạnh về vấn đề này, phản đối ADP. Nhưng tôi phải nói rằng, với các phương thức đã được mô tả ở trên, việc sử dụng ADO recordset thực sự trở nên đơn giản, đặc biệt là khi tải dữ liệu vào form và control.