Quan ly recordset trong Access Data Project.

PhanVien.Com . Bai Viet . Cong Nghe Thong Tin . Co So Du Lieu . Quan ly recordset trong Access Data Project.

Quan ly recordset trong Access Data Project

Da co rat nhieu bai viet ve quan ly tap hop ban ghi (recordset) voi doi tuong form (bieu mau), control (dieu khien) va report (bao cao) trong co so du lieu Microsoft Access. Nhung gan day, toi buoc phai phat trien mot ung dung voi khuynh huong kha moi, doi hoi mot so thao tac sang tao tren recordset..

Quan ly recordset trong Access Data Project - Ban co dau.
Quan ly recordset trong Access Data Project - Ban khong dau.
Sponsored links:

Quan ly recordset trong Access Data Project.

Cac yeu cau de thuc hien:

1. Chuong trinh  phai la mot ADP (Access Data Project), khong phai la MDB.

2. Du an co the tiep tuc ket noi sang co so du lieu  ‘config’ cua SQL Server.

3. Tat ca du lieu  phai duoc luu tru  trong cac co so du lieu  ‘client’ SQL Server.

4. Cac form phai ket noi toi co so du lieu  client chinh xac  trong thoi gian  chay.

Ban co thuc hien dieu nay? Tat ca co so du lieu  (CSDL) duoc dat tren mot SQL Server  don, nhung ung dung  co the can lay du lieu  tu bat ky mot nguon trong so do. O mot so truong hop, nguoi dung  yeu cau trich lay du lieu  tu mot CSDL cu the. Ho co the khoi phat hoat dong  su dung CSDL rieng va ung dung  phai duoc tro dong toi noi luu tru  du lieu  chinh xac.

Y nghi dau tien cua toi la phai thay doi thuoc tinh ket noi cua ung dung  ADP moi khi nguoi dung  chon mot CSDL khac nhau. Nhung dieu nay khong don gian ma cung chang de dang gi. Thay doi ket noi trong thoi gian  chay cung giong nhu bat con ngua dua phai quay nguoc lai giua duong dua. Khi mot thuoc tinh ket noi cua ADP bi dong, tat ca form deu bi huy.

Giai phap cuoi cung rat de chiu va co the dung lai duoc. Trong mot nutsell, no gom nhung phan sau:

Ket noi ADP duoc gan voi CSDL config.

  • CSDL config quan ly dang nhap nguoi dung.
  • Config bao gom mot danh sach CSDL client.
  • Nhiem vu cho tat ca client nam trong CSDL Config.

ADP co hai thu tuc don gian hoa chuyen doi.

  • Thu tuc con: LoadDbsConnectString(ClientID).
  • Ham: GetRecordset(SQL) As Recordset.

Du lieu  Form va Control  duoc load qua thuoc tinh Recordset.

  • Thuoc tinh Form.RecordSource phai de trong.
  • Thuoc tinh Form.RecordSet phai duoc thiet lap o ma VBA.
  • Phai dung toan tu Set.
  • Bang, bang ao (view) hoac thu tuc luu tru  co the duoc su dung.

Vi du ben duoi (hinh minh hoa) la frmAddress o mo hinh thiet ke. Form nay se doi hoi thiet lap bon recordset: mot cho du lieu  cua form va ba cho cac hop combo box. Chu y la recordset cho mot trong ba hop do, cboStateCode se phu thuoc vao gia tri  duoc chon trong tuy chon country (dat nuoc), cboCountryID. Chung ta se xem co the load form nay voi du lieu  nhu the nao.

 

Chu y: Hien nay khong co ma nguon de download. Boi de lam viec  voi cac ket noi doi hoi phai co luong rat lon ma nguon. Do nguoi doc thuong co may chu  va CSDL rieng nen dieu nay tro thanh mot van de khong don gian de trien khai.

Thiet lap ket noi ADP

Nhu da de cap toi o tren, buoc dau tien la thiet lap toan dien ket noi cho ADP. Trong truong hop cua chung ta, day la thiet lap cho CSDL Config chua thong tin  ve cac database client khac nhau ma ung dung  co the tro toi.

Thuoc tinh Connection  cua ADP co the duoc thiet lap de dang trong thoi gian  thiet ke  ma khong can phai chinh sua boi nguoi dung. Don gian chi can chon Connection tu menu File va dien day du gia tri  yeu cau trong hop thoai.

 

Hop thoai nay co mot nut "Test Connection" (kiem tra ket noi), nhung no la mot placebo (hop de giu cho). Ban chi co the chon mot CSDL neu nhap mot server  hop le. Neu ban co ca ten server  va database hop le, se chang co cai gi de phai kiem tra ca. Neu muon yen tam hon, kich vao nut Test Connection  de kiem tra lai truoc khi dong hop thoai.

Tao cac thu tuc

