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:
The next table we're going to create will be called tbl_Songs, and it looks like this:
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.
The 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.
Now that you know how to create a base view, create one for the tbl_Songs table and save it as "bv_Songs".
Next: Creating the Queries >>
More ASP Articles
More By Eric Beck
