|
|
Hack 95. Use Access as an XML Database
Have Access work as a front end to your XML data. A few hacks throughout this book (see the list at the end of this hack) explore XML usage with Access. This hack presents the crowning achievement: a complete XML database. To clarify, this hack shows you how Access can read from and write to XML files and have the data appear on a form for viewing and editing. The form has the requisite database functionality: browse, add a record, update a record, and delete a record. The power behind making this work is to incorporate the MSXML parser from Microsoft. Visit http://www.microsoft.com/xml and see "Provide Complete XML Control to Any Version of Access" [Hack #87] for an introduction to getting and using the parser. The parser is the key to getting Access to do more than simple XML imports and exports. Figure 9-18 shows the form used in the application. The displayed record is from an XML file. The form has Previous and Next buttons for navigating through records, as well as Update and Delete buttons. The New button is for entering new records, and the Save button is used for saving new records to the file. Figure 9-18. Displaying data from an XML file
The data is completely external, but it doesn't come from a table. This application contains no tables, whether linked or connected with ADO or ODBC. In fact, this application contains nothing except this one form. 9.5.1. The CodeThe following code behind the form takes care of all data management:
Option Compare Database
Public xmlobj As DOMDocument
Public xml_list As IXMLDOMNodeList
Public record_num As Integer
Public file_name As String
Private Sub cmdDelete_Click()
Dim xml_node As IXMLDOMElement
Set xml_node = xmlobj.documentElement.childNodes(record_num)
xmlobj.documentElement.removeChild xml_node
xmlobj.Save file_name
reload_file
End Sub
Private Sub cmdNew_Click()
Me.txtEmployeeID = ""
Me.txtEmployeeName = ""
Me.txtHireDate = ""
Me.txtRecordNum = ""
End Sub
Private Sub cmdNext_Click()
If record_num < xml_list.length - 1 Then
record_num = record_num + 1
Else
record_num = 0
End If
load_record
End Sub
Private Sub cmdPrevious_Click()
If record_num > 0 Then
record_num = record_num - 1
Else
record_num = xml_list.length - 1
End If
load_record
End Sub
Private Sub cmdSave_Click()
Dim xml_node As IXMLDOMElement
If Me.txtEmployeeID = "" Or Me.txtEmployeeName = "" Or _
Me.txtHireDate = "" Then
MsgBox "Must fill in all three fields"
Exit Sub
End If
Set xml_node = xmlobj.createElement("Employee")
xml_node.setAttribute "EmployeeID", Me.txtEmployeeID
xml_node.setAttribute "EmployeeName", Me.txtEmployeeName
xml_node.setAttribute "HireDate", Me.txtHireDate
xmlobj.documentElement.appendChild xml_node
xmlobj.Save file_name
reload_file
End Sub
Private Sub cmdUpdate_Click()
xmlobj.documentElement.childNodes(record_num) _
.Attributes(0).nodeValue = Me.txtEmployeeID
xmlobj.documentElement.childNodes(record_num) _
.Attributes(1).nodeValue = Me.txtEmployeeName
xmlobj.documentElement.childNodes(record_num) _
.Attributes(2).nodeValue = Me.txtHireDate
xmlobj.Save file_name
reload_file
End Sub
Private Sub Form_Open(Cancel As Integer)
file_name = "C:\EmployeeData.xml"
Set xmlobj = New DOMDocument
xmlobj.async = False
xmlobj.Load file_name
Set xml_list = xmlobj.selectNodes _
("Employees/Employee")
'load first record
record_num = 0
load_record
End Sub
Sub load_record()
Me.txtEmployeeID = _
xml_list.Item(record_num).Attributes(0).nodeValue
Me.txtEmployeeName = _
xml_list.Item(record_num).Attributes(1).nodeValue
Me.txtHireDate = _
xml_list.Item(record_num).Attributes(2).nodeValue
Me.txtRecordNum = record_num + 1
End Sub
Sub reload_file()
xmlobj.Load file_name
Set xml_list = xmlobj.selectNodes _
("Employees/Employee")
'load first record
record_num = 0
load_record
End Sub
9.5.2. Loading the XML FileWhen the form opens, a public XML variable (xmlobj) is set to the loaded XML file, which resides in memory. A list of nodes (xml_list) holds the Employee records, and the first record is displayed in the form:
Private Sub Form_Open(Cancel As Integer)
file_name = "C:\EmployeeData.xml"
Set xmlobj = New DOMDocument
xmlobj.async = False
xmlobj.Load file_name
Set xml_list = xmlobj.selectNodes _
("Employees/Employee")
'load first record
record_num = 0
load_record
End Sub
9.5.3. Browsing RecordsIn XML lingo, the length property is the same as the count property in VB. When the Next or Previous buttons are clicked, a public variable, record_ num, is compared with the number of XML records. If the record_num variable hits the total count as a result of clicking Next, it resets to 0. If the record_num variable hits 0 as a result of clicking Previous, it resets to the number of records. Clicking Next or Previous completes with a call to the load_record routine:
Private Sub cmdNext_Click()
If record_num < xml_list.length - 1 Then
record_num = record_num + 1
Else
record_num = 0
End If
load_record
End Sub
Private Sub cmdPrevious_Click()
If record_num > 0 Then
record_num = record_num - 1
Else
record_num = xml_list.length - 1
End If
load_record
End Sub
The load_record routine simply fills the controls on the form with the data from the XML record that is positioned at the record_num number:
Sub load_record()
Me.txtEmployeeID = _
xml_list.Item(record_num).Attributes(0).nodeValue
Me.txtEmployeeName = _
xml_list.Item(record_num).Attributes(1).nodeValue
Me.txtHireDate = _
xml_list.Item(record_num).Attributes(2).nodeValue
Me.txtRecordNum = record_num + 1
End Sub
9.5.4. Updating a RecordWhen data is changed while on the form, the Update button must be clicked to save the changes back to the original file. The process here is to update the node (the employee record) in the file with the form values. The Employee node is a child of documentElement Employees. The values aren't saved until the Save method runs on xmlobj. After that, the file is reloaded, and this last step resets the form back to the first record (an alternative is to leave the form displaying the updated record):
Private Sub cmdUpdate_Click()
xmlobj.documentElement.childNodes(record_num) _
.Attributes(0).nodeValue = Me.txtEmployeeID
xmlobj.documentElement.childNodes(record_num) _
.Attributes(1).nodeValue = Me.txtEmployeeName
xmlobj.documentElement.childNodes(record_num) _
.Attributes(2).nodeValue = Me.txtHireDate
xmlobj.Save file_name
reload_file
End Sub
9.5.5. Deleting a RecordTo delete a record set a node variable (xml_node) to the employee record. Then, the removeChild method of its parent deletes it:
Private Sub cmdDelete_Click()
Dim xml_node As IXMLDOMElement
Set xml_node = xmlobj.documentElement.childNodes(record_num)
xmlobj.documentElement.removeChild xml_node
xmlobj.Save file_name
reload_file
End Sub
As with other file changes, the Save method is necessary. 9.5.6. Adding a New RecordThe New and Save buttons work together to add a record to the XML file. The New button simply clears the form, and new employee information can be entered. The Save button runs the code that saves a new record. After validating that all text boxes contain data, a new element is created. Attributes are set to the form values, and the element, along with its attributes, are saved using the appendChild method. The Save method follows, and the file is reloaded (now it contains the new record):
Private Sub cmdSave_Click()
Dim xml_node As IXMLDOMElement
If Me.txtEmployeeID = "" Or Me.txtEmployeeName = "" Or _
Me.txtHireDate = "" Then
MsgBox "Must fill in all three fields"
Exit Sub
End If
Set xml_node = xmlobj.createElement("Employee")
xml_node.setAttribute "EmployeeID", Me.txtEmployeeID
xml_node.setAttribute "EmployeeName", Me.txtEmployeeName
xml_node.setAttribute "HireDate", Me.txtHireDate
xmlobj.documentElement.appendChild xml_node
xmlobj.Save file_name
reload_file
End Sub
9.5.7. See Also
 |
|
|
