The DATABASE1.PL Script is no longer intended for use. This documentation is kept online as a reference for forms yet to be converted to FORMMAIL2.PL or DATABASE2.PL Script. PLEASE see FORMMAIL2.PL Documentation or DATABASE2.PL Documentation.

It is now possible to set up a web form that records data from users in a format that you can import into Microsoft Access or Excel. This method works with with Microsoft Access versions 2 or later, and Microsoft Excel version 7 or later. The import instructions are for version 7 for Windows 95, but other versions are extremely similiar.

You can use this method with an existing Access database, provided that there are only "standard" type fields, and the last field in the structure is a "date" type field. "Standard" field types are text, memo, date, numeric (number), or currency. The last field, a "date" type, is used to record what date the user input was done. If you already have a "date_input" field, you can move it to the end of the structure, else just add another field to your structure.

It is probably best to set up a database table and then design your html form, but MS Access will import the data from the web into a new table on-the-fly, and allow you to name the fields and field types (but not lengths).

There are several example forms you can look at, download and change to get started fast. There are even matching databases written in Access that you can use (you will need to "unzip" these). But if you need something very customized, you can build it using the instructions that follow. Please note that the ready-to-use Access databases that you can download from the three links below all were created with MS Access version 7.

Prerequisites
You should be familiar with MS Access and html forms before trying to use the DATABASE1.PL script. However, you can get off to a quick start by using one of the above example forms and making minor changes to it. If you would like to take a refresher course on forms, click here. If you like online tutorials, you might want to visit Microsoft's Access Online Tutorial.

Some Important Notes

• The maximum amount of information your form can handle is 1K (1024 bytes or letters of information).
• You may have character, number, currency, date and "yes/no" (logical) fields in your desktop database. Multimedia and OLE (object link embedding) fields are not supported (of course, how would a user input one of those anyway?)
• There isn't really a good way of preventing your users from entering "bad data". You can, however, limit the amount of characters they type in a field, or clean things up after you import the file.
• In the case of text boxes, hard returns (new lines) are not retained because the data has to fit on one line when it's saved.
• There is no corresponding "querying" feature associated with DATABASE1.PL. Its purpose in life is to collect data for you and to save typing. But there are easy ways of publishing data to the web with MS Access (using the free Access Internet Assistant). Also, web-data technology continues to evolve and there may be more interactive methods available soon.
• All field names on your new forms should be in lower case. The html field names do not have to match field names in your Access file (although they can).
• This script does not use SSL or special security. Someone hacking around could figure out how to look at your datafile. Although future plans include encryption, security, and more interactive features, at this time we ask that you do not use DATABASE1.PL to take credit card information through a web form.

The Desktop (Access) Table

Other than that, you may have fields of the type listed above (in the second bullet under Some Important Notes). The overall size limit of data transmission is about 1K, so you will not want to have so many fields or huge fields that more than 1024 characters of input are submitted by the user at a single time.

Setting Up the HTML Form

      <FORM METHOD=POST ACTION=http://www.ianr.unl.edu/cgi/database1.pl>

• htmlform
  The htmlform field tells the DATABASE1.PL script where to save your data file. Normally, you want this to be the same directory that your form is in, and it must be a directory that you have FTP access rights to.

  Syntax:
  

      <INPUT TYPE="hidden" NAME="htmlform" VALUE="/ianr/mycounty/">
  

  In this example, your form is located on http://www.ianr.unl.edu/ianr/mycounty and mycounty is the directory that your data gets saved to.

• backgrpref
  This field is required for DATABASE1.PL to work, but the details on how to set it up are not posted here. If you did not register for DATABASE1.PL already, you can do that here. Information and notices of enhancements to DATABASE1.PL will be sent to you when you register.

