Appending XML data to tables - Code Help

[ccg_0004]

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

 

[ccg_0004]

Got the code to work

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!!!

 

[DCrake]

Simple Software Solutions

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.

 

[ccg_0004]

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.

 

[ccg_0004]

Latest Updat

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"