Import xml data from Access

LiveCycle forms have the ability to connect directly to an Access DB.

1. In LiveCycle designer open up the "Data View" panel. If it's not visible near the Hierarchy panel, you can activate it from the Window menu item.
2. From the panel menu select "New Connection".
3. This activates a wizard that walks you through the process of setting up a connection between the form and the Access DB.
4. When the connection has be successfully created, all the DB table fields will be shown in the Data View Panel.
5. Connect a field to DB table through the field "Binding", which you'll find on the Object Panel.

This is the easy part. Selecting an actual record requires some scripting. I looked around this site and surprisingly there isn't much except some forum posts on the topic. You'll find more technical info at Adobe, but not much of a beginners guide. Search for "LiveCycle Data Connection"

Stephan Cameron, "The" LiveCycle Guru guys covers this topic pretty well on his blog. Connect a form to a DB"

I created an extensive set of training videos on this topic at the membership site: www.pdfscripting.com

What if I were to export the table from Access in XML format? I've gotten that too work. All the fields show up in my Data View. I have moved the ones that I want over to the Design View. Is it very simple to merge the two now?

Thank you,
Lee

Yes, that will works as well. Make the connection in your current form, then "Bind" the form fields to the fields in the connection. When the form is connected to an XML file, it's connected to a specific XML file. However, any XML file that has the same gramar can be "imported" into the form. So there are two ways to get data into the PDF.
1. Modify the original XML file, and new data should be loaded into the PDF when it is opened in Acrobat.
2. After the PDF is open in Acrobat a different XML file (but with the same grammar) can be imported.

It looks like I am doing everything right. I have followed your directions to a T, but when I open the new form in Acrobat, I just see my two fields and they are blank. It does allow me to enter data into the fields, but I am expecting the fields to be prepopulated with my values from the xml file. My ultimate goal here is to create a connection with the xml file and produce a pdf with multiple outputs because of the many different values I have for my two fields. I would then like to print these individual forms for mailing purposes.

Any idea why my fields would be blank? I know they are bound to the form, but I just can't get them to populate.

Thank you,
Lee

try importing the data from xml.

The problem with the XML might be that the connection isn't opening.

I'm sorry, I guess I should've ask why it might be happening, and how I might be able to fix it. The tutorials and even trying to find other resources on the web is really rather discouraging. It seems there really isn't any information out there regarding this topic (free that is). I would figure that this would be a very popular use of Adobe LiveCycle.

Any ideas on why the xml might not opening, and how I might be able to fix it?

Thanks,
Lee

You are correct, this information is poorly documented. I've written on particular aspects of it, and Stephan has also written about it on his blog, but there's not much else out there. Once of the reasons may be that this kind of connection is only useful on Acrobat Pro, on forms that are used locally. You can't very easily distribute a form with a data connection, because the data file has to travel with it. Not very efficient.

I did a test on this one and I was partially mistaken about how the XML connection works. I was under the impression that the XML acted as a data source, but it doesn't. Its just a template for the data model, so that data can be imported and exported to/from the form using a particular XML grammar.

Just try importing the xml data file. Change some data on the form and export to a new XML file name. You aught to be able to import the data from either file.

To hook up the form to a Data Source, which can automatically import data you'll have to create an OLEDB connection.

Thom,

Thank you so much. I was able to import the file and it entered the first record. I hate bug you again, but what will I need to do so that the pdf will read all of the records in the xml file. It looks like it is reading the first one. I expect i must have to put in a page break or indicate that there is a break somewhere in here, so that it can go to the new record? I am sort of expecting multiple pages, one for each record in the xml file.

Again, thank you for your help and patience,
Lee

First, the form has to be dynamic. Then there has to be a repeatable subform that matches the repeatable node in the XML. The names don't have to be the same, but the form node and the data node have to be bound.

A good way to see how this works is to create a new dynamic form with a repeatable subform. Fill in some data in the repeatable fields and export. You should be able to import the data into a new form, and see that a new repeatable subform is created for each repeated node in the XML file.

Thom,

Ok, I have finally got the data connection set with my Access DB. It is connecting, but I am running into the same issue, where only one record shows up.

I have gone and made the data connection, it is in a subform that is repeatable, there doesn't seem to be any setting that allows me to make the individual fields repeatable, but they are bound to the data connection.

