?
Solved

IMPORT XML Data To Access 2003

Posted on 2006-03-20
20
Medium Priority
?
5,989 Views
Last Modified: 2009-12-16
Hi there!

I want to "append the XML data to an existing table" and it is easy to do it "manually" through

File > Get External Data > Import > *.XML (chosen) > (Import Options) "Append Data to Existing table(s)"  

but... I want my Access 2003 database, based on the server, to run this routine from an XML data presented also in the server... automatically, from 2 to 2 hours, from (say) 11: am till  4:00 pm (USA time) , from Monday till Friday.

I know I have to use VBA or some Access macro but... I am a newbie in those tools (although I can "read and make small code adjustments" with ease). Could someone help me with this coding?

Thanks,
fskilnik.


0
Comment
Question by:fskilnik
  • 14
  • 6
20 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 16238867
The VBA equivalent of that
Application.ImportXML "C:\Data\Test1.xml", acAppendData
leave you slightly at the mercy of the formatting Access decides is appropriate.
The core concept of xml is a good one - however it's implemtation varies so massively that it's hard to know what you'll get.

You could create a scheduled task to run the application and have that import run on startup...
Or you could have a constantly open (but perhaps hidden) form with a timer set.
And run the import at the specific times of the day.

To gain more control over the import - you'd have to have relatively loads more code - to parse it yourself and insert it using recordsets.
0
 

Author Comment

by:fskilnik
ID: 16240544

         Hello Mr. Purvis!

         Thanks for the quick and very interesting reply.  

>> .... leave you slightly at the mercy of the formatting Access decides is appropriate ... To gain more control over the import

         My table related to all this must be something like that:  
         (values as examples of the types involved, of course)

StockID   //   FullTicker  //    ItemPrice  //    ItemDate  //   ItemTime
95              NYSE: ACN              31.67               3/14/2006              4:01pm
107            NYSE: AEA               14.05              3/14/2006              4:02pm
121            Nasdaq: AEOS          29.40              3/14/2006              4:00pm

          Perhaps we could NOT worry about the formatting at all, just try to see what Access gets when updating at the server...!?   What do you think, please?


>>  You could create a scheduled task to run the application and have that import  run on startup...
Or you could have a constantly open (but perhaps hidden) form with a timer set.
And run the import at the specific times of the day.

       Both ideas seem nice to me but I have no idea which one would be easier for me, considering my programming (lack of) abilities... Could you suggest one of the approaches and, related to it, a (or some) links for me to adapt the code?

      Thanks a lot!
      fskilnik
0
 

Author Comment

by:fskilnik
ID: 16274069

Hello Mr. Purvis,

My native language is Portuguese and it seems to me that my phrase: "Perhaps we could NOT worry about the formatting at all, just try to see what Access gets when updating at the server...!?   What do you think, please?"

seems offensive, but it was not my intention at all, as the whole content seems to prove. You may check my other postings. I always try to be, at least, 100% polite if not friendly (what I do most times, please check!)

Anyway, I am sorry if you got me wrong and I apologise for my poor language ability.

I will close this question and reward you the points, because I simply don´t know what to do about it. I would be glad if you could continue helping me even after the question is officially closed.

Thanks a lot,
Fábio.




0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16274199
Certainly.  There was no offence at all - (I hadn't even noticed it... though I suppose CAPS is considered shouting - but had not even caught my eye :-S)

I answer a good few questions here and the occasional one can slip by in the mix.  
In the future if it seems as that is the case you can always post a "Any thoughts?" type reminder in the question if I've not responded by a day (usually means it's slipped past rather than I haven't had time).

Your language skills seem quite advanced to me!
(I really only speak English and VBA very well ;-)

Oh hang on.
Reading this question again I recognize it.  I thought I had responded to it.
The reply mustn't have posted.

I think I said something like
"Is your application open at the times you want to do the import?  Or could it be left open? Otherwise - it will have to be a scheduled task"

See I get there in the end.
:-)
0
 

Author Comment

by:fskilnik
ID: 16274621

Thanks for the quick reply,  Mr. Purvis!

