xml

  • Thread starter Thread starter MMA IT
  • Start date Start date
- I don't think there is a direct way to do it. My best suggestion would be
to import it into Access 2003 as a table(s), and then have AC2k import the
table from AC2k3. (Access 2003 isn't that great at importing XML though).

-You can also use something like Word 2003 to save it as an html document,
though I'm not sure how the conversion works and whether or not it would be
importable by AC2k. Try it out.

- If you don't have Officek3, it gets a bit trickier. Hopefully the XML
file came with an xsd/xsl files as well. With those, figure out what your
"search" criteria would be. Create a layout of the table(s) you want to
import to in AC2k. Export those tables into HTML (still following me? ;).
Using the search criteria, write a program that read the entirety of the XML
file (using the "search" criteria) and then writes HTML files based on the
what was found. The HTML needs to match the layout that was exported by
AC2k. You should be able to import it then with no glitches.

- Other XML programs might exist to export into another common format that
can be universally imported without much interpretation. I don't recommend
this route though, as you are using "trusting" the 3rd program to
successfully convert it to something AC2k will understand.

With all of this, keep in mind the time tradeoff. If you are dealing with a
small amount of data, it would probably be easier to do this stuff by hand
if your resources are limited. If you are dealing with large data sets,
take the time to program it. The third method was the approach I
took...exported one messy table from Access 2.0 with 3500 records, had to
search for certain criteria, and then based off that criteria, the record
would be it in 1 of 6 new tables I was to create for a new database.
Figuring out the search criteria took me about 15 hours, writing the program
about 20 hours. Validation/Documentation 5 hours. All in all, only 40
hours worth of work vs. the time it would have taken to do it by hand (or
try to figure out how to do a bazillion joins in SQL ;).

Good luck,
 
Here is a procedure that I used to import XML files into access into access
2002.

Careful with line wraps.
'--------- START CODE -----------------
Private Sub XML_Import(strXMLFile As String, strTable As String)
On Error GoTo Proc_Error
'AUTHOR: Jose Hernandez (jhernandez_27@hotmail.com)
'PURPOSE: Import XML files, specifically Microsoft Baseline Security
Analyzer files.
'COMMENTS: You need to create the table before importing the XML file.
'RERERENCES: Microsoft XML, v3.0 and Microsoft ActiveX Data Objects x.x
Library
'MBSA Scans: %userprofile%\SecurityScans
'MBSA Folder: %ProgramFiles%\Microsoft Baseline Security Analyzer
Dim oXMLDoc As MSXML2.DOMDocument30
Dim oRootElem As MSXML2.IXMLDOMElement
Dim oNodeList As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
Dim oAttrib As MSXML2.IXMLDOMAttribute
Dim oScansRS As ADODB.Recordset
Dim strXSLFile As String

Set oXMLDoc = New MSXML2.DOMDocument30
oXMLDoc.async = False

'Open the ADO xml document
If oXMLDoc.Load(strXMLFile) = False Then GoTo Proc_Exit
Set oScansRS = New ADODB.Recordset
oScansRS.Open strTable, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

'Set oNode = oXMLDoc.selectSingleNode("Reports")
Set oNode = oXMLDoc.firstChild
Set oRootElem = oXMLDoc.documentElement
Set oNodeList = oRootElem.childNodes

For Each oNode In oNodeList
'Debug.Print oNode.xml
'Debug.Print oNode.Attributes.Length
'Debug.Print oNode.Attributes.Item(2).nodeValue
If oNode.Attributes.Length = 0 Then GoTo NextNode
oScansRS.AddNew
For Each oAttrib In oNode.Attributes
'Debug.Print oAttrib.Name & " --> " & oAttrib.Value
oScansRS.Fields(oAttrib.Name).Value = oAttrib.nodeValue
Next oAttrib
oScansRS.Fields("XMLFile").Value = Left(Dir(strXMLFile), InStr(1,
Dir(strXMLFile), ".") - 1)
oScansRS.Fields("NodeText").Value = oNode.Text
oScansRS.Update
NextNode:
Next oNode

Proc_Exit:
If Not oXMLDoc Is Nothing Then Set oXMLDoc = Nothing
If Not oRootElem Is Nothing Then Set oRootElem = Nothing
If Not oNodeList Is Nothing Then Set oNodeList = Nothing
If Not oNode Is Nothing Then Set oNode = Nothing
If Not oAttrib Is Nothing Then Set oAttrib = Nothing
If Not oScansRS Is Nothing Then Set oScansRS = Nothing
'MsgBox "Import Finished..."
Exit Sub
Proc_Error:
MsgBox Err.Description
Resume Proc_Exit
End Sub
'--------- END CODE -----------------


HTH

Jose
 
Back
Top