The first record shows up but no other. I have changed the form properties so that the preview type is interactive, and to preview the adobe xml form as dynamic, the Server default is set to Render Dynamic format.
I have also saved it as a dynamic XML Form (*pdf).

Is there anything else that I could be missing?

Thank you,
Lee

I didn't know that you wanted repeated data in the form until your previous post. The Data Source (DB) connection pulls data one record at a time. A Script can be created to read the data into a repeated subform, but left to it's own devices, only the first record will load automatically. Scripting this is a slightly advanced topic.

On the other hand, importing data xml automatically handles repeated data, if the form is properly bound to the XML file structure. No scripting needed.

Thank you, I'm reverting back to the xml so that I can have all of the data generate their own forms. I got it to work with the xml file when I imported the data.

Thank you for your help,
Lee

Thom,

I am working on creating PDFs from Access DB, I have created template with LiveCycle and need to setup data connection. When I import the data in acrobat, only the first set of data shows up.

Looks like I am in the same situation as Lee, but I am a beginner to LiveCycle and I don't quite understand what you are talking about in your last reply, could you explain further with details?

Thank you very much.

Sylvia

A database connection in a LiveCycle form is to a single table in the DB. The data connection returns data to the form one record at a time. For example, say the form has fields for First Name, Last Name, and Phone Number and those fields are bound to Columns in the DB connection. Then, by default,those fields will be filled with data from the first record in the Database when the LC form is opened in Acrobat. If you want something else to happen, you'll need to write a script to control the DB connection.

For example, say it's an order form and there is a dynamic table with lines for each item ordered. You want the lines on the form filled with data from every record that contains an ordered item. This is standard relational DB stuff. You would need to write a script that loops over all records in the DB connection and then copies the data into the dynamic LC table, creating a new line for each record.

Alternatively, you could export the data from Access as an XML file. Then, in the form, create a data connection to XML grammar used by the exported data and then bind the form fields to the proper nodes in the XML. Now when you import the xml file into the form, Acrobat will automatically create the new lines in the table and populate them with data from the xml file.

I am new to the forum, but have used Adobe Professional 8 and LiveCycle designer for approximately 2 years. I have a form that I want to import records from an MS Access database. I have problems connecting directly to the database, so I have exported the table to XML. When exporting, it only brings in the first record of the table.

Today, I tried copying the table over to Excel, then making this an xml file. I succeeded in creating a multiple record xml file. When I connect the adobe form in LiveCycle designer to the xml table, it only brings in the first record. How do I display the next and subsequent records?

I do not know how to do scripts, so the solution would have to be with commands through LiveCycle.

Any help would be greatly appreciated.

When you say you want to bring in the other records, what exactly do you mean? Do you want them displayed in a table? or do you want buttons that navigate between records?

I want to populate the form for one set of records, save the form, then go to the next set of records and populate the form again. It is for employee performance evaluations. Each employee that needs the evaluations has a set of records (name, hire date, etc). Once the form is populated with this, I want to save the file then populate the next set.

Thank you for your help.

Ahh, you want to do a variable data operation. The only solution is with a script, and it's not simple. You'll need to learn scripting, hire someone, or purchase a tool.

You can find such a script here:Variable Data Print Tooland information about writing such scripts here:
http://www.pdfscripting.com/public/34.cfm#XFADBForms

and here:
http://www.pdfscripting.com/public/department48.cfm

But it's only available to members.

Thank you for the information. i will have to check this out.

On the other hand, is their a way to change an xml file (or csv file) into a dataset for pdf? I can change a pdf dataset into a csv and consequently into an xml, but I haven't found a way to do the reverse. If this was possible, I could publish the document and bring the data in that way.

I currently have Adobe Professional 8, but will soon have Adobe Professional 10--which I hear is a lot more user friendly than 8. Will that give me any more flexiblity for issues of this kind?

The problem is that there is no automatic way for handling multiple datasets that is built into Acrobat. There are ways, several of them, but they all require scripting. Acrobat imports a single data set very nicely. So if you could write out individual XML files for each record in the data set, then you could import them one at a time into the form.

That is what we currently do. We export one record at a time from Access to xml, then import this into the PDF form. I was just looking for a more efficent way of importing them.

Thank you so much for your time and help.

Check out http://www.FDFToolkit.netFDFToolkit.net can import, export, merge, parse, populate, XML, ADO.NET(ACCESS/SQL), XDP, FDF, and XFDF data formats.

FDFToolkit.net requires Microsoft .Net programming knowledge.