I am glad I didn´t sound offensive at all.  

I should have imagined you missed my question because I saw, at your profile, that you answered some others since mine. (Quite impressive, by the way!)  Sorry for my misunderstanding.  ("Any thoughts?" is a GREAT advice, I am sure I will use it. Easy and Perfect!!)

Thanks for the compliment. I learned English for approx. 8 years and since then (about 10 years ago) I kept my reading. Novels, not only computing and... PURE MATH stuff (yep! PhD. still to conclude. Coincidence, isn´t it? :) )

Well, let´s go for the "essential", right ?!  ;)

> "Is your application open at the times you want to do the import?  Or could it be left open? Otherwise - it will have to be a scheduled task"

I will simply do the following: the user presses a button at the specific (protected) asp-page and the XML is generated at the server (this already works 100%). Then I would like the Access database to "grasp" the info from the XML right after it was "updated" (if possible) or, if not, at scheduled times...  I hope you got the picture. Oh... everything is in the server, by the way.

Thanks a lot for your good-will in helping me since the very beginning!  
Fábio.

P.S.: I found this (perhaps useful) snippet (below) at:  (The XML-based follows it)
http://groups.google.com.br/group/comp.databases.ms-access/msg/afce3d7ee49cfa5c?

DO you think that´s all (or part of what) I need ?

-------------------------------------------------------------------------------------------------------------------------------------
Sub AddWithDOM()
Dim doc As MSXML.DOMDocument
Dim nolPrinc As MSXML.IXMLDOMNodeList
Dim nolChild As MSXML.IXMLDOMNodeList
Dim nod As MSXML.IXMLDOMNode
Dim nodP As MSXML.IXMLDOMNode


Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset


  Set cnn = New ADODB.Connection
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & CurrentDb.Name & ";"
  Set rst = New ADODB.Recordset
  rst.Open "SELECT * FROM Table1", cnn, adOpenKeyset, adLockOptimistic


  Set doc = New MSXML.DOMDocument
  doc.async = False
  Call doc.Load("C:\SamplesTmp\portfolio.xml")
  Set nolPrinc = doc.selectNodes("/portfolio/stock")
  For Each nod In nolPrinc
    Set nolChild = nod.childNodes
    rst.AddNew
    For Each nodP In nolChild
      Select Case nodP.baseName
      Case "shares"
        rst!Shares = nodP.Text
      Case "symbol"
        rst!symbol = nodP.Text
      End Select
    Next
    rst.Update
  Next


  'Let's clean them up
  rst.Close
  Set rst = Nothing
  Set cnn = Nothing
  Set nodP = Nothing
  Set nod = Nothing
  Set nolChild = Nothing
  Set nolPrinc = Nothing
  Set doc = Nothing
End Sub


This Snippet use the portfolio.xml that is avaible at XML SDK


<portfolio>
   <stock>
      <shares>100</shares>
      <symbol>MSFT</symbol>
      <price>$70.00</price>
      <info>
         <companyname>Microsoft Corporation</companyname>
         <website>http://www.microsoft.com</website>
      </info>
   </stock>
   <stock>
      <shares>100</shares>
      <symbol>AAPL</symbol>
      <price>$107.00</price>
      <info>
         <companyname>Apple Computer, Inc.</companyname>
         <website>http://www.apple.com</website>
      </info>
   </stock>
   <stock>
      <shares>100</shares>
      <symbol>DELL</symbol>
      <price>$50.00</price>
      <info>
         <companyname>Dell Corporation</companyname>
         <website>http://www.dell.com</website>
      </info>
    </stock>
    <stock>
       <shares>100</shares>
       <symbol>INTC</symbol>
       <price>$115.00</price>
       <info>
          <companyname>Intel Corporation</companyname>
          <website>http://www.intel.com</website>
       </info>
   </stock>
</portfolio>



 
 
0
 

Author Comment

by:fskilnik
ID: 16274676

Ops!  Now I guess I understood your question!!!    Sorry!