Theo cac yeu cau o tren, nguon cho client data (du lieu cho may khach) phai nam o dang JIT (just in time, tuc tam thoi). Nhung nguon nay co the la khi nguoi dung  lua chon tim kiem  mot CSDL cu the hoac la ket qua sau khi thuc hien mot nhiem vu gan voi mot co so du lieu  client cu the. Nhung ket luan cuoi cung thi, ban se khong biet  CSDL nao duoc tro toi truoc khi form duoc mo.

De dap ung yeu cau nay, chung toi  tao mot gia tri  xau tong the (g_ClientCnn), chia nho xau ket noi day du vao CSDL client duoc yeu cau. Do do, khi mot yeu cau du lieu  duoc thuc hien, chung toi  se xac dinh ClientID phu hop va dua no vao thu tuc con thiet lap xau ket noi. (Xem doan ma ben duoi de hieu chi tiet).

Doan ma:

' 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
 

Ngoai ra con can mot thu tuc con de thiet lap xau ket noi, voi mot hoac nhieu ham tai du lieu  vao recordset. Ben duoi la mot trong cac ham tra ra recordset ADO. Toi su dung mot so thuc thi khac cho thu tuc luu tru  va chay cac lenh INSERT, UPDATE  DELETE. Tuy nhien, de thuc hien nhiem vu tiep theo, chung ta can tra lai mot recordset ADO, thuc hien nhu sau:

Doan ma:

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 

Dat tat ca lai voi nhau

Bay gio chung ta da co tat ca cac thanh phan (mot ket noi du an, mot form va bon thu tuc). Van de chi con la lam sao ghep chung lai voi nhau trong su kien Form_Open(). Hay chac chan rang form cua ban khong co lenh SELECT trong thuoc tinh RecordSource, vi no se canh tranh voi ma nguon chung ta se chay. Voi muc dich do, form nay se khong co nguon record khi thiet ke. Thuc te, no khong the tro toi bat ky bang nao vi cac bang client chung ta tim kiem  tham chi khong nam trong CSDL Config, CSDL hien ADP dang duoc ket noi. Dataset se duoc gan tu dong vao form va cac dieu khien cua no nam trong ma sau:

Doan ma:

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

Nhieu nam nay toi biet rang, ban co the dua mot dataset vao form hay control, nhung chua bao gio hieu tai sao lai nhu the. Khi tiep can voi phuong thuc nay, dot nhien toi hieu ra ly do. No cung huu ich mot cach dac biet khi ban muon su dung SQL Server  de luu tru  cac thu tuc trong Access  Data Project. Chuong trinh  nay chac chan cung hoat dong  duoc cho MDB mac du chua co mot kiemt tra chinh thuc nao. Co mot so quan diem  phan ung manh ve van de nay, phan doi ADP. Nhung toi phai noi rang, voi cac phuong thuc da duoc mo ta o tren, viec su dung ADO recordset thuc su tro nen don gian, dac biet la khi tai du lieu  vao form va control.

Sponsored links:

Updated: 28/02/2008 | Views: 1301 | Comments: 0

Read full: Quan ly recordset trong Access Data Project

Post your comment about Quan ly recordset trong Access Data Project go to this page.

Read more about Quan ly recordset trong Access Data Project

Your comment will be displayed here Quan ly recordset trong Access Data Project. Comment title Quan ly recordset trong Access Data Project . Quan ly recordset trong Access Data Project Cong Nghe Thong Tin in category Co So Du Lieu.

Comments on Quan ly recordset trong Access Data Project

Hien tai chua co binh luan nao ve bai viet Quan ly recordset trong Access Data Project!

Ban co y kien gi ve bai viet Quan ly recordset trong Access Data Project nay? Hay goi suy nghi, binh luan, danh gia, loi khuyen ... cua ban ve bai viet Quan ly recordset trong Access Data Project ban tai day.

Older than Quan ly recordset trong Access Data Project in Co So Du Lieu

Upload nhieu file XML vao cot kieu du lieu XML (28/2/2008)

Newer than Quan ly recordset trong Access Data Project in Co So Du Lieu

Thao tac voi cac tep du lieu voi bang Java (17/4/2008)SQL Server 2005 SP3 se ra mat cuoi nam nay (16/4/2008)MySQL doi moi "vuot bac" (16/4/2008)Quan ly co so du lieu voi TOAD (14/4/2008)Sony Ericsson K660i - di dong duyet web (5/4/2008)Su khac nhau giua SQL, MySQL va SQL Server? (28/3/2008)Gioi thieu ve SQL (28/3/2008)Nam bat so do thuc thi voi SQL Server 2005 Profiler (28/3/2008)Meo vat JavaScript (28/3/2008)Ma hoa trong SQL Server 2005 (11/3/2008)

Tin Tuc Online - Bai Moi

Phan Vien
Ket noi voi nha phat trien ung dung phan mem Phan Vien tren Facebook!
Phanvien | Mobile Version | Contact Us | Terms of Use | Privacy Policy | Refresh
Copyright © 2006 - 2013 PhanVien.Com . All rights reserved.
Cached: IP: 123.30.175.83, 17 February, 2013 09:28:02 (GMT +7 HANOI - VIETNAM).