Web Databases 101

Setting up the Database

To get the database set up is actually not a very complex task if you understand Microsoft Access and are comfortable playing with Windows settings from the Control Panel.

The first thing you need to do is start Access (or whatever you are using) and create a new database. Store the database wherever you like and call it something simple and memorable, like registration_db. Next, create a new table and call it something like tlb_users. Build the table using design view so you can set the constraints for the fields. Make sure each field is named appropriately to reflect the data being collected by the form. Also, be sure to assign a primary key - just make up an auto-number field for that. Finally, make certain that you properly set the constraints for each field - primarily field lengths. If you have address with maxlength="100" then your database should also allow for 100 characters in the address field. If you don't want to fool around with making this database, you can download the one I made. For those of you who want to take the pure do-it-yourself approach, here is a screenshot of my tbl_users.

If you are totally lost at this point, I do not recommend you go forward. At the bottom of this page, I will provide some links to Access and relational database tutorials so you can get up to speed. Once you have read those, come back and try again! It will take some time to get a handle on all of this though, if you are fairly new to these concepts and techniques.

Making the Database Accessible Via the Web

The next thing you need to do is make the database accessible to your web page. To do that, you will have to set up a DSN (data source name) in the ODBC settings found inside the Windows Control Panel. A data source name is something you configure that will store information about how to connect to the database using ODBC (Open Database Connectivity - basically a driver that enable one to send SQL commands to a database management system, from outside the system itself - like via a web page).

Windows makes setting up a DSN fairly easy and painless - all you need to know is the name of your database and where it is on your hard drive. Please note that you are going to have to have some administrative privileges to do this if you are on an NT server. If you are working with a third party web host, you will need to contact them and ask them to set it up for you. With Windows 9x and Personal Web Server, you can set it up on your own!

First, go to Start > Settings > Control Panel. Then click on "Data Sources" (if NT) or "ODBC Data Sources" (if Windows 9x). Then, click on the System DSN tab. Click Add... and select the database type you are using - for Access users that is Microsoft Access Driver (*.mdb). Enter something simple for the data source name and remember what you enter; you will need it when we add the necessary logic to the web page. Description you can leave blank or fill in, it is not really necessary. Next, where you see a row of buttons under the heading Database, click the Select button. Browse through your hard drive and find the database you created or downloaded. Then, select it, click OK and then click OK again on the original screen. You will see that screen disappear, but will notice that there is now something in the list on the System DSN tab - that is the database you just set up, identified by the name you assigned in the dialog box.

Believe it or not, that is it, we are ready to go back to the code and make this work! On the next page we will discuss just how we will get the web page to talk to the database, via ODBC.

Database References
WDVL: Database Basics
Database Normalization
Webmonkey: Your First Database Microsoft Access Tutorials

Finalizing the Form
Part 3 - Building a Registration Database
ADO is Our Friend