Yes, the Access database may be "solicited" during the whole process. The same user or other (no more than 1 or 2) may be "consulting" the database while the XML could be asked by Access.  I never thought that this could be a problem but, sure, now I understand your worries!!  Well thought!

Thanks again,
Fábio.
0
 

Author Comment

by:fskilnik
ID: 16275038

Sorry for the 3rd message in a row, Mr. Purvis.

But I want to spare your time offering pre-ideas and asking your expert judgement on them...

Here is another link, with another approach, that perhaps could also be a good idea, I guess. The
only (very easy, it seems) modification I would certainly have to do is SQLServer to Access. Then I would ask to run this VBScript right after the XML is created but... what about the Access being read simultaneously... would be a problem?

Title: "How to import XML into SQL Server with the XML Bulk Load component"

Link: http://support.microsoft.com/?scid=316005

Well, I promise I will wait your answer for now, ok?!  :)

Thanks a lot,
Fábio.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16276346
XML Bulk insert is a very SQL Server ability.
Access' Import XML functionality is the closest thing to it.

The other link you posted, from CDMA, is manipulating XML at a substantially lower level - and definately more effort.

If we can get your XML file into a local table then importing it into your desired final table is always going to be pretty straight forward.

So if your file could appear at a certain point - then if you have your applications checking for it's appearance.
Once there import it - delete the file.

A hidden form with a Timer Event and interval checking for the existance of that file.
If it finds it - it moves it and begins the import.
(Moves it so that another application doesn't begin importing the same file).

The example XML import line earlier will get you the XML itself.
Checking for the file is easy enough too.

Do you need a few pointers on those ideas?
0
 

Author Comment

by:fskilnik
ID: 16276715
Great details, Mr. Purvis!

Let me see if I get the whole picture: when one of the (very few) users decides to update the stock quotes, he will press a button, the XML will be updated at the server with the most recent Yahoo stock quotes (till now already done) and we  will make this XML update a local Access database with a temporary XML.   That´s right?  If so, then how will we make the Access database at the server be updated?  

As far as the timer is concerned, you may consider that something will be updated every (say) 2 hours, during (say) a fixed period of 8 hours a day, only. Perhaps this could make everything easier. If so, no problem.

Yes, pelase, explain a bit more. And you may consider the idea you feel best the one that we will adapt. I am pretty sure I have no experience nor expertise to be able to measure possibilities at the level you are dealing with!!

Thanks a lot, Leigh!
Fábio.

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16276792
The time will need to run continuously - checking, say, every minute for the file's location.

If you have a new form.  frmCheck.  It'll always be opened like
Docmd.Openform "frmCheck", windowMode:=achidden

It needs a timer interval of 60,000.
And a Timer Event Procedure.

Dim strLocalFile as String
Const cXMLFilePath = "L:\Your Data Folder\XMLFileName.xml"

If Dir(cXMLFilePath) <> "" Then
    strLocalFile = CurrentProject.Path & "\TempLocal.xml"
    Kill strLocalFile
    FileCopy cXMLFilePath, strLocalFile
    Kill cXMLFilePath
    Application.ImportXML strLocalFile, acAppendData
    CurrentDb.Execute "INSERT INTO tblYourTable Field1, Field2, Field3 SELECT Field1, Field2, Field3 FROM TempLocal"
    CurrentDb.Execute "DELETE * FROM TempLocal"
End If

That's the idea of it.
See how you go.
I'm off now.  (Late.)
0
 

Author Comment

by:fskilnik
ID: 16280817

Marvellous, Mr. Purvis.

I will start implementing all this NOW!  :)   Please wait (say 2 or 3 days) about all this.

Thanks a lot and have a great weekend!

Fábio.
0
 

Author Comment

by:fskilnik
ID: 16303490

I´m back, Mr. Purvis.

I was able to use all your idea successfully (great!) locally (at the localhost) but... a small detail. I could not discover where should I put the  

DoCmd.OpenForm "frmCheck", windowMode:=acHidden

instruction, although I imagine why it is necessary when the dabase gets to work at the server.

Could you please help me once more just in this little small thing ??  Thanks a lot.

