April 2007 ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, April 20, 2007

MICROSOFT ACCESS AND INTERNET

MS-ACCESS AND INTERNET

Courtesy: All Articles on Microsoft Access and Internet are indebted to Susan Sales Harkins and Martin W.P.Reid

The following article with examples illustrates how to use Microsoft Access Database on the internet. The example ASP Codes (or your Project for Internet) can be tried out on the Local machine itself after installing a Personal Web Server (Windows 98) or Internet Information Services (Windows 2000) on your PC.

Install the local server

The first step is to install Personal Web Server or Internet Information Services, so skip to the next page if a server is already installed. PWS is a desktop Web server that allows you to connect to a network and share data. The example ASP files will use your system's server (PWS or IIS) to simulate downloading data from the internet, but that data will really be on your local system.

To install PWS on Windows 98:

Insert your Windows 98 install disc in your CD-ROM drive.
Click the Start button on the taskbar and choose Run.
Enter the statement x:\add-on\pws\setup.exe
where x represents your CD-ROM drive letter. Click OK.
Click Next after reading the welcoming message.
Click the Add/Remove button.
Make sure the following items are checked:
- Common Program Files, FrontPage 98,
- Server Extensions,
- Microsoft Data Access Components 1.5,
- Personal Web Server (PWS),
- and Transaction Server.
Then click Next.
When prompted, click Finish to complete the installation. Then restart your computer.

To install IIS on Windows 2000:

Insert your Windows 2000 install disc in your CD-ROM drive.
Click Install Add-on Components.
Check the Internet Information Services(IIS) item and click Next. You can expect to wait a few minutes while Windows completes the process.
When Windows displays the Windows Components Wizard dialog box, click Finish.
Select Exit in the Microsoft Windows 2000 CD dialog box.

After installing PWS or IIS, open Windows Explorer and locate a new folder named inetpub on your local drive. C:\inetpub is the most likely location. Open this folder to find the wwwroot subfolder, otherwise known as the Web root. All subfolders and the files within wwwroot will be accessible via the Web. Initially, there will be only two files in this folder, default.asp and global.asp, but this article doesn't deal with them.

A known bug installing PWS

You may encounter an error when installing the Transaction Server on Windows 95 and 98 systems. For complete information, read the Knowledge Base Article at Microsoft's site or follow these instructions:

Create a temporary folder named \Temp on your local hard drive.
Insert your Windows 98 install CD and copy the complete contents of x:add-ons\pws\ to \Temp.
Download mtssetup.exe from the knowledge Base page.
Execute mtssetup.exe by double-clicking it, extract Mtssetup.dll to \Temp, and confirm the overwrite process.
In \Temp, double-click setup.exe to begin the installation process. Follow the PWS instructions above.

After installing PWS or IIS, you should create a new folder to store the database and ASP files that you'll execute in order to retrieve data from that database. Create the new folder in \inetpub\wwwroot and name it sample. Then copy the Northwinds sample database that comes with Access into this new folder. You'll find the Northwind.mdb file in \Program Files\Microsoft Office\Office\Samples. If you can't locate Northwind.mdb., use the Windows Find feature or insert your Office CD and install it.

By default, a folder won't share files. So after you've created the sample folder, right-click its icon in Windows Explorer, select Properties, then click the Web Sharing tab. Select the Share This Folder option and click OK. Now Windows will allow you to share the files within this folder on a network.

Click Next for example .asp files.

Earlier Post Link References:

Example ASP Files

Set up the server folder and ASP files

After installing PWS or IIS, you should create a new folder to store the database and ASP files that you'll execute in order to retrieve data from that database. Create the new folder in \inetpub\wwwroot and name it sample.

Then copy the Northwind sample database that comes with Access into this new folder. You will find the Northwind.mdb file in \Program Files\Microsoft Office\Office\Samples. If you can't locate Northwind.mdb, use the Windows Find feature or insert your Office CD and install it.

By default, a folder won't share files. So after you've created the sample folder, right-click its icon in Windows Explorer, select Properties, then click the Web Sharing tab. Select the Share This Folder option and click OK. Now Windows will allow you to share the files within this folder on a network.

