Home arrow ASP arrow Page 2 - MS Access: Tables, Views and Procedures
ASP

MS Access: Tables, Views and Procedures


Ever wondered how to create a table, view or procedure with MS Access? In this article Eric shows us how to create all three and work with them from an ASP script.

Author Info:
By: Eric Beck
Rating: 4 stars4 stars4 stars4 stars4 stars / 74
July 04, 2002
TABLE OF CONTENTS:
  1. · MS Access: Tables, Views and Procedures
  2. · Building Our Database
  3. · Creating the Queries
  4. · Conclusion

print this article
SEARCH DEVARTICLES

MS Access: Tables, Views and Procedures - Building Our Database
(Page 2 of 4 )

Before we begin, there are several key concepts to follow when developing any type of application. The first concept I would like to discuss is scalability. The entire application, right down to the database needs to be designed with scalability in mind. In other words, we want to allow for the growth of our application.

So let's take a look at our database and see how we can apply this concept. When executing queries against a database, it is not a good idea to run the queries on the actual tables themselves. The main reason being that you risk affecting the integrity of the data in your database. For example, if changes are ever required to a table, you risk adversely affecting the outcome of a query being executed against the table(s). This could be a very costly error.

The way to get around this is to create views against each table in your database and have the queries execute against those views. A view is simply a SELECT query that extracts data from a table allowing you to view the data in a particular way. So, let's start by building a database.

The database we're going to build has only two tables in it but it will serve our purpose for this particular tutorial. We will use this database to keep a record of music tapes.

The first table is called tbl_Tapes and looks like this:

Our tbl_Tapes table

The next table we're going to create will be called tbl_Songs, and it looks like this:

Our tbl_Songs table

Creating Base Views
Now that we have the tables we are going to use for our database, we can go ahead and create our views. The views that we are going to create at this point are called base views, and will be named with the convention bv_[View Name]. For example, we will name the base view for the tape table "bv_Tapes" etc. Base views act as duplicate tables.

OK, so let's start by creating our first base view.

Our tbl_Songs tableThe first thing we need to do is select the queries button in the objects toolbar and then select "Create Query in Design View" and add our table called "tbl_Tapes".

We are going to add all of the fields to the view, but we want to add them individually. We don't want to use the * to select all the fields. This is where scalability and safety come in. If we ever want to make changes to the table we want to be able to do so without affecting the view and the queries that run against it.

The base view for our tbl_Tapes table

Now that you know how to create a base view, create one for the tbl_Songs table and save it as "bv_Songs".
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials