Import xml file to table

[artec]

Hi, Is it possible to import xml file and add to all tables columns with filename( in all imported records) ?

This is my code:

Dim strFolder As String
Dim EFile As Object
strFolder = "C:\
Desktop\lk\1\"
strFile = Dir(strFolder & "*.xml")
Do While Len(strFile) > 0
Application.ImportXML strFolder & strFile, acAppendData
strFile = Dir()
.

.

[June7]

What is the issue? Does it work? If not, what happens - error message, wrong results, nothing?

What do you mean by 'add to all tables columns with filename'?

[artec]

Import files works without any errors but it makes a few tables from one file (without ralationship). I need to force import all files to one table or a few tables with key, to make relations. File name is unique and I think that good way will be insert it to new columns in all new tables.

[June7]

Maybe this will offer ideas
https://www.accessforums.net/import-...que-26039.html

[artec]

Maybe this will offer ideas
https://www.accessforums.net/import-...que-26039.html

I found other idea but somethig works wrong.
I have code to import one file like this:

DoCmd.SetWarnings FalseConst acAppendData = 2
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\path\xml.accdb"
objAccess.ImportXML "" & Me.lstFileList.Value & "", acAppendData
DoCmd.RunSQL "UPDATE TAB1 SET [IDTMP] = '" & Me.Tekst6.Value & "' WHERE [ID] is null ;"
.

This part

objAccess.ImportXML "" & Me.lstFileList.Value & "", acAppendData.

import files which are listed from call function. This works great. Field [IDTMP] is empty and I need update it with this sql

DoCmd.RunSQL "UPDATE TABELA SET [IDTMP] = '" & Me.Tekst6.Value & "' WHERE [ID] is null ;".

And now is problem.Between this two parts of code import and update access can't refresh table TAB1 and update doesn't work. Sometimes works and updates field [IDTEMP]. Sometimes when I import next xml file updates record for first file, sometimes updates two records with the same Me.Tekst6.Value.
Probably is something wrong with refresh table.
One more. If I share import and update for two different button and use after import system button "Refresh all" and update sql everything works great.What is wrong ?

[June7]

I don't know why Refresh would be needed after update is executed. Do you have the tables open when this code runs? Also don't know why sometimes fails. I would have to test code with data if you want to provide files for analysis. Follow instructions at bottom of my post.

[artec]

Ok. There are files for analysis. Thank You.

[June7]

Your attached zip is corrupted and I can't open it to extract files.

[artec]

Please try to change extession to rar. It was comressed by winrar. I've download this file and it's ok. I did new zipped version and attache it.

[June7]

I can't open rar, only Windows compression. I did get the last zip to extract.

When I click the Import and Update button, get message "The expression On Click you entered as the event property setting produced the following error: A problem occrred while Microsoft Office Access was communicating with the OLE server or ActiveXControl."

I was testing with A2007. Will try again later with A2010.

Any idea why I am getting this error?

[artec]

I was testing it on 2007 and 2010 and its ok. Please put Test.accdb, Test1.xml, Test2.xml files to C:\XML on your disk. This is my location for this database and files. I hope that help you to run it.

[June7]

Still get same error. All of the VBA event procedures trigger this error. I got the following to work in my db:

Private Sub Polecenie8_Click()
Application.ImportXML "C:\XML\Test1.xml", acAppendData
DoCmd.RunSQL "UPDATE TABELA SET [IDTMP] = 'test' WHERE [IDTMP] is null;"
End Sub

[artec]

I dont know what is, but your code which you got works perfectly without any errors and makes procedure what I need. Firstly file is imported and next table is updated. Everything works right. Thank You very much !