XML Automatic Import to Access 2002 1

[cjircita]

I need to generate an automatic XML import to Access 2002, using VB or VBScript. Does anyone have anything they can share?

 

[jonscott8]

If the target machine has AccessXP loaded, which I presume it does based on your post, the simplest method would be to automate Access.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_accessxml.asp

A simple VBS would resemble

Set oAccess = CreateObject("Access.Application&quot
oAccess.OpenCurrentDatabase "C:\mydb.mdb"
oAccess.ImportXML "C:\myxml.xml"
oAccess.CloseCurrentDatabase

Jon Hawkins

 

[cjircita]

Thank you. I do already have this working within Access. What I'm looking for is if someone has used VB or VBScript to automatically load XML into an .mdb file, bypassing the Access interface.

Thanks for the reply though.

 

[RandomUserIndy]

Here is something that I wrote a few days ago to do something similar I beleive. It goes to a target directory, imports any XML file it finds in Append Mode and then moves it to a new directory. Its not well commented right now, if you need any, let me know, I will try to help; but here it is...

Private Sub ImportXMLFile()
Set fs = Application.FileSearch
With fs
.LookIn = "E:\diagnostics"
.FileName = "*.xml"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Dim item As Variant
item = .FoundFiles.item(i)
Const STRUCTURE_AND_DATA = 2 'Appends data
On Error Resume Next 'In case a invalid XML documet is placed in E:/Diagnostics Folder, it will be moved to E:/Diagnostics2
Application.ImportXML DataSource:=item, ImportOptions:=STRUCTURE_AND_DATA
Dim js
Set js = CreateObject("Scripting.FileSystemObject&quot 'Establishes FileSystemObjet required for the MoveFile
js.MoveFile item, "E:/diagnostics2/" 'Moves files from Diagnostics to Diagnostics2 after being imported into Access
Next i
Else
Loop
End If
End With
End Sub

Hope this helps.

 

[RandomUserIndy]

This might help a little more, I added a timer to the form load to automatically check for new XML files ~ every 60 seconds; so far it seems reliable and stable, but I am sure that it could be improved upon. You could also just as easily replace the "move" routine with a "delete" routine; I needed to keep the orginal XML for back up, other wise I would just delete it on the fly. Hope this is of some use...

Sub Form_Load()
Me.TimerInterval = 60000 'Calls the "Sub Form_Timer()" routine every 60 seconds
End Sub

Sub Form_Timer()
Set fs = Application.FileSearch
With fs
.LookIn = "E:\diagnostics" 'Looks in this directory for XML files
.FileName = "*.xml" 'Sets the pattern to only find files with extension .xml
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count 'Start of loop stops when count is = to number of XML files found
Dim item As Variant
item = .FoundFiles.item(i)
Const STRUCTURE_AND_DATA = 2 'Appends data
On Error Resume Next 'In case a invalid XML documet is placed in E:/Diagnostics Folder, it will be moved to E:/Diagnostics2
Application.ImportXML DataSource:=item, ImportOptions:=STRUCTURE_AND_DATA 'Sets up the ImportXML paramaters; "item" estabalashes the file path, ImportOptions is set to Append data
Dim js
Set js = CreateObject("Scripting.FileSystemObject&quot 'Establishes FileSystemObjet required for the MoveFile
js.MoveFile item, "E:/diagnostics2/" 'Moves files from Diagnostics to Diagnostics2 after being imported into Access
Next i
End If
End With
End Sub

Billy Munger