Create the example ASP Files

The three links below open three short ASP example scripts. To create these files, simply copy and paste each script into Notepad as an individual file. Save each as an ASP file by manually adding the .asp extension to the file's name. For instance, to save the first script, choose Save As from the File menu, select All Files(*.*) from the Save AS Type control, then enter the filename example1.asp. Put all three ASP files in the \inetpub\wwwroot\sample folder you created.

Click Next to create a DSN connection.

Create a DSN connection

Creating a DSN Connection

At this point, you need to create a data link. When you execute an ASP file, Windows must know which database you're connecting to. An ODBC Data Source Name (DSN) simply stores the information(the name, the path, the user, and the password) required to connect to a specific database. OLE DB connections are superior to DSN because they're quicker to connect, but you must include the specific connection string in the script, and that's beyond the scope of this article. A DSN connection requires a little know-how to set up. There are two types of DSN.

System: A System DSN is available to all users on the system and is the one to use when you want others to access a DSN connection via the Web. For this article, it is vital that you use a System DSN created on your Web server or local development system.

File: All users who have the appropriate drivers installed can share a File DSN. A driver is a bit of software that provides a specific functionality, such as a connection to a data store, a sound card, and so on.

The main difference between the two types is that a File DSN is not available to the public, while the System DSN is available to all users of the machine, including the Web server.

To create a DSN on a Windows 98 system:

Open the Control Panel and double-click the ODBC Data Sources(32-bit) item.
Click the System DSN tab.
Click the Add button. Select the Microsoft Access Driver(*.mdb) item and click Finish.
When prompted, enter a name for the DSN. For the sample, enter NorthwindDSN.
Enter a description for the connection.
Click Select to locate your Access database. Find and select the sample database \inetpub\wwwroot\sample\Northwind.mdb
Click OK to create your DSN. Notice that the new DSN is added to the System Data Sources list in the ODBC Administrator.
Click OK one last time and close the Control Panel window.

To create a DSN on a Windows 2000 system:

Open the Control Panel and double-click Administrative Tools.
In the resulting folder, double-click Data Sources (ODBC) to open the OBDC Data Source Administrator.
Select System DSN, then click Add.
Scroll through the list of drivers and select the Microsoft Access Driver. Click Finish.
Follow steps four through eight for the Windows 98 instructions.

Click Next for connect-to-the-database

Connect to the database

Connecting the Database through ODBC

All the pieces are in place. Now you'll use the System DSN you created to connect to the Northwind.mdb sample database in \inetpub\wwwroot\sample the same directory where you stored the ASP example scripts. Open a browser and execute each ASP file by entering a URL in the form http://server/subfoldername/filename.asp where the server is the name of your server, subfolder name identifies the folder within wwwroot containing the ASP file, and filename.asp is the name of the ASP file that you are executing. In this case, the server should be localhost, so to run the first example file enter http://localhost/sample/example.asp

The result should be a list of names, last then first, of each employee in the Northwind Employees table. Let's look at the actual scripts; all of the ASP code is enclosed in < % and %> tags. In example.asp, the first statement< %Set Conn = Server.CreateObject("ADODB.Connection") Creates a new instance of the connection object named Conn. You can give objects any name that you want, but descriptive names are recommended. By default, the connection is read-only, which is what we want in this case. You can change this by adding the following line Conn.Mode = constant Where constant is adModeRead for read-only, adModeWrite for write-only, or adModeReadWrite to allow read and write.

Now you must open the connection using the System DSN that you created earlier. So the next statement Conn.Open "NorthWindDSN" Points the connection to the NorthwindDSN, which gives you access to the Northwind sample database in \inetpub\wwwroot\sample. The next statements

Sql = "SELECT LastName, FirstName FROM Employees"

set rst = Conn.Execute(sql)

Populate rst with the data from the LastName and FirstName fields in the Employees table. The While loop then moves through each record in rst, displaying the contents of the LastName and FirstName fields.

This simple example returns two data fields from every record in the table. You will rarely retrieve so much data and will more likely limit the retrieved information to some criteria. To illustrate, example2.asp restricts the SQL statement to retrieve only those records where the LastName field equals the string 'Reid':

