Connecting a Form to a Database

In response to “Lala”:http://blogs.adobe.com/formbuilder/2006/08/importing_data_in_acrobat_1.html#comment-28257 and “malik”:http://blogs.adobe.com/formbuilder/2006/08/importing_data_in_acrobat_1.html#comment-29712’s questions on connecting a form to a database (whether it’s Microsoft Access, MySQL, etc. doesn’t really matter), I decided to write a little tutorial on how to do it.

Even if you already know how to do it, I encourage you to *pay special attention* to the section on *Auto-Incremented Table Columns* because it might help you understand and resolve some of the issues you may have already run into.


h2. Create a System DSNFirst, you need to create a System DSN for your database using the ODBC Data Source Administrator Windows tool.== ODBC Data Source Administrator ==That will let Acrobat interface with your database when your form is opened in Acrobat in order to be read and/or filled. Because of the DSN, it doesn’t matter what kind of database you need to connect to.For this tutorial, I’ll be using my FormBuilder database which contains the same kind of movie information found in the XML Data files from various other tutorials I’ve already posted:”Download Movie Database [sql]”:http://blogs.adobe.com/formbuilder/samples/DataBinding/FormBuilderDB20060927.sqlThis SQL file will create the database and a user if you’re using something like MySQL (for which you can also download a “free ODBC driver”:http://dev.mysql.com/downloads/connector/odbc/3.51.html). You should also be able to easily tweak it to create tables in a Microsoft Access database if that’s what you want to use.h2. Make a New Data ConnectionThe second thing you need to do is create a data connection in Designer: From a new or existing form, open the Data View palette (you can use the “Window | Data View” menu item to open it) and choose “New Data Connection” from the palette’s fly-out menu.== Data View Palette Fly-Out Menu ==In the “New Data Connection” wizard, pick “OLEDB Database” from the first screen, using the “Build” button on the next screen to open the “Data Link Properties” dialog, go to the “Connection” tab and pick the name of the DSN you created in the first step from the first drop down list. Then click the “Test Connection” button to make sure a connection can be established to the database via the DSN.== Data Link Properties Dialog ==Click on OK to close the “Data Link Properties” dialog and return to the “New Data Connection” wizard. Now that you’ve chosen a DSN, you’ll be able to specify the resource within that DSN to which the connection should be made: You may either pick a Table from the list, specify a Stored Procedure or specify an SQL Query. If you’re connecting to a single table, you may be able to simply pick its name from the list of tables. If you need to connect to multiple tables in the same data connection, then you’ll need to use a Stored Procedure or an SQL Query.== Table Or SQL Query ==h3. Auto-Incremented Table ColumnsIf the table you’re wanting to connect to contains any *auto-incrementing columns*, you must use the “SQL Query” option instead of simply choosing a table name from the “Table” option. If you pick a table with an auto-increment column, you’ll be able to read from it but you’ll get errors when you try to push data into it. If this is the case, write an SQL Query that selects all columns in the table except for those which are auto-incremented. In the image above, I chose “SQL Query” because the “movie” table I’m connecting to has an auto-incrementing column named “id” that needs to be excluded from the data connection.h2. Bind Fields to Data Connection NodesAt this point, you should have a new data connection listed in the Data View palette which contains a list of “nodes”, one for each column in the table(s) you picked while setting-up the data connection:== Data Connection in Data View Palette ==The next step is to create fields to represent each node in the data connection and _bind_ each field to its respective data node. The easiest way to do this is simply to drag & drop the nodes from the data connection onto your form. This is handy for two important reasons:# The Data View palette has inspected the definition of each node and pre-determined the best type of field to use in order to edit its data.# When you drop the nodes onto the form, the fields that are created are automatically setup to be bound to their respective data nodes.The database I’ve connected to is one that uses the Movie Data I’ve used in previous tutorials. In this case, I’ve connected to the Movie table’s “title” and “showTime” columns. Since the “title” column is described as VARCHAR, the Data View palette figured it should be a text field. As for the “showTime” column, described as TIME, it’s set to be a date/time field with its Data Format property preset to _Time_.After you’ve completed this step, the Data View palette now shows the data nodes in the data connection as “bound” with special icons:== Fields Bound to Data Nodes ==h2. Add Control ButtonsThe last step in this process consists in adding a set of controls to manipulate the records in the database obtained via the data connection. The simplest way to do this is to use a set of buttons where each is assigned one of the following statements (each statement is one line and provided in FormCalc):bc. xfa.sourceSet.{DataConnectionName}.first()xfa.sourceSet.{DataConnectionName}.previous()xfa.sourceSet.{DataConnectionName}.next()xfa.sourceSet.{DataConnectionName}.last()xfa.sourceSet.{DataConnectionName}.addNew()xfa.sourceSet.{DataConnectionName}.update()xfa.sourceSet.{DataConnectionName}.delete()xfa.sourceSet.{DataConnectionName}.cancel()where _{DataConnectionName}_ should be replaced by the name you gave to the data connection you created earlier (“DataConnection” by default).Each statement above represents a different action to take with the data connection: Move to the first, previous, next or last record, add a new record, update or delete the current record and cancel changes to the current record, respectively.== Data Connection Control Buttons ==Note that the “first”, “previous”, “next” and “last” statements *imply an “update”* by default which means that if you simply usebc. xfa.sourceSet.{DataConnectionName}.next()to move to the next record and the user has made changes to the current record, those *changes will be committed* prior to moving to the next record. If you want those navigation controls not to commit changes (and therefore require the user to explicitly click on the “update” button in order to apply any changes to the current record), you must specify the _cancel_ statement prior to the _next_ statement:bc. xfa.souceSet.{DataConnectionName}.cancel()xfa.souceSet.{DataConnectionName}.next()To help you do this quicker now and in the future, here’s a Library Object Snippet that you can place into the Custom tab of the Library palette (you’ll have to save the XFO file to the following folder on your system: C:\Documents and Settings\{userid}\Application Data\Adobe\Designer\en\objects\custom where _userid_ is your windows user id).”Download Data Connection Controls Snippet [xfo]”:http://blogs.adobe.com/formbuilder/samples/DataBinding/DataConnectionControls.xfo*Minimum Requirements:* Designer 7.0, Acrobat 7.0.Once you place the file in the folder indicated above, you’ll then have a new object in the Custom tab of your Library palette named “DataConnectionControls”. Simply drag the object onto your form and the buttons will appear, all pre-configured and ready to go.h2. Run Your FormNow that the DSN, data connection, fields, bindings and navigation controls have been setup, you should be able to preview your form in Acrobat Pro and see the first record in the database table(s) pre-loaded into the bound fields.== Form Loaded In Acrobat ==If you’re having problems getting this going, you can check-out my form (assuming you’ve created the FormBuilder database and a DSN for it) to see if you missed any steps:”Download Sample [pdf]”:http://blogs.adobe.com/formbuilder/samples/DataBinding/ConnectFormToDB.pdf*Minimum Requirements:* Designer 7.0, “Acrobat Standard 7.0”:http://blogs.adobe.com/formbuilder/2006/08/importing_data_in_acrobat.html.====~*Updated:* October 17, 2006~

