I found code on the MS TechNet website to programatically import XML data from a file into a table in the database. Here is that code:

Const acAppendData = 2

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

objAccess.ImportXML "c:\scripts\test.xml", acAppendData


The code works great, but when I put the filename of my main database in the "opencurrentdatabase" line I get an error message saying that the database is open and cannot be edited. If I use a different filename other than the file I have open it works great.

How can I get the code to run on a form *WITHIN* the DB I want the data to be appended to? Thanks in advance for any help.

CG

Here's another code I got from the MSDN website but it locks up Access:

Application.ImportXML _
DataSource:="c:\Service Report 5-30-08 2_data.xml", _
ImportOptions:=acStructureAndData

I found a working code. I put it behind a button on a form and it works nicely. Here's the code:

Private Sub Command1_Click()
Const acAppendData = 2
Application.ImportXML "c:\Service Report 5-30-08 2_data.xml", acAppendData
End Sub

Now!!!! What I want to do next is to be able to add multiple XML files at one time.
The files all have a different name however.
How can I get this code to loop continually to go through all .xml files in a directory?

CG

I will try to find this using the search function on the forum but if someone finds it faster please help!!!

Did you type in this code freehand or cut and paste from your application? The reason for asking is that certain questions need addressing.

Application.ImportXML "c:\Service Report 5-30-08 2_data.xml", acAppendData

1. How are you identifying which table you are importing it in to?
2. Is it a New or Existing Table?
3. How are you splitting the path from the file name? There does not seem to be a \ before the fiel name.

1.) The XML file desingates which table the data will be appended to.
2.) It is an existing table.
3.) Maybe I need to check this again but it appears to read as the path is the c:\ drive.

I have compiled a new code that searches for all XML files in a directory and then imports them.

Here's the code:
Option Compare Database
Public Sub Command0_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String

DoCmd.SetWarnings False
path = "C:\"

'Loop through the folder & build file list
strFile = Dir(path & "*.xml")

While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)

Application.ImportXML filename, acAppendData

Next intFile

DoCmd.SetWarnings True

End Sub
But I want to take it one step further if someone can help please. I want to be able to move the imported files to an archive directory. Does anyone know the VB for that function?

CG

Found the code to move all files from the dir.

dim fso as object

set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "C:\olddir\*.jpg", "c:\newdir"