vol273.indd IASSIST Quarterly Fall 2003 15 by Nancy Lemay * Abstract As data users become technically knowledgeable, “data specialists”, with no technical support available, should endeavour to offer sophisticated means for acquiring and/or requesting data. Providing users with the means to send data requests via the World Wide Web (Web) is a realistic goal for any data specialist, even those without programming capabilities. Data requests can either be sent via e-mail or stored in a database. This article will cover the latter option, i.e. storing data requests in a database. By empowering the data user, you are in a way empowering yourself. Introduction This article provides guidance for the creation of Web- based forms, using the example of the registration form created for the 2003 IASSIST conference. Web-based forms can range from an online survey to a data request form. The underlying concepts presented in this article are pertinent for any database-linked Web form. The tools necessary for the creation of such a form should be accessible to most data specialists, who are regularly required to manage information gathered using the Web. Context In the spring of 2003, the University of Ottawa hosted the IASSIST conference. In light of the high participation rates in the past, the local arrangements committee (LAC) anticipated a high participation rate for 2003. The LACʼs priority was therefore to ensure that the Web- based registration procedure be simple and efficient. More importantly, a Web-based registration procedure would minimize human errors since the number of people involved in processing the registrations and payments would be reduced. Capturing the registration information electronically also allowed us to automate the creation of receipts, name tags and banquet tickets, thereby significantly reducing the LACʼs workload. Considerations The suggestions provided in this article are meant to limit problems and complications which may be encountered when setting up a Web form. It should be kept in mind that there are other ways to successfully produce the same end results. The very first thing required to create a Web form is access to a Microsoft Internet Server having MSFrontPage extensions and MSAccess software installed. The use of a test server is recommended to verify that the Web form works properly and to avoid many pitfalls. Keep in mind that once the form is live on the Web, it is very difficult and inconvenient to make changes to it. It is also difficult to know how the database will react when changes are made while the form is being accessed simultaneously by people via the Web. Summary of Steps to be Taken1 Once access to a Microsoft Internet Server has been set-up and tested, we can move on to the conceptual information necessary to produce a Web application. The first step to link a Web form to a database is to create a simple form using MSFrontPage (called “yourwebform. htm”). The creation of a simple form in MSFrontPage is relatively easy since the whole process is Wizard driven. Any MSFrontPage manual should also explain how to create a simple form. It is highly recommended to create a few textboxes in order to test the success of the operation before creating a complex Web form. It is very important to ensure that the textboxes are named properly, preferably without spaces in the textbox names.2 The names given to every textbox should be noted, since this information will be needed for the database. It is also wise to decide upon a naming scheme ahead of time, especially if more than one person will be working on the form or database. For instance, we decided that all textbox names would be capitalized and any spaces replaced with an underscore. This can be particularly useful if you need to go back and make changes or remove textboxes. After creating the form, an MSAccess database using the same field names given to the textboxes in the first step should be created. An example is the creation of three fields called First_Name, Last_Name and Email. Then save the database as “yourdatabase.mdb”. The process is Linking a FrontPage 2000 Web Form to an Access 2000 Database 16 IASSIST Quarterly Fall 2003 simple as it is entirely Wizard driven. After the creation of the form and database, import the database into the MSFrontPage web page. To do so click on File � Import option � Add file and locate the database “yourdatabase.mdb”. At this point, the Wizard will ask whether or not to create a database connection. It is highly recommended to do so. The connection may be created later, but it is always easier to do it at this point, especially using the Wizardʼs instructions. A new folder named /fpdb, which will be used to store the database, should then be created. The form created, “yourwebform.htm”, is the applicationʼs data-entry form. A new ASP page needs to be created with an SQL Insert statement. This can be done using the Form Properties Dialog Box to create a link to “Enter_Database_ Insert.asp”. This ASP page will link the form to the database. To create the “Enter_Database_Insert.asp” page use the Insert Database Results Wizard to add the SQL Insert statement for copying records from the form to the MSAccess database. The following is an example of an SQL Insert statement: Insert into Names (First_Name, Last_Name, Email) Values (ʻ%%First_Name%%ʼ, ʻ%%Last_Name%%ʼ, ʻ%%Email%%ʼ) Verify the query by using the Verify Query button in the dialog box. Save the new ASP page as “Enter_Database_ Insert.asp”. At this point, it is necessary to rename “yourwebform.htm” form with an .asp extension. At this point, it should be possible to start testing the form on the local machine, or preferably, on a test server. Access to the Database The last major concern is attributing access rights to other project team members. Unless a second person was actively involved in the setup of the database, only the database administrator should be given access to the MSAccess database on the Microsoft Internet Server. This will help ensure database integrity. When setting up our database, access to the information stored in the MSAccess database was provided to our registration coordinator by linking an MSExcel spreadsheet to the database. We wanted to limit the use of the database as much as possible. Therefore, because the registration coordinator needed just the fields pertinent to the registration desk, only selected fields (such as registration information and ID numbers) from the MSAccess spreadsheet were linked to the Excel spreadsheet. This gave the registration coordinator full access to the registration data and the ability to add columns or comments in Excel without changing the MSAccess database. Furthermore, since we had a limited number of spaces available for each workshop, the registration coordinator could track workshop registrations and inform the technical support coordinator when a workshop was full. This allowed us to update the registration form on the Web and remove the registration option for that particular workshop. In addition to the tracking capabilities, the registration coordinator could provide the LAC with weekly updates about registration numbers and membership details. Using MSAccess Reports for Receipts, Name Tags and Banquet Tickets The use of a database and Web form to capture our registration information allowed us to take advantage of many of MSAccessʼs advanced features. For example, the report option in MSAccess which enables the retrieval of data from a database while formatting it to meet the reportʼs purpose, or, the addition of graphics, tables and charts to reports. All of our 2003 IASSIST receipts were automatically created as reports in MSAccess. We then simply arranged the registration summary in a formatted table along with the 2003 IASSIST logo. This meant that the registration coordinator only had to print and send the receipts to conference participants. Name tags were also generated using the report options in MSAccess. This was done by selecting the pertinent fields, formatting our report to follow the dimensions of precut inkjet name tags paper, and adding the conference logo. Conclusion The linking of a Web form to a database can be used for numerous tasks - not only for conference registrations, but also for other data requests sent via an online form or for online surveys about your service area. The goal of this article was to encourage non-technical data specialists to explore this option in order to provide a sophisticated service. * Contact: Nancy Lemay, Data Support Specialist, Geographic Information and Data Centre, University of Ottawa. nlemay@uottawa.ca. Footnotes 1 The following article was used as a guide through specific steps in Front Page: Slater, William F. III. Database Wizardry with FrontPage 2000. Sept. 1999. < http://www. webtechniques.com/archives/1999/09/data/>. 2 An example is: First_Name, Last_Name and Email