01) I will ask the moderator to change "B" for "A" , of course. Right now.
02) I will post the code I used (slightly different from yours, but based 100% in it) right after I put it all at the server and it proves to do as expected!

Thanks a lot,
Fábio.
0
 

Author Comment

by:fskilnik
ID: 16303787

Ops, I guess I found the right place to put this instruction, Mr. Purvis: right at the very beginning of the code...please confirm!   (For the interested readers, all of it is below.)

Thank you very much for your continuous support, Mr. Purvis. I have already asked the support community to change the grade to "A", by the way.

Cheers,
Fábio.

------------------------------------------------------------------------------------------------------------------------------

DoCmd.OpenForm "frmCheck", windowMode:=acHidden

Option Compare Database

'600000 in TimerInterval will give us 20min of delay between each checking for an updated XML document...

Private Sub Form_Timer()
Dim strLocalFile As String
Const cXMLFilePath = "E:\PATH_RELATED\StockData.xml"
If Dir(cXMLFilePath) <> "" Then
strLocalFile = "E:\PATH_RELATED\TempLocal.xml"
Kill strLocalFile
FileCopy cXMLFilePath, strLocalFile
Kill cXMLFilePath
DoCmd.DeleteObject acTable, "Stock"    <<---------------------  SAME NAME PRESENTED IN THE XML...
Application.ImportXML strLocalFile, acStructureAndData      <<---------  DECIDED NOT TO APPEND, BUT SUBSTITUTE
End If
End Sub
0
 

Author Comment

by:fskilnik
ID: 16303939

Ops, I got an error from the first line of the code...  :(

I will check this option below yet...

----------------------------------------------------------------------------------------------------------------------

Option Compare Database

Private Sub Form_Timer()
DoCmd.OpenForm "frmCheck", acNormal, "", "", , acHidden         <<------------------------ NEW TRY
Dim strLocalFile As String
Const cXMLFilePath = "C:\Inetpub\wwwroot\Websites\Skilnik\YahooData\StockData.xml"
If Dir(cXMLFilePath) <> "" Then
strLocalFile = "C:\Inetpub\wwwroot\Websites\Skilnik\YahooData\TempLocal.xml"
Kill strLocalFile
FileCopy cXMLFilePath, strLocalFile
Kill cXMLFilePath
DoCmd.DeleteObject acTable, "Stock"
Application.ImportXML strLocalFile, acStructureAndData
End If
End Sub
0
 

Author Comment

by:fskilnik
ID: 16306003

Nope!  It didn´t work. The problem must be I could not let the form opened, therefore the code didn´t "start" according to the timer contained in this form.

Please help Mr.Purvis !

Thanks a lot,
Fábio.

P.S.: the grade was changed!  :)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16306644
When your database is opened you'd launch the hidden form.
Whatever startup code you have.
Whatever form is first to load.
That would call the single line of code to load your hidden checking form.
It then sits there - running the checking code - watching - and waiting.
0
 

Author Comment

by:fskilnik
ID: 16310010

Hello, Mr. Purvis.

Thanks for the reply.

Good and bad news:

I managed to control Access "StartUp" and the frmCheck state following the EE question-answer:

>where and how would I open the hidden form?  
Tools menu: Startup, in the Display Form/Page combo box you'll need to choose a form to display when your application starts up (the Switchboard will work), and in that form's .Open event make the call to open your hidden form.

From the link:  http://www.experts-exchange.com/Databases/MS_Access/Q_21583832.html?query=Hidden+Access+Forms&clearTAFilter=true


And I guess I COULD manage to open the database via ASP from this EE question-answer:  

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21687146.html?query=open+Access+db+with+ASP&clearTAFilter=true

The problem: I´ve phoned my Web server and they do not have Access installed there, nor they permit to schedule procedures via Access...  

Anyway, I learned a lot from your very helpful answers. Now I will have to make it all from ASP directly to the database, via populating the XML to Access... (not the traditional other way round...)

Well, thank you very much indeed. It was really nice to see the Timer and StockData -- TempData transfer working at the localhost!  It is only a pitty I could not do it at the server...

Best regards,
Fábio.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16310120
I'm not surprised. You'll never get a ISP's web server running an Access database for you.  
That hadn't been clear to me in this question - that this wasn't your own Server.

Never mind - at least you've learned some new things.
0
 

Author Comment

by:fskilnik
ID: 16312890

> That hadn't been clear to me in this question - that this wasn't your own Server.

You are right. I should have mentioned it.  But... "Living and learning", isn´t it ?!

> Never mind - at least you've learned some new things.

Sure, Mr. Purvis!  I really liked it a lot. VBA is amazing and in few coding lines we could do very interesting things, no doubt.

See you in my next difficulties, I hope!   :)

Thank you very much,
Fábio.
0
 

Author Comment

by:fskilnik
ID: 16366342
Hi there, Mr. Purvis... GOOD NEWS!!   :)

I am back to leave my solution  :)  for the interested readers!

Cheers,
Fábio.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
There is an asp-coded EE solution from mikosha at
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21192124.html?query=<span%20class=

Based on that, I was able to create something directly related to my needs, although the table

StockID   //   FullTicker  //    ItemPrice  //    ItemDate  //   ItemTime
95              NYSE: ACN              31.67               3/14/2006              4:01pm
107            NYSE: AEA               14.05              3/14/2006              4:02pm
121            Nasdaq: AEOS          29.40              3/14/2006              4:00pm

presented at the postings above was slightly modified, as easily visible through the code below.  I will leave my solution to help others:

----------------------------------------------------------------------------------------------------------------------------------------

Set objConnection = Server.CreateObject("ADODB.Connection")
  objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"                   <<------------  For Access
  objConnection.ConnectionString = "PATH AND DATABASE.mdb"
  objConnection.open
 
 
Set ServerXML = Server.CreateObject("MSXML2.DOMDocument.4.0")
    ServerXML.resolveExternals = False
    ServerXML.validateOnParse = False
    ServerXML.async = False
    ServerXML.setProperty "ServerHTTPRequest", True

If ServerXML.Load("http://www.DOMAIN.com/DIRECTORIES/Something.xml") then

   If (ServerXML.parseError.errorCode <> 0) Then
   Response.write("error in parsing")

Else
       ' In XMLDestination you're gettinng collection of <item> elements using .selectNodes (as is now)
       'or .getElementsByTagName() .

Set XMLDestination = ServerXML.getElementsByTagName("Stock")

       ' Here will be two nested loops : outer loop for each member of XMLDestination collection and
       'inner loop for each member of XMLDestination collection .childNodes collection

       For Each Node In XMLDestination
     
             Symbol_value = "Symbol"
             Last_value  = "Last_value"
             Date_value  = "Date_value"
             Time_value  = "Time_value"
                  
             For Each Child_Node In Node.childNodes
         
                 If Child_Node.nodeName = "Symbol" Then            
       Symbol = replace(Child_Node.text,chr(39),chr(96))
                 'Response.Write Symbol & "<br>"
                 End If

                 If Child_Node.nodeName = "Last_value" Then       
                 Last_value = replace(Child_Node.text,chr(39),chr(96))
                 'Response.Write Last_value & "<br>"
                 End If
                         
       If Child_Node.nodeName = "Date_value" Then
                 Date_value = replace(Child_Node.text,chr(39),chr(96))
                 'Response.Write Date_value & "<br>"
                 End If
                         
        If Child_Node.nodeName = "Time_value" Then
                  Time_value = replace(Child_Node.text,chr(39),chr(96))  
                   'Response.Write Time_value & "<br>"
                   End If
             Next

strSQL = "insert into Stock(Symbol, Last_value, Date_value, Time_value) values(" & chr(39) &Symbol & chr(39) & "," &  Last_value &  "," & "#" & Date_value & "#" & "," & chr(39) & Time_value & chr(39) &  ")"
                         
              'Response.Write     strSQL & "<br><br>"
              objConnection.execute(strSQL)              
       Next
 End If

End If


0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question