Sql = "SELECT LastName, FirstName FROM employees WHERE LastName = 'Reid'"

The If statement checks rst for records, and the EOF property will be true if rst is empty. In which case, the code displays an error message, and Response.End Kills the script. Any ASP or HTML after this statement will not be executed.

Formatting tables

The first two ASP files deal with simple data retrieval. The third grabs the data from the LastName and FirstName fields in the Employees table and format it in a simple HTML table. The formatting begins with

<caption align='"top"'>Employee List from Northwind. Demo</caption>

</table>

This starts the table and adds a title above it, while the statements

<tr>
<th>Last Name</th>
<th>First Name</th>
</tr>

Add headings to the table columns. The <tr></tr> and <td></td> tags create table rows and cells while the While loop populates it. The bgcolor="#00FFFF" attribute adds a bit of color to each row.

Click Next for the modify-data-online page.

example1 asp

Example1 ASP

Paste this code into a file and save it as example1.asp in your sample folder.

<html> 
 <body> 
 <%Set Conn = Server.CreateObject("ADODB.Connection") 
Conn.Open "NorthwindDSN" 
sql = "SELECT LastName, FirstName FROM Employees" 
Set rst = Conn.Execute(sql) %> 
 <%Do While Not rst.EOF%>
    <%=rst("LastName")%>
    <%=rst("FirstName")%>
    <%=rst.MoveNext 
Loop
 Set Conn = Nothing
 Set rst = Nothing %> 
 </body> 
 </html>

Click Next for example2.asp

example2 asp

Example2 ASP

Paste this code into a file and save it as example2.asp in your sample folder.

<html>
 <body> 
 <%Set Conn = Server.CreateObject("ADODB.Connection") 
Conn.Open "NorthwindDSN" 
sql = "SELECT LastName, FirstName FROM Employees WHERE LastName = 'Reid' " 
Set rst = Conn.Execute(sql) 
If rst.EOF Then
      Response.Write "No employee records available"
      Conn.Close
      Set Conn = Nothing
      Response.End 
End if %> 
 <%Do While Not rst.EOF%> 
     <%=rst("LastName")%> 
     <%=rst("FirstName")%>   
     <%=rst.MoveNext 
Loop 
Set Conn = Nothing 
Set rst = Nothing %> 
 </body> 
 </html>

Click Next for example3.asp

example3 asp

Example3 ASP

Paste this code into a file and save it as example3.asp in your sample folder.

<html>
 <body>
 <%Set Conn = Server.CreateObject("ADODB.Connection") 
 Conn.Open "NorthwindDSN" 
 Sql = "SELECT LastName, FirstName FROM Employees " 
 Set rst = Conn.Execute(Sql) 
 If rst.EOF Then  
   Response.Write "No employee records available"
   Conn.Close
   Set Conn=Nothing
   Response.End 
End If %> 
 <table border="1">
 <caption align="top">Employee List from Northwind. Demo </caption>
 <tr>  <th>Last Name </th>  <th>First Name </th>  </tr>
 <%Do While Not rst.EOF%> 
    <tr bgcolor="#00FFFF">  <td>  <%=rst("LastName")%>  </td>  <td>  <%=rst("FirstName")%>  </td>  </tr> 
  <%rst.MoveNext 
Loop 
Set Conn = Nothing 
Set rst = Nothing %> 
 </table> 
 </body>
 </html>

Go to Main Page

Modify Access Data Online

Introduction

In the previous data basics column, we showed you how to retrieve data from an access table over the Web using ASP scripts. Retrieving and viewing data are just two of the tasks that you will need in an interactive database application. Now we are going to venture into making database changes online, using HTML forms and ASP scripts to insert new data and modify and delete existing information. We will assume that you have gone through the steps in the prior lesson and have installed a server, created a shared directory \inetpub\wwwroot\sample containing the Northwind database and established a DSN connection to it.

The examples that follow will use seven more sample files. As in the previous lesson, you should create each one by copying and pasting the code into Notepad and saving it with the indicated filename in your \inetpub\wwwroot\sample directory.

