none
ExportXML From Access RRS feed

  • Question

  • I am trying to export a single record from Access 2010 to an xml file which will be used to populate a pdf document.  I have made this work from the menu in access, which has a checkbox for "Current Record", but I need it to work from a button with code.  I am using, or trying to use, the ExportXML Method [Access 2003 VBA Language Reference] that I found.  How do I click that checkbox in the code?

    The MSDN shows the syntax to be:

    expression.ExportXML(ObjectType, DataSource, DataTarget, SchemaTarget, PresentationTarget, ImageTarget, Encoding, OtherFlags, WhereCondition, AdditionalData)

    I was sure I had found what I needed when I saw the "WhereCondition" argument in the syntax.  The condition I want to use is where the ID, which identifies the same record in all tables, equals the ID of the record I am looking at on the form.  I want to export a single record to the xml file giving it the name of the customer.  The ID number is contained in the variable "RcdKey".  How can I get the code to export a single record instead of all records in the database?  Is there a better way than what I am trying.

    Thanks for your help.

    Here is the code for the button:

    Private Sub Command170_Click()
    Dim RcdKey As String
    Dim Name As String

    RcdKey = Form_Customer_Info.ID
    Name = Form_Customer_Info.Legal_Name

    MsgBox (RcdKey)
    MsgBox (Name)
     Dim objAD As AdditionalData

       ' Create the AdditionalData object.
       Set objAD = Application.CreateAdditionalData

       ' Add the related tables to the object.
       With objAD
          .Add "Vehicles"
          .Add "Drivers"
          .Add "Policys"
          .Add "Prior Carriers/Losses"
          .Add "Trailers"
          .Add "VSF"
          
       End With

       ' Export the Orders table along with the addtional data.
       Application.ExportXML acExportTable, "Customer_Info", _
           "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xsl", Wherecondition: ID = RcdKey, AdditionalData:=objAD

    End Sub

     

    • Moved by Kee Poppy Thursday, September 29, 2011 3:38 AM (From:Visual Basic General)
    Tuesday, September 27, 2011 8:14 PM

Answers

  • Thanks for that Sergey.

    When I pasted that into the VBA code the DataAdapter line was red with no explanation of what to do to fix that.  I do wish I could find a way to use Visual Studio as the code editor when I click "view code" from within an Access database.

    After pontificating a while longer, I made these changes to the existing code and it worked.

    Private Sub Command170_Click()

    Dim Name As String
    Dim DataAdapter As Object

    Name = Form_Customer_Info.Legal_Name

     Dim objAD As AdditionalData

        'Create the AdditionalData object.
       Set objAD = Application.CreateAdditionalData

        'Add the related tables to the object.
       With objAD
          .Add "Vehicles"
          .Add "Drivers"
          .Add "Policys"
          .Add "Prior Carriers/Losses"
          .Add "Trailers"
          .Add "VSF"
          
       End With
       
       RcdKey = ("ID=" + CStr(Form_Customer_Info.ID))
     
       ' Export the Customer_Info along with the addtional data.
         Application.ExportXML acExportTable, "Customer_Info", _
           "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xml", "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xsd", _
           "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xsl", Wherecondition:=(RcdKey), AdditionalData:=objAD
       
    End Sub

    On to importing the new xml to the pdf form.

    "I'll (probably) be back"

     

    This is the first time I have posted to the forums.  I will try to find out how to mark this as solved but if I don't, will someone tell me how?

     

    Thanks again!!!

     

     


    JMC
    • Marked as answer by bndmc Tuesday, September 27, 2011 11:42 PM
    • Unmarked as answer by bndmc Wednesday, September 28, 2011 12:37 AM
    • Proposed as answer by danishaniModerator Monday, November 28, 2011 11:31 PM
    • Marked as answer by danishaniModerator Wednesday, January 11, 2012 8:25 PM
    Tuesday, September 27, 2011 11:42 PM

All replies

  • You can compose and run SQL query for a single record from the database table. This is working code:

     

      DataAdapter = New OleDb.OleDbDataAdapter(("SELECT <columns> FROM <table> WHERE <condition>), connString)
    
                Dim DBTable As New DataTable
    
               
                DBTable.Locale = System.Globalization.CultureInfo.InvariantCulture
    
                DataAdapter.Fill(DBTable)
    
    	'Write data  to XML file:
    
                DBTable.WriteXml(".\DataToXML.XML", True) 
    This code snippet can export entire database table but you can query for just a single record.

     

    Tuesday, September 27, 2011 8:46 PM
  • Thanks for that Sergey.

    When I pasted that into the VBA code the DataAdapter line was red with no explanation of what to do to fix that.  I do wish I could find a way to use Visual Studio as the code editor when I click "view code" from within an Access database.

    After pontificating a while longer, I made these changes to the existing code and it worked.

    Private Sub Command170_Click()

    Dim Name As String
    Dim DataAdapter As Object

    Name = Form_Customer_Info.Legal_Name

     Dim objAD As AdditionalData

        'Create the AdditionalData object.
       Set objAD = Application.CreateAdditionalData

        'Add the related tables to the object.
       With objAD
          .Add "Vehicles"
          .Add "Drivers"
          .Add "Policys"
          .Add "Prior Carriers/Losses"
          .Add "Trailers"
          .Add "VSF"
          
       End With
       
       RcdKey = ("ID=" + CStr(Form_Customer_Info.ID))
     
       ' Export the Customer_Info along with the addtional data.
         Application.ExportXML acExportTable, "Customer_Info", _
           "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xml", "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xsd", _
           "C:\Insurance\P&C\temp\test\" + (Name) + "Customer_Info.xsl", Wherecondition:=(RcdKey), AdditionalData:=objAD
       
    End Sub

    On to importing the new xml to the pdf form.

    "I'll (probably) be back"

     

    This is the first time I have posted to the forums.  I will try to find out how to mark this as solved but if I don't, will someone tell me how?

     

    Thanks again!!!

     

     


    JMC
    • Marked as answer by bndmc Tuesday, September 27, 2011 11:42 PM
    • Unmarked as answer by bndmc Wednesday, September 28, 2011 12:37 AM
    • Proposed as answer by danishaniModerator Monday, November 28, 2011 11:31 PM
    • Marked as answer by danishaniModerator Wednesday, January 11, 2012 8:25 PM
    Tuesday, September 27, 2011 11:42 PM
  • Thanks for that Sergey.

    When I pasted that into the VBA code the DataAdapter line was red with no explanation of what to do to fix that.  I do wish I could find a way to use Visual Studio as the code editor when I click "view code" from within an Access database.

     

    Of course, you have to modify the code for your needs. At least you have to add 
    by yourself the Imports needed. Anyway, i just proposed a solution, it it your decision what to choose.

    Wednesday, September 28, 2011 6:12 AM
  • bndmc

    Be aware this is not a VBA forum.

    Try this one instead of this Visual Studio for .Net forum

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

     


    Success
    Cor
    Wednesday, September 28, 2011 6:29 AM
  • Thank you, Cor. Of course my code snippet will not work in VBA. But if i see the question in VB.NET forum, i (obviously, wrongly) decide that OP asks for VB.NET solution. Not VBA,, not VB 6.0, and even not C++. I have to get used to filter such posts.
    Wednesday, September 28, 2011 12:44 PM