FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Getting Started with Microsoft Access

Microsoft Access represents one of the most powerful tools for taking your data to the internet. There are many other database programs, some more powerful, but none that is better integrated with FrontPage than Access.

In this tutorial, we'll look at a few of the things that you can do to make sure your database is ready to be placed online. Good basic structure is key to making your online life much easier over the long run.

Manually Create Your Database In Access

Design Your Tables

When you create your tables in Access, avoid using spaces in the field names. You can separate words with an underscore, or simply change the case. Examples include first_name or FirstName. I use the latter style. Spaces can cause problems in FrontPage and can make hand coding more difficult later.

If you want to be even more accurate in your naming, always start your table names with a small case t. For instance, tFirstName. Preface your query names with a q such as qFirstNameAlphaList. This will make it easier to select your data sources in the Database Results Wizard later. You will never wonder if the data source is a table or a query because the name will always make it self evident.

Be sure to take the time to carefully assign your data types. Make sure your date fields are set with the date/time style, that your memo fields are set to memo, and that yes/no fields are set appropriately.

Normalize Your Data

What do we mean by normalizing your data? One of the real benefits of relational databases like Microsoft Access is that they can link multiple tables together in a one-to-many relationship.

One-to-many relationships occur when a single record in one table can relate to several records in another. For instance a single company in your Companies table may relate to several employees in your Employees Table. Now, you could combine these tables into one, but you risk several problems.

• If you want to have one table that lists all the companies and employees, then you will need a Company field for every record. Now if you don't enter the company name exactly the same way every time, then Access won't be able to accurately search by company name.
• You begin to have duplication of data entry effort. No piece of information should need to be entered into the database more than one time. Any more and errors begin to creep into your data.

Here is an example of how to set up a company directory.

1. Company Table. This is a list of the Company names and the physical information for the organization such as mailing and courier address, tax number and billing codes
2. Employees Table. This will be a list of employees, their contact information and employee data (EmployeeID, salary, start date). Each employee will be connected to the Company Table by the CompanyID field.
3. Sales Table. This will list the sales generated by each employee. This will connect to the Employees table by the EmployeeID field.

Using this method, the company is entered into a single table. Employees are connected to the company by the CompanyID field. As the employee generates sales, they are added to the Sales Table but will have a field for EmployeeID to make sure they remain connected to the appropriate Employee. No data is recorded more than once.

Always Use An Autonumber Key

Every table needs to have a Key field. This is a single field that will always be unique. Make a habit of using an autonumber field for your key. Avoid the temptation to use one of your existing fields as a key. If you choose CompanyName as your key and they later change their name, you will find your date in your related tables no longer connecting to the Company Table.

Autonumber fields are always unique, never change, and are easy to work with.

DO NOT name your Autonumber field ID. Give it a unique name such as CompanyID, EmployeeID and SaleID. This will avoid the chance that Access will get confused and try to match a field to the wrong table.

Create Your Queries In Access

You can use FrontPage to do much of the work of creating queries through the various steps of the Database Results Wizard. You will be well served by learning how to use the Access Query Design window. It is far more powerful than anything you could do in FrontPage AND you can save the query and simply select it when you open the Database Results Wizard (DRW) in FrontPage.

The DRW allows you to select a Record Source to work with. You can select any table OR any saved query. A little work in Access can save a lot of work in FrontPage.

Forget the Forms and Reports

Once you decide to use your database online, you can forget about designing forms and reports in Access. Because your database will reside on your web server, you'll create all of your forms and reports in FrontPage. Put most of your energy into creating good tables and queries and let FrontPage do the rest.

This, of course, assumes that you will only be using your database online. If you will be doing some work locally, and then uploading the database then you may still need forms and reports as part of the database.