Form function

Most of these samples operate by sending HTML forms to the browser. Formscollect data from the user to send to programs on the server, in this case, our ASP scripts. The <form></form>element's action attribute indicates the URL of the server program that will receive the data. How the data is sent depends on the method attribute: post passes the data invisibly, while get appends the data in the server program's URL. After the data is processed, the server program sends a result page back to the browser.

A collection of controls (also called fields or inputs) contains the form's data. Control elements include <input />, <select>, <textarea> and others. Depending on the type of control, a user may freely enter its data or select a value from limited options. Some inputs even have fixed values and are present just to pass information from one server program to the next. Each control has a name attribute used to label its data when sent to the server.

Click Next for inserting-data

Inserting data

Inserting Data

This example inserts a new record, with CategoryName and Description values, in the categories table of the Northwind database. It requires the files insert_form.html and insert.asp. The first displays a simple HTML form with two text box controls, while the second is an ASP script that actually inserts the data.

Once you've installed the files, open your browser and enter the URL. http://localhost/sample/insert_form.html to view the HTML form. The form doesn't have an input for CategoryID because that is an AutoNumber field that Access fills in automatically. You'll want to avoid that common mistake don't include AutoNumber fields in your form unless you take some measures to prevent the user from trying to change that data. The form also omits the Picture field, as upload images and other large objects are subjects for another article.

In insert_form.html, the statement

<form name="frmInsert" action="insert.asp"></form> Indicates that the script insert.asp will process the data. The first input

<input type="text" name="txtCategory" /> Accepts a new category name to pass as the field txtCategory, while the code

Produces the multiple-line Description field, 10 rows high by 40 characters wide, for a new description to pass as the field txtDescription

Enter a new category name and description and click the Insert button to call the ASP script insert.asp. If the task is successful, the script will display the confirmation message

You have successfully inserted a new record.

Notice that the browser's location bar now displays the name of the ASP file. If you open the Northwind database in Access, you will find that the categories table now contains a new record with the name and description that you added.

The ASP script assigns the contents of the txtCategory and txtDescription controls in the HTML form to two variables named category and Description, respectively. Because all ASP variables are Variants, you don't need to specify a data type when declaring a variable. In fact, you don't have to declare the variables at all; however, doing so does make your script more readable.

It then inserts those variables into a SQL statement:

Sql = "INSERT INTO Categories (CategoryName, Description)"

Sql = sql & " Values(" & Category &" , " & description & " )"

These lines construct an INSERT INTO statement using the submitted form values. If either was left blank, the script returns a generic error.

The FormsCollection collection

When an HTML form submits data, ASP stores it in the Request.FormCollection collection, which you can manipulate with the syntax

Request.form(dataItem) Where dataItem is the numerical index or name of a data field, such as txtCategory. You can also count the number of fields with the request.form.Count property and iterate through them with a statement such as

For x = 1 to request.form.count
...
Next

Click Next for Modifying-data

modifying data

Modifying Data

Modifying data is a bit more complicated because the script must make certain that the appropriate record is in fact available, in other words, that it exists and is not locked by a security setting or other transaction. So this example uses three scripts. The first, amend_select.asp, generates an HTML form listing the available categories from the Categories table. It passes your selection to the second, amend_change.asp, which creates a new form displaying the selected record's current values as editable fields. Those are submitted to the third script, amend.asp, which updates the actual database record accordingly with the new values.

To try these scripts out, install the files and call up the following URL:http://localhost/sample/amend_select.asp

It should display a simple drop-down control, a <select></select> element named ProdCats with an <option></option> for each CategoryName in the categories table. Select a category and click the Submit button.

This will execute amend_change.asp, which uses the submitted ProdCats to build a SQL query and generate a new form with the appropriate record's CategoryID, CategoryName, and Description values. Modify the name and description and click Change to send the new values to amend.asp. That script will construct and execute a SQL UPDATE statement from the new values. If successful, it returns the confirmation message: You have successfully amended a record.

Opening the Categories table in access should reveal your changes.