• fldorder
  This is an extremely important field. Without it, your data gets written in a random order, which makes importing virtually impossible. You must list the fields (but not hidden ones) in the order that they appear in the database (Access) table. You do not need to list a date field or IPaddress field at the end, these fields are automatically put in last/next-to-last order.

  Syntax:
  

      <INPUT TYPE="hidden" NAME="fldorder"
          VALUE="first,last,address,city,state,zip,age,comments">
  

  This example uses fields called first,last,address, etc., but of course you may have different fields and field names. Also, these correspond to the field names you use on your html form, not necessarily to field names in your Access database (though they can be the same). Please avoid spaces in the field list. Note that case matters, i.e. "First" is not the same as "first" - a safe rule is to always use lower case on all fields and field references. Also, note that it is best to keep the VALUE= part on a separate line, as shown above.

• required
  Ironically, the required field is optional! If you want to reject data you deem incomplete, yet politely tell users what they forgot to fill out, you can include this field. Simply place all field names that you want to be mandatory into it. If the required fields are not filled in, the user will be notified of what they need to fill in. Note: Case matters in the list.

  Syntax:
  

      <INPUT TYPE="hidden" NAME="required"
         VALUE="first,last,address">
  

• savehostaddr
  If you want to record the user's IP address in your database, set "savehostaddr" to "yes". The default (if you do not have a "savehostaddr field) is to not save this information. You may also explicitly set it to "no". An IP tells you (roughly, at least) where the user came from. Sometimes the IP will only tell you that the user came through on Navix, or perhaps a certain building on campus. Ordinarily, this is not a concern, but having the option may prevent some (less mature?) users from inputting lots of garbage.

  Syntax:
  

      <INPUT TYPE="hidden" NAME="savehostaddr" VALUE="no">
  

• htmlurl
  If you wish to redirect the user to a different URL, rather than having them see the default response ("Thank You for your Input or Request") to the form, you can use this hidden field to send them to a pre-made HTML page.

  Syntax:
  

  <input type=hidden name="htmlurl"
      value="http://www.ianr.unl.edu/your_dir/your_file.htm">
  

Using MAXLENGTH to control input
It is a good idea to use the MAXLENGTH property on HTML forms to limit input on text fields. You don't want the user to type in more than your Access table will hold. So for example, if your "NAME" field in Access is defined as text, 20 characters, you'd of course set MAXLENGTH to 20.

Logical (Yes/No) Fields
Logical fields should be set up so that a true (or checked) value is "TRUE" (with the quotes) and false returns "FALSE". One way to do logical fields is with two radio buttons per field, as shown below:

Are you a logical person? <INPUT TYPE="radio" NAME="logiperson" VALUE="TRUE">Yes
  <INPUT TYPE="radio" NAME="logiperson" VALUE="FALSE">No

Another way of using logical fields is to use a single checkbox for the field:

<INPUT TYPE="checkbox" NAME="logiperson" value="TRUE"> Check here if you're logical

• If you use a single checkbox to represent a logical field, you cannot "require" it - since the user may want to leave it unchecked.
• Checkboxes may be used for text choices and multiple options in a single field as well - checkboxes are not just for logical fields.

Remember...

• Have several people test your form, and make sure your Access table looks right before making your form available for everyone to use.
• The last field in your Access table must be a date field.
• If you set savehostaddr to "yes", the next to the last field must be text, with a size of about 20.
• If you have setup questions, you can send me a note.

Appending the Data to an Access Table

1. Open your Access database (.mdb) file.
2. Open the File menu, Get External Data, Import...
3. Change "Files of Type:" to Text Files
4. Navigate to where you downloaded your file (presumably with a .TXT extension). The instructions on how or what to download are not listed in this document, but are available upon request to IANR departments with pages on the IANR web server.
5. Double-click the file name
6. The Text Import Wizard appears. Make sure "Delimited" is selected (not Fixed Width)
7. Click on Next. Make sure the "Comma" radio button is selected as delimiter, and Text Qualifier is a double quote (") mark.
8. Click on Next.
9. If you are appending (adding to) an existing table, select "In an existing table", and select the table. Otherwise, select "In a New Table", and allow Access to build the table for you.
10. If you clicked on "Existing Table" - click on Next, then Finish.
11. If you clicked on "Into a New Table" - click on Next, and rename fields and specify field types, if you want to. Then click on Finish.