61 Responses to Connecting a Form to a Database

  1. Gar says:

    Two scripts failed, delete and update. Error message for both: “Empty row cannot be inserted. Row must have at least one column value set.”Suggestions on how to fix this?

  2. Dwight Beech says:

    I am trying to create a form that links to an Access database. I am using the library.custom.data drop down list object. I drop it on the palette and then from the data viewer drag and drop the field on top of the DD list field. The data file is only about 100 records and when I test the form and click on the drop down list field it only shows the first record. In fact that is all it does and I do not get the list of items to choose from.What am I doing wrong?

  3. Gar,It sounds like you’re trying to do an “addNew” to add a new record to the table and then immediately doing an “update”. The call to “update” with no data may violate a “not null” rule set on one of the columns in the table the data connection is editing.Try to specify data for all fields prior to calling “update”.As far as getting a message stating that an “empty row cannot be inserted” when calling “delete”, that sounds a little strange because you’re trying to remove an existing record, not insert a new one.

  4. Dwight,The “Data Drop Down List” object provided in the Library palette’s Custom tab is designed to load all records from a data connection using a data node for the item text and another for the item value.By dragging the data node onto the Data Drop Down List from the data connection in the Data View palette, you’re simply creating a binding between the Data Drop Down List (which is essentially a simple drop down list with some script in its Initialize event) and the data node in the data connection. The default behaviour of data bindings is to load the first record on form load in Acrobat.What I suggest is to set the Data Drop Down List’s Default Binding property to Normal using the Object palette’s Binding tab and then modify the Initialize script so that it works with your data connection.If you open the Script Editor palette (use the Window menu to locate it) and select the Initialize event (with the Data Drop Down List object selected on the canvas), you’ll see the script that’s used to load all values from specified columns in a specified data connection. Near the top, you’ll see the following statements:var sDataConnectionName = “<value>”;var sColHiddenValue = “<value>”;var sColDisplayText = “<value>”;The idea here is to change the “<value>” entries with information that pertains to the data connection you’ve created. For instance, if it’s called “MyDataConnection” and contains an ID and a Name column, you might do the following to have the drop down list automatically populated with items which show the Name and have the ID set as their value:var sDataConnectionName = “MyDataConnection”;var sColHiddenValue = “ID”;var sColDisplayText = “Name”;Another solution — which doesn’t required any scripting, by the way — is to use the Dynamic Properties feature of Designer 7.1 to automatically populate a regular drop down list’s item list with values from a data connection.

  5. Ricardo Malveira says:

    Hi Stefan,The Data Connection Controls are excelent… Do you have a way also to go straight to a record, possibly using a drop down box, where we select a record and every other field is updated acordingly?… Cheers.

  6. Newbie07 says:

    Hi Stefan,I am Newbie07 from the adobe designer forum and thanks for this turorial. All the controls, except for addnew() are working for me. I do not get any error while inserting the data, but the data never get inserted into the table.

  7. Ricardo,I figured the answer to your question was best explained in detail with a sample. Check-out my latest post on Selecting Specific Database Records. Note that you could set the SQL statement to extract a single record from the data connection, thereby achieving the functionality you were seeking.

  8. Newbie07,What exactly is happening when you attempt to use the xfa.sourceSet.{DataConnectionName}.addNew() function? Do you get an error message?Have you looked at Designer’s Report palette while previewing and testing your form to see if any warnings/errors are listed there that may be related to updating/adding new records in the database?

  9. Ricardo Malveira says:

    Hi Cameron,Thank you for your answer. It is awesome on the 2 data links.But based on the solution Above “Connecting a form to a database”, I was wondering a solution to go straight to the record wanted, instead of going one by one through all the records. Something like a drop down box that once you choose the record wanted, all the other fields on the form, connected to the database show their values related to the chosen record on the form.Cheers.

  10. Ricardo,The way data connections to ODBC data sources work in XFA is that you’re expected to iterate through all records or display (report-style) all records at once. The only way to show/update only one specific record at any given time is to specify a very specific SQL query that produces zero or one records for the data connection.What I’ve done with my “Selecting Specific Database Records”:http://blogs.adobe.com/formbuilder/2006/09/selecting_specific_database_records.html tutorial is show you how to change the SQL statement used by a data connection dynamically (i.e. at run-time with respect to other user selections).The idea would be to follow that tutorial and allow the user to select enough information in order to build an SQL query that would produce one record and then update the second data connection’s SQL query such that only that specific record is subsequently available for viewing/editing in the form.

  11. Bill East says:

    I need to use XML files instead of a database. I have a load data function and record navigators. Is there an equivalent for the Add/Delete Record buttons for XML file data?

  12. yuda says:

    How to add data within two tables. I have usexfa.sourceSet.DataConnection.addNew()in a AddNew button. but this only adding just a table. I’ve also make a New Data Connection “DataConnection2” to the second table.How to solve this problem?

  13. Bill,XML Data files aren’t edited in the same way as database tables are via data connections. As such, the concept of the add/remove buttons doesn’t really apply.What happens is that you define a data connection based on a schema to which an XML Data file you load adheres. At this point, the data is loaded into the form and the form must use dynamic subforms and Instance Managers to create new instances (records) or to remove them. When the user is done modifying the form’s data, the data can then be exported via the same data connection to a new XML Data file (via HTTP or email). This new exported XML Data file would then contain the data in the state which it was at the moment it was exported from the form (including any modifications to subform instances — records — that might have been made).

  14. Yuda,While it’s possible to define multiple data connections to ODBC data sources (typically database tables), only one may be active at any given time.If you need to update data in two tables concurrently, I would suggest that you use an SQL statement as the definition for the data connection. This way, you can define an SQL statement that joins two or more tables together and cause data to be updated in multiple tables with a single active ODBC data connection.

  15. Jan says:

    You say:”You should also be able to easily tweak it to create tables in a Microsoft Access database if that’s what you want to use.”Sorry, I don’t know how I create a Access DB with your SQL file, have a tip for me?

  16. Jan,Unfortunately, I’m not an Access expert so I can’t give you a definite answer or procedure to follow but I do know that while Access supports SQL, it either doesn’t support the entire language set or doesn’t expose most of it by default.I was able to find information on Create Table queries in Access which you may find useful in translating the code in the SQL file I provided into code that can be interpreted by Access in order to create the various tables you need.

  17. Dwight Beech says:

    Hi:I am having a problem understanding how I can update a file in an Access database.The issue is that I want to collect info from the form that is partially populated by tables in the Access database. I then want to post to the Access database but in a different file that I was using to populate the form. I am collecting expense data and wish to have it contained in a file in Access.How can I achieve this by using thexfa.sourceSet.{DataConnectionName}.addNew().Thanks,Dwight

  18. Bob Samagalsky says:

    I just started using Adobe LiveCycle designer to create an online competency test for a client. Everything is working well thus far with respect to filling out the test and emailing back to me for automotic grading using the XML data that has been submitted via email.My client at this point has a concern regarding the PRINT option that is made available to the individual sending his/her results. My client does not wish to have the PRINT option available.How can I create an “email to:” dialogue that only has the options for “Send data” or “Cancel” ??Your advice would be most appreciated.Regards,Bob

  19. Dwight,The key here is to use multiple data connections. The very minimum would be two: one to read/gather the partial information you need for filling the form and another to write the data you’ve collected.This is basically the same thing I’ve done in my sample on selecting specific database records.Designer supports multiple data connection definitions to multiple ODBC data sources but only one connection can be open (allowing you to read from or write to the tables that it references) at any given time.The trick then is to open the “read only” data connection when your form is initialized in order to populate the fields with the partial information you need and then close the data connection and open the second (“write”) data connection so that when the user enters data and clicks on the “add” button, the call toxfa.sourceSet.WritableDataConnection.addNew()will take the data from the fields bound to the data nodes in the WritableDataConnection and commit it to the tables to which they pertain.

  20. Bob,If I understand correctly, you’re basically wanting to by-pass the intermediate dialog that opens when the user clicks on an email submit button where the user must choose their email client or print the data.This dialog can be by-passed by programmatically causing the data on the form to be submitted.When the user clicks on an actual email submit button to submit the form’s data, Acrobat sees that as being a result of a user action (clicking on the button) and displays the intermediate dialog.If you were to place a regular button on the form, make the actual email submit button invisible and then, in the regular button’s Click event, cause the email submit button’s Click event to be executed, you would then cause the form’s data to be submitted programmatically, resulting in a new email message with the data as an attachment to be displayed, having by-passed the intermediate dialog.To execute the email submit button’s Click event from another button’s Click event, simply use the following script:EmailSubmitButton.execEvent(“click”)where EmailSubmitButton is the name of your invisible email submit button.

  21. Kevin Williams says:

    Stefan,I am going nuts with this. I am trying to connect my form to my MSSQL 2000 database. I have all the dsn’s set up and connection test is fine. When I try to preview the form I get the following error: “connection for source “” failed because the environment is not trusted.” I have googled the tar out of this error and can’t seem to get a straight answer. You seem to know what you are talking about. Any suggestions? I realize that this is more focused on Access, but I thought I would give it a try. Thanks,Kevin WilliamsIT ManagerQuantell, Inc.

  22. Kevin,It’s difficult to figure-out what the problem is here. I’m thinking it could be one of two things:You’re trying to do basically what this post’s sample does (browse records from your MSSQL 2000 database on a form) but you’re using Designer 8.0 and Acrobat 8.0. In this case, your problem could be related to attempting to modify the SourceSet model directly instead of using a cloned version.(Shot in the dark…) Your current system environment (i.e. the user under which Acrobat is being executed) doesn’t have permission to access the DSN. For example, if you’re previewing the form in Designer, then Acrobat actually thinks it’s running in a browser which means it may be running under a lowered-permissions user (like IUSR_{username} — the Internet Guest Account) which may not have permission to access the DSN.I have yet to run into #2 myself so hopefully #1 fixes your problem.Let me know how it goes.

  23. Kevin,I did a little more investigating and it sounds like you may have run into a bug in Acrobat 8 where it won’t trust a local DSN in a form even if the form has been certified and is trusted to execute highly-privileged JavaScript (to connect to the DSN).That’s not to say that you can’t get a form to connect to a local DSN in Acrobat 8. It just seems that in certain circumstances (which, unfortunately, I can’t pin-point), Acrobat will fail to trust a local DSN connection.It’s possible that it may something to do with the way the connection string is specified in the XML Source view. Amongst other things, it should start with “Provider=MSDASQL” and have the string “Data Source=” somewhere into the string. You can find this information by going to the XML Source view and looking for the <sourceSet> node about 3/4 ways down.Also, someone else asked me about the same error message just recently and they seem to have gotten around the problem by setting some data bindings to “Normal” instead of “None”. I can’t say I would personally have ever thought to try that but they apparently found the answer on the Designer Forum.

  24. katrina Cowling says:

    I have setup a data connection and some formcalc script in a form created in designer 7 which works fine in preview mode, but I get the following error when opening the form using Reader 7.Error: accessor ‘$sourceSet.DataConnection2.#command.query.commandType’ is unknown.I have used the following script from Adobe Designer Help:$sourceSet.DataConnection2.#command.query.commandType = “text”$sourceSet.DataConnection2.#command.query.select.nodes.item(0).value = Concat(“Select*from BRANCH_IN Where branch_number = “, TextField8.rawValue,””)$sourceSet.DataConnection2.open() Have I missed a step? I have checked my Data Connection setup after reading previous posts, and it looks fine.Thanks,Katrina

  25. Claudia says:

    I just started using Adobe LiveCycle Designer. My first problem is that I can´t insert a new record to my database. I use following script:xfa.sourceSet.Datenverbindung_output.addNew();Filling in new dataxfa.sourceSet.Datenverbindung_output.update();Update()[existing record], First(), Next() are working fine. I do not get any error while inserting the data, but the data never get inserted into the table.Thanks,Claudia

  26. Katrina,By default, the Adobe Reader is not capable of importing data. Forms that need to do this in Reader need to be Reader-Extended to allow data import. I’m guessing this is why you’re seeing the error in Reader and not when previewing the PDF in Designer.When viewing the form in Preview Mode, you’re probably using Acrobat Pro which is capable of importing data by default.Acrobat Pro is capable of Reader-Extending forms to enable certain capabilities on a certain number of forms. You may be able to use it to enable data import in your form when it’s viewed with Reader 7.

  27. Claudia,What you’re describing is a typical problem that occurs when the record being updated (after being inserted) in the database includes an auto-incremented cell.Please see the Auto-Incremented Table Columns sub section in this post in case this might be the problem.If it is, you’ll most likely need to specify an SQL Query instead of simply picking a table from the database or you may have to narrow your query (if you had already specified one).

  28. Dave says:

    Just wanted to drop a quick note of praise.I know how rare it is to get positive feedback, and this post covered everything I needed to know.Thanks!

  29. Jin-oh Choi says:

    I am trying to pre-populate an Adobe form with SQL Data, but am getting the error of “Connection for Source DataConnection failed because the environment is not trusted”Is there a way to do this without using a System DSN as I do not wish to setup a local System DSN for each user.Thanks in advance,Jin-oh Choi

  30. DW says:

    I have been following your tutorials and they work quite well. I have a need for users to open a designer pdf form from the internet, fill it out, transmit the data so that it populates a database, then print the form and mail it in. Could you put together such a tutorial or do you know where one exists? Thanks.

  31. Rick says:

    I have been trying to make FormBuilder with the record navigation buttons work with a MS Access 2003 DB.When I run the form the first record in the DB appears, yet when I click on any of the buttons (first, last, previous…) I receive an error message similar to this “accessor ‘xfa.sourcset.mcteldir.last()’ is unknown”.Any ideas of what I am doing wrong?

  32. Peter says:

    Is Acrobat 6.0 Professional, that is packaged with Adobe CS capable of connecting to an ODBC data source or do I need to upgrade or get LiveCycle? I have not been able to get data to import from a text tab delimited file successfully.

  33. Jin-oh Choi,It sounds like you’re running into the same problem others have been running into where Acrobat 8 fails to trust a local DSN connection for some unknown reason.The problem is currently being investigated however I haven’t seen anything conclusive yet.The only other ways of importing data into a PDF form are via XML or a web service (WSDL data connection).

  34. DW,There are a few different ways you could go about doing this and without a little more detail, it’s difficult to suggest one way over another.For instance, you could have the PDF form served from your web site and then the data could be submitted to the database via a LiveCycle Forms solution.The fact that you would like the user to first submit the data, then print and mail-in the form, however, tends to tell me that you need a signature from them. An alternative solution would be to use LiveCycle Barcoded Forms whereby you would encode, in real time, the data entered into the form electronically (prior to printing) into a 2D barcode (usually “PDF 417”). Once the form is filled, the user would print the form, sign it and mail it in to your office. On the receiving end, you would use a 2D barcode scanner to quickly scan the information entered into the form directly into a database and then simply keep the paper copy of the form with the user’s signature for your records.A 3rd option would be to create a web service to which the form’s data could be submitted over the Internet.Unfortunately, I don’t have the resources necessary to put together such a sample in a working state. As far as an existing sample, I don’t know if we have one but if we did, it would be on the LiveCycle Developer Center Samples site (you’ll find LC Designer and LC Forms samples there).

  35. Rick,If the error message you’re getting is exactly as you’ve specified,

    “accessor ‘xfa.sourcset.mcteldir.last()’ is unknown”

    then the problem is likely due to the fact that “sourcset” is misspelt: It should be “sourceSet”.If that was just a typo when you wrote what the error message looks like, then it’s something less obvious.The fact that the first record appears when you load the form in Acrobat means that your data connection, “mcteldir”, should be properly setup. Is that the correct name for your data connection? If it isn’t, then it’s another reason why you might get this error.Aside from that, it’s difficult for me to tell what’s going on. Please let me know if either of these pointers solves your problem.

  36. Peter,Acrobat 6.0 Pro should be able to import data from ODBC data sources and you should be able to create ODBC data connections in Designer 6.0 that comes with it.

  37. Hi,Wonderful answers given here. Is there a contact in INDIA – New Delhi who can help develop some solutions for us on database connectivity for pdf forms.Thanks,

  38. Chandresh,Thank you for the complement!I’m currently looking into this for you and will let you know as soon as I have an answer.

  39. Tobias says:

    Hi Stefan,thx alot for your guide. I followed your instructions and everythings works fine with connecting to the database and dynamically fill the form. But there’s a big problem!It’s a dynamic order form, so the users are able to tell what they want to order and how many. Then the total price is calculated. After this they save the form (with Acrobat Professional). But when they reopen the previously saved form its refilled with data from the database and the calculated price and everything is cleared!Is there any chance to save the form with filled data without reconnecting to the database?Thx alot for your help,Tobias

  40. Thanks Stefan – great info! I have used this article to provide my form with access to my data – using ‘previous’, ‘next’ and ‘print’ buttons. The latter prints only the current form – how could I iterate thru my dataset and print ‘all’ records? Thx, Kim.

  41. Kim,You’re welcome!I think my article on displaying all records from an ODBC data connection is exactly what you’re looking in this case.

  42. Tobias,I think the cause of this problem may lie with the bindings set on the various fields which you’re using to fill the order.When you drag a field from the Data View palette, it automatically gets an explicit binding to the pertaining data node. If you look in the Binding tab of the Object palette, you’ll find the Default Binding property and it’ll probably contain something like “$record.{DataConnectionName}.DataNodeName”.If that’s the case, then what will happen on save and re-open is that the data from the database will be pulled into the form after the data is merged-in and it’ll replace all the saved data.Usually, when you have a dynamic order form such as the one you’re designing, you tend to have drop down lists and list boxes that let the user choose an item from the database which they would like to order. In those cases, you still create a data connection to the database but you use it in a different way than the one I detailed in this article. In particular, you use the “data drop down list” and “data list box” objects found in the Library palette’s Custom tab. These objects contain pre-defined Initialize scripts that will connect to the data connection you specify and populate their lists with the text and value data nodes which you also specify. The difference here is that the drop down lists and list boxes defined in this manner end-up with “Normal” binding types instead of explicit ones to the database. As long as you keep the names of these objects different from the names of the data nodes in your data connection, when you save and re-open the form, they’ll still contain the saved data and yet will still be re-populated with values from the data connection.You can find an example of using the data drop down list object in the sample form from my article on selecting specific database records. See the “CategoryList” drop down list.

  43. Thx Stefan, Below is the code I use for iterating thru the DB – it works, displaying a message & moving onto next record. I have used various methods to try & print all records but it just seems to hang. How do I use the xfa.host.print parameters to print each page and move to the next record w/out user intervention? Thx again, Kim.xfa.sourceSet.BJFCDATA.open();xfa.sourceSet.BJFCDATA.first();while( not xfa.sourceSet.BJFCDATA.isEOF() ) do//$host.print();$host.messageBox($record.BJFCDATA.Surname);//xfa.host.print(1, “0”, (xfa.host.numPages -1).toString(), 0, 1, 0, 0, 0);//$host.messageBox(xfa.host.numPages);//topmostSubform.Page1.PrintButton2::click:xfa.sourceSet.BJFCDATA.next();endwhile//$host.print(1, “0”, (xfa.host.numPages -1).toString(), 0, 1, 0, 0, 0);//$host.print();xfa.sourceSet.BJFCDATA.close();

  44. Musomba, Kapuulya says:

    I am to design a database by use of Microsoft access 2003. The data base includes names and with each name the data for specific person is entered. How can I display information of a particular individual by i.e tying on the first letter of a name

  45. Kim,I think I understand better what you’re trying to do. Sometimes the term “print” is used synonymously with the term “display” so I didn’t realize you were actually wanting to print each record to paper.I would strongly suggest that you model your form according to my sample on displaying all records from a database. This will give you a form which, when loaded in Acrobat, will list all data from all records in your database.Once the form is generated, the user can simply print the form in the usual manner. You could even provide a print button if you like: just place it above or below the repeating subform which will contain the data for a single record.If its necessary for you to print each record on its own page, then you could set a conditional break on the repeating subform (the one that contains the record data) set to break to the “top of next page” (by setting the conditional break’s “To” property) “before” the next subform instance is appended (by setting the conditional break’s “Break” property). This will result in a form which displays one record per page.

  46. Kapuulya,I think my tutorial on selecting specific database records is exactly what you’re looking for. In fact, the sample form uses a drop down list to allow the user to select a type of movie and then filters the records which the user can browse using the fields below based on that criteria. Any modifications made to the data in a record based on the filter are immediately reflected in the database.Based on that sample, you could provide a drop down list containing a list of unique letters, those being the first letters of each name in the database. That can all be done with a carefully crafted SQL statement used to populate the drop down list which would essentially serve as your index. A selection in the drop down list would then apply an SQL statement to the second data connection which would simply select all records from the database where the name begins with the letter the user selected.

  47. Svet says:

    Hi Stefan,Big thanks for your blog.I have a form that works with MS Access database that has 150 fields. I can add new records and go through records but when I try to delete a record I get “Query is too complex” error message. Any suggestions how to overcome this?Thanks

  48. Svet,You’re very welcome!I’ve never personally come across that error.I found a Microsoft Knowledge Base article which offers suggestions on how to solve the problem which might do the trick for you.Please let me know if it helps.

  49. M Llewellyn says:

    I have created a Survey form in Lifecycle Developer 7.0 and have it writing the results to an Access database via a local DSN. It was soooo easy to set up I knew it had to be too good to be true.Now I am ready to test it with other users. The plan was to e-mail the link to the survey to the test group of users. There is no way I can create a DSN on the local machine for each of the users that will be completing the survey.I need instructions on how to make my survey write to my database without using a local DSN. Can’t the DSN be set up on the server where the survey is located and the database resides? I have searched this but the answers are vague. Please help me figure out a way to be able to use my survey and write the results to an Access database WITHOUT a local DSN on each client.

  50. M Llewellyn,Unfortunately, I don’t believe it’s possible to create a DSN on a server and have the form connect to it.What you can do is share the database file and have everyone create a local DSN to it. This way, people don’t also have to have a local copy of the database. I can’t speak to how secure this is but you can do it quite easily in Windows.If this still isn’t good enough, then I think you’re only option is to write a web service that runs on the server where the database is located. Your form would then use the web service to set and obtain information and the web service would take care of working directly with the database. This model would actually also be much safer since it wouldn’t directly expose the database.

  51. Mike says:

    I had the same error as Katrina (12/8/2006). Everything works when I use Acrobat Pro 7.0.8, but when I use Reader 8, the list box prefills the database info, but when it runs the SQL statement to fill the binded fields I get an error. You mentioned reader extend option… where is that? I just see commenting extending in acrobat pro. I assume the reader extension product from Adobe is expensive?

  52. Mike,Before we start down the road of Reader Extensions (which is quite expensive if you’re only using it for a small number of forms), what specific error message are you getting when you try to use your form in Reader 8?Based on what you wrote, it sounds like you are still capable of importing data from a data connection in Reader 8 since your list box is getting pre-filled with database information. Does the user then make a selection, prompting your form to run an SQL query which is supposed to retrieve specific records from the database and display them in bound fields?Are you getting some sort of security error?

  53. Joanne Koch says:

    I am going crazy! I am an extreme novice developer…yet have been tasked with creating forms that send data to a database. I am able to use your controls just fine. However, I need a form that opens as a blank form, the user fills out data, and then submits it to the database. I do not want them to be able to read any data from the database. I just want them to add a new record each time without risk of updating older records. Make sense? Can someone PLEASE help?

  54. Joanne,I believe you have two options here:You can use the sample form I provided in this tutorial and modify it such that a new record is added automatically when the form is opened in Acrobat and the only button available is the “update” button (which you could rename “insert”). This way, the user is presented with empty fields ready for new record data which gets inserted into the database when they click on the “insert” (update) button.To do this, you would simply remove all buttons except for the “update” button and move the script from the “add new” button into the root subform’s (named “form1” at the very top of the tree in the Hierarchy palette) Form:Ready event (using the Script Editor palette).A more advanced option would be to use some of the techniques I describe in my tutorial on inserting, updating and deleting records in a database.

  55. Kerry Pugh says:

    Stefan,Thanks for the blog! I am a bit new to all of the design aspects and find this is my most used bookmark. Seeing as I am now a forms specialist? it is great to have this kind of resource. I do have a question though. I have a button that take xml data into my msacess database and brings data out. In the end it makes the form complete. Now I would like to change that button or add another that would save a pdf into the record in the database it created. Perhaps, as an ole object. This way I can refer to the form when I look at the record if I needed to. Is this possible?Thanks,Kerry

  56. Kerry,I’m happy to hear that you find my blog is a good source of information. It’s always encouraging to hear that it’s useful to people!Unfortunately, I don’t believe that there’s a way to get a copy of the PDF automatically inserted into a database record via a button on a PDF form you create with Designer. That doesn’t mean it isn’t possible, however, it just means that you’ll have to resort to other technologies, which come into play once the form has been submitted, to help make it work.For instance, you could design your form such that when the user clicks on the submit button you use today, the last step in the script is to execute an invisible submit button’s Click event, thereby resulting in the submission of the entire PDF, including the filled data, to a destination of your choice. This destination could be a server or even an email address. Once the PDF is received, it could be inserted into the appropriate record in the database, either as a binary blob or linked via OLE (depending on the type of database you use). The idea here would be to do everything in one step: the submission of the data to the database and the submission of the PDF to some destination (i.e. the user still only needs to click once).This solution is essentially the “Two Button Submit” Technique where the invisible button is a submit button which submits the entire PDF instead of the data only.You can create this submit button in Designer by using a regular submit button (use a button object from the Library palette and select “Submit” from the “Control Type” property on the Object palette’s Field tab) and specifying “PDF” in the “Submit As” property on the Object palette’s Submit tab (if you’re using Designer 7.1, the property will be named “Submit Format” instead).To submit the PDF to an email address, simply enter the following Submit URL:

    mailto:email@address.com

    where “email@address.com” is the email address to which the PDF should be submitted.

  57. Chaitali says:

    Hi,I am using the code for connect to the database with methods like first(),next(),previous(),last(),addNew(),update(),cancel() etc…when i view the PDF form i am just able to get the first record, but onclick of navigation buttons the records are not moving. Neither i am able to insert new records or perform any DML operation. what could be the reason.

  58. Chaitali,In case you’re using the buttons from the snippet I provided in this tutorial, is it possible that you’ve given a name other than “DataConnection” to your data connection?If that’s the case, then the scripts on all the buttons would fail (and so you wouldn’t be able to navigate through the records) because their programmed to work with a data connection with a name of “DataConnection”.Of course, you can change this by editing the scripts on each button using the Script Editor palette available via the top-level Window menu.

  59. Lyn says:

    Thank you! Thank you! Thank you Stefan. Your blog is a lifesaver and has helped me beyond belief.But I do have one small problem described below..All the buttons work fine until I click the Add New button. After this button is clicked, it blanks out the form as it should to allow new information to be posted. But then all the buttons stop working. I get an error message similar to this one when I clicked previous:Script failed (language is formcalc; context is xfa[0].form[0].form1[0].#subform[0].previous[0])Script=xfa.sourceSet.DataConnection.previous()Error: next operation failed. Multiple-step operation generated errors.Check each status value.[ID:11]But it does place the updated record in the Access database (strange, huh). And even stranger, if I close and reopen the form the buttons work again until I click add new.So what am I doing wrong? This is a very simple dynamic form created in Designer 8.Any help will be most appreciated.

  60. Kristin says:

    I was wondering if there was a way to encrypt the database username/password information on these documents, in case the user saves the document from my website to their local machine. Thanks.

  61. Lyn,The very last part of the error message you quoted is “[ID:11]”. This is usually the name of the column (and the value associated to it) for which there was an error.Since the name is “ID”, I’m wondering if you might be attempting to set the value of an auto-incremented column. This is something that you cannot do using a data connection. In that case, you must provide an SQL Query, when setting-up your data connection, which excludes the “ID” column from the connection.You can edit an existing data connection by right-clicking on it in the Data View palette and choosing the “Connection Properties” command.