Note that the second template, amend_change.asp, puts the CategoryID in a hidden input named txtID. We need to pass the CategoryID value to amend.asp in order to uniquely identify the record, but because it is an AutoNumber field, we don't want the user to modify it. By using a hidden input, the field is passed along, but the user never sees or changes it. This is the standard way to include a record's unique, automatically generated identifier in an HTML form.

Click Next for Deleting-data

deleting data

Introduction

Deleting really requires more thought in real-world applications than we will demonstrate here. Instead of deleting data, consider archiving it in a different table for historical purposes. Or you might simply flag a record as inactive without actually removing it from the database. Actually deleting data is a permanent solution, so approach it with caution.

Here we use the final two scripts. As with modifying, deleting first requires that you ascertain that a given field is available. So the first script, delete_select.asp, displays a list of discontinued products from the Northwind database's Products table. Clicking that product will execute delete.asp, which actually deletes the record from the table.

Cascade Delete Option

But before doing so, we must turn on the Cascade Delete option in the relationship between the Products table and the Order Details table. Otherwise, the script will return an error. To do so, open the Northwind database in Access and:

Click the Relationships button to open the Relationships window.
Right-click the join line between the Products and Order Details tables.
Choose Edit Relationships from the resulting submenu.
In the Edit Relationships dialog box, check the "Cascade delete related records" option.
Click OK, close the relationship window, and save the database.

This is an accommodation to simplify the example. When making deletions in a real application, it is better not to permanently turn on the cascade Delete option. Such a drastic step might destroy valid corresponding data in the Order Details table, and just because you are discontinuing the item doesn't mean that you never actually supplied it to a customer. So let this simplified example demonstrate how far seemingly simple decisions can reach.

That said, you are ready to delete some data. Point your browser to the following URL: http://localhost/sample/delete_select.asp

You should see a list of discontinued products, which delete_select.asp generates by selecting from Products where Discontinued=True. Notice that this time, the page isn't an HTML form instead, each product is simply a link with delete.asp and an appended ProductID value for its href.

Click any item to delete it from the Products table (and any corresponding records from the Order Details table). This executes delete.asp, which grabs the < ProductID value from the URL as Request.QueryString("ProductID")

Using this value, it constructs a Delete SQL statement, executes it, and confirms with the message: The record has been deleted.

Opening the Products table in Access should reveal that the record is indeed gone.

Click Next for insert-form.html

Friday, April 13, 2007

insert form html

Insert Form HTML Code

<html>
<head>
<title>Insert new data</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>This form will insert a new record into the Category table
of the Northwind database.<hr></p>
<form name="frmInsert" method="post" action="insert.asp">
<p>
<b>Category Name</b><br>
<input type="text" name="txtCategory">
</p>
<p>
<b>Description</b><br>
<textarea name="txtDescription" rows="10" cols="40" ></textarea>
</p>
<input type="submit" name="Submit" value="Insert">
</form>
</body>
</html>

Click Next for insert.asp

insert asp

Insert ASP Code

<% Dim Category 
Dim Description 
Dim cn 
Dim sql 
Category = request.form("txtCategory") 
Description = request.form("txtDescription") 
Set cn = Server.CreateObject("ADODB.Connection") 
cn.Open "NorthWindDSN" 
sql = "INSERT INTO Categories (CategoryName, Description) "  
sql = sql & " values( '" & Category & "' , '"  
sql = sql & Description & "' )" 
cn.Execute(sql) 
Response.write "You have successfully inserted a new record." 
cn.Close 
Set cn = nothing %>

Click Next for amend-select.asp

amend select asp

Amend Select ASP Code

<html>
<head>
<title>Select category to change</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>Please select which category you wish to modify.<hr></p>
<%
Dim rst
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open "Categories", "DSN=NorthWindDSN"
%>
<form name="frmFind" method="get" action="amend_change.asp">
<select name = "ProdCats" Size = "1">
<%
DO WHILE NOT rst.EOF
 Response.Write "<option value= '" & rst("CategoryID") & "'>"
 Response.Write rst("CategoryName") & "</option>"
 rst.MoveNext
 Loop
 rst.close
 set rst=Nothing
 %>
 </select>
 <input type="submit">
 </form>
 </body>
 </html>

Click Next for amend-change.asp

amend change asp

Amend Change ASP Code

<html>
<head>
<title>Change category</title>
</head>
<body bgcolor='"#FFFFFF"' text='"#000000"'>
<%
Dim rst
Set rst = Server.CreateObject("ADODB.Recordset")
Dim strSQL
Dim varList
varList = Request.QueryString("ProdCats")
strSQL = "SELECT CategoryName, Description, CategoryID "
strSQL = strSQL & "From Categories WHERE CategoryID = " & varList
rst.Open strSQL, "DSN=NorthWindDSN"
%>
<p>This record is available for updating.</p>
<hr />
<form name="frmCategory" method="post" action="amend.asp">
<input type="hidden" name="txtID" value="<%=rst("CategoryID")%>" />
<p><b>Category</b></p>
<input type="text" name="txtCategory" value="<%=rst("CategoryName")%>" />
<p><b>Description</b></p>
<textarea name="txtDescription" cols="25" rows="15" />
<%=rst("Description")%>
</textarea>
<input type="submit" name="Submit" value="Change" />
</form>
</body>
</html>

Click Next for amend.asp

Wednesday, April 4, 2007

amend asp

Amend ASP Code

<%
Dim Category
Dim Description
Dim cn
Category = request.form("txtCategory")
Description = request.form("txtDescription")
catID = request.form("txtID")
strSQL="UPDATE Categories 
SET CategoryName='" & Category & "'",Description='" & Description & "'"
strSQL = strSQL & " WHERE CategoryID =" & CatID
set cn = Server.CreateObject("ADODB.Connection")
cn.Open "NorthWindDSN"
cn.Execute(strSQL)
Response.write "You have successfully amended a record."
cn.Close
Set cn = nothing
%>

Click Next for delete.asp

delete asp

Delete ASP Code

<html>
<head>
<%
Dim rst
Set rst = Server.CreateObject("ADODB.Recordset")
Dim strSQL
Dim DeleteID
DeleteID = Request.QueryString("ProductID")
strSQL = "Delete * FROM Products Where ProductID = " & DeleteID
rst.Open strSQL, "DSN=NorthWindDSN"
%>
</head>
<body>
<h4>The record has been deleted.</h4>
</body>
<html>

Click Next for delete-select.asp

Tuesday, April 3, 2007

delete select asp

Delete Select ASP Code

<%strSQL = "SELECT ProductID, ProductName "
 strSQL = strSQL & "FROM Products "
 strSQL = strSQL & "WHERE Discontinued=True"
 Set rst= Server.CreateObject("ADODB.Recordset")
 Set cnn= Server.CreateObject("ADODB.Connection")
 cnn.open "NorthWindDSN"
 rst.open strSQL, cnn
 %>
 <html>
 <head>
 <title>Delete product</title>
 </head>
 <body bgcolor="#FFFFFF" text="#000000">
 <p>Click the product that you want to delete.</p>
 <hr>
 <%
 Do While Not rst.EOF
 %>
     <a href="delete.asp?ProductID=<%=rst("ProductID")%>"> <%=rst("ProductName")%></a>
 <%
 rst.Movenext
 Loop
 %>
 </body>
 </html>

Go Top

Friday, April 20, 2007

MICROSOFT ACCESS AND INTERNET

Install the local server

To install PWS on Windows 98:

To install IIS on Windows 2000:

Earlier Post Link References:

Set up the server folder and ASP files

Create the example ASP Files

Creating a DSN Connection

To create a DSN on a Windows 98 system:

To create a DSN on a Windows 2000 system:

Connecting the Database through ODBC

Formatting tables

Example1 ASP

Example2 ASP

Example3 ASP

Introduction

Form function

Inserting Data

Modifying Data

Introduction

Cascade Delete Option

Friday, April 13, 2007

Insert Form HTML Code

Insert ASP Code

Amend Select ASP Code

Amend Change ASP Code

Wednesday, April 4, 2007

Amend ASP Code

Delete ASP Code

Tuesday, April 3, 2007

Delete Select ASP Code

MS-Access and Internet.

About Me

Statcounter