Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Importing XML files into Access 2007 DB

    Hi... I'm trying to import data that has been exported from a pdf form into an XML file into an Access database. I've not had much luck getting it to work programatically. (The get external data feature works fine but we have many of these files to import every month).

    I did find the following code on the web but it doesn't do anything when I run it. I don't really care if I use accAppendData = 2 or 1 as long as it works.

    Private Sub cmdImportXMLFile_Click()
    Const acAppendData = 2 'Imports the data into an existing table
    'Const acStructureAndData = 1 'Imports the data into a new table based on the structure of the specified XML file.
    Dim stImportFileName As String

    Dim Response As String

    On Error GoTo HandleErr

    If IsNull(Me.ImportFileName) Then

    Response = MsgBox("Please select a file to import.", vbOKOnly)
    GoTo ExitHere
    End If

    stImportFileName = Me.ImportFileName


    Set objAccess = CreateObject("Access.Application")

    objAccess.OpenCurrentDatabase "C:\Access Apps\ExpensesTracking.accdb"

    objAccess.ImportXML stImportFileName, acAppendDataAndStructure


    ExitHere:

    Exit Sub

    HandleErr:

    MsgBox "Error " & Err.Number & ": " & Err.Desc, vbCritical, "cmdImportXMLFile_Click in Form_frmMenu"
    End Sub



    I've attached two pdf's... one is the xml file and the other is the tables in Access. I'm only interest in the <details> in the pdf and importing it into the MSAccess 'details' table. Does anyone have any suggestions?
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Has acAppendDataAndStructure been defined? You use it in the ImportXML line of code.

    Why are you opeing another Access database from the one you are executing?

    I have done this succesfully in Access2003 some years back but from the same database i was in.
    I will check on that program of mine and let you know, unfortunately it's at home and i am at work at the moment.

    It would help if you provided the xml file as opposed to a pdf of it.
    Last edited by patt; 2011-05-12 at 04:44.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    I have previously imported data from a straight XML file into Access (as I read it, this is what you are trying to do?). I had to hunt all over for a means of parsing the data into my Access tables, but got there in the end, and it's now run on a daily basis. Like Patt, I'm at work and don't have the relevant file handy, but will keep an eye on this thread, and if you want more information I'll be happy to provide it.

    Cheers

    Alison C

  4. #4
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I've not had any experience with importing XML before. I've imported data from csv and excel file for numerous applications but never XML. If you can assist with some basic code that will import the <details> portion of the xml file, that would be great.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Your code looks ok except where i outlined.

    Would you send an xml file so i can import it into my database.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Sample

    Hi SueP -

    I'm sooo sorry - my notifications don't seem to be coming through, and I didn't realise you'd responded. The attached file is the code I used to import the XML data. I've had to change a few variable and field names because it was very specific, so hope I've been consistent with my changes.

    If I can find the original page that helped me to do this, I'll post the URL. I looked for weeks, and found nothing, and then suddenly one new page came up in the search I'd done a dozen times before, and it was like changing from a minor to a major key in a piece of music! Daylight dawned, and it all seemed relatively straightforward. This post might only go partway to that point for you, but I'm hoping it'll give you something to work with.

    Meanwhile, this link might help
    http://msdn.microsoft.com/en-us/libr...ffice.10).aspx


    Cheers

    Alison C
    Attached Files Attached Files

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Hi Sue, would you send the XML file (as a txt file) you are trying to import.

  8. #8
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Patt.... here's the file

    Thanks
    Attached Files Attached Files

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Hi Sue,
    I have included a database that successfully imports your xml file.
    Attached Files Attached Files

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Well, how did you go Sue? Have you solved your problem?

  11. The Following User Says Thank You to patt For This Useful Post:

    SueP (2011-06-08)

  12. #11
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi... sorry I didn't get back to you... been away from the office for a bit. Yes it worked and the data is importing very smoothly.

    Thanks so much for your help.

    Have a great day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •