Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 15.5 Post Web Form Data to an Access Database

15.5.1 Problem

You want to be able to collect data
from a user of your web site and post the data to an Access database.
You are not using a SharePoint server and you'd
prefer to accomplish this task with little or no programming. Is
there a way to use FrontPage to collect form data and
send it to an Access database?

15.5.2 Solution

You can use Form Page Wizard to help you create a web page that
collects information from a user using an HTML form and posts it to
one of a number of different choices. A database, however, is not one
of the choices. Fortunately, with a little post-wizard wizardry, you
can change the form so it points its data to an Access database.
Follow these steps to create a form that posts to an Access database:

This solution will not work with a SharePoint-enabled web site.

1. Startup FrontPage 2003.

2. Select Select File New... to create a new web site. On the
   New task pane, click on "One page Web
   site..." under New Web site.

3. At the Web Site Templates dialog box click on Empty Web Site and
   enter the following location for the web site:

   http://localhost/15-05

   FrontPage creates a new empty web site on the current machine. If you
   do not have a Microsoft web server running on the current machine,
   you will need to change localhost to the name or address of a
   Microsoft web server for which you have site creation privileges.

4. Select File Import. Click on Add File... from the Import
   dialog box.

5. Navigate to the

   15-05.MDB sample database and
   click Open. Click OK to add the database to the site.

6. When you click OK, FrontPage recognizes that you are importing a
   database and asks you if you wish to create a database connection for
   the database as shown in Figure 15-14.

Figure 15-14. FrontPage displays this dialog box when you attempt to import a database

7. Enter "15-05" for the database
   connection name and click Yes to import the database and create the
   database connection.

8. FrontPage displays an additional dialog box suggesting that the
   database be moved to the fpdb folder. This is a good practice, so you
   should click Yes.

9. Select File New... to create a new page. On the New task
   pane, click on "More page
   templates..." under New page.

10. Click on the General tab of the Page Templates dialog box, select the
    Form Page Wizard template, and click OK.

11. FrontPage starts the Form Page wizard. Click Next at the first page
    of the wizard which merely tells you about the wizard.

12. At the second page of the wizard, click Add to add a new question to
    the form. The questions you will add to the form are listed in Table 15-1.

    After adding the four fields, the wizard should look like Figure 15-15.

Figure 15-15. The Form Page wizard after adding four questions

13. Click Next. FrontPage displays the Presentation Options page of the
    wizard. The default responses should be fine, so click Next.

14. FrontPage displays the Output Options page as shown in Figure 15-16. Notice that there isn't any
    option to save the results to a database. This is an obvious
    oversight on the part of the FrontPage team, but you will be able to
    remedy this problem later. For now, select "save
    results to a text file" and click Finish.

Figure 15-16. The Form Page wizard doesn't give you the option to save the results to a database, but this can be fixed later

15. Select File Save to save the page, naming it
    "register.asp".

16. Select File New... to create a new page. On the New task
    pane, click on "Blank Page" under
    New page to create a new blank page.

17. On the new page, enter the text "Thank you for
    registering".

18. Select File Save to save the page, naming it
    "confirm.asp".

19. When the wizard is complete, click the mouse anywhere within the
    form. Right-click on the form and select Form Properties from the
    popup menu.

20. At the Form Properties dialog box, under Where to store results,
    select the Send to database option and click on the Options...
    button.

21. At the Options for Saving Results to Database dialog box, select the
    "15-05" database connection under
    Database Connection to Use and tblRegister under Table to hold form
    results.

22. Under URL of confirmation page (optional), enter
    "confirm.asp".

23. Click the Saved Results tab and modify each field so that it maps to
    the fields in tblRegister according to Table 15-2:

24. Click OK to dismiss the Options for Saving Results to Database dialog
    box and OK to dismiss the Form Properties dialog box.

25. Change the heading of the page to "Registration
    Form" and replace the introductory text on the form
    with "Please register by completing the following
    form."

26. Select File Save to save the changes you have made to the
    register.asp page.

27. Select File Preview in Browser to display the page in your
    browser. The page should look similar to the one shown in Figure 15-17. Enter data into each of the fields and click
    Submit Form.

Figure 15-17. The completed registration page

28. Open the

    15-05.MDB database to verify that the
    data was added to the tblRegister table.

15.5.3 Discussion

You don't need to use the Form Page wizard to connect a form
to an Access database. If you'd prefer to setup the
form yourself, go ahead and create the form, skipping steps 9-15 of
the solution. The remainder of the solution, however, should still
apply.

If you don't have an existing Access database to
work with, you can have
FrontPage create a new one for
you. From the Options for Saving Results to Database dialog box (see
Step 21), click on the Create Database... button to create a new
database. FrontPage creates a new database and hooks the form up to a
table in the database named Results.

The steps in this solution apply when using FrontPage 2003. However,
except for some trivial differences, the steps are virtually
identical when using FrontPage 2002.

15.5.4 See Also

See Database Power with Microsoft FrontPage version 2002. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_fp2003_ta/html/odc_fpbldgxmlwebs.asp