ID: Q165479
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
This article demonstrates two techniques for creating a data entry form in ASP format.
NOTE: This article contains information about editing ASP files, and assumes that you are familiar with editing ASP files. Microsoft Access Product Support professionals do not support customization of any HTML, HTX, IDC, or ASP files.
When you export to ASP format a Microsoft Access 97 form that has its DataEntry property set to Yes, the ASP file displays the underlying records when you open it in a Web browser. That is because, when Microsoft Access 97 exports a form to ASP, it uses the form's RecordSource property to generate a SQL Statement for the ASP file. It also automatically generates the VBScript for all of the buttons on the form that enable you to move between records, add new records, and delete existing records. The export to ASP feature in Microsoft Access 97 was not designed to create a data entry form that does not display records.
There are two different techniques you can use to create a data entry form with ASP files:
Following is an example of how to create an HTML Insert Form that uses ASP files to insert records into a Microsoft Access 97 database.
Creating the ASP Files in Microsoft Access:
1. Start Microsoft Access 97 and open the sample database Northwind.mdb.
2. Create a new query called EnterShipper based on the Shippers table:
Query: EnterShipper
-------------------
Type: Select Query
Field: CompanyName
Table: Shippers
3. On the Query menu, click Parameters.
4. Type the following in the Query Parameters dialog box, and then click
OK.
Parameter Data Type
----------------------------
[EnterName] Text
5. Save the EnterShipper query and close it.
6. Select the EnterShipper query in the Database window, and then click
Save As/Export on the File menu.
7. In the Save As dialog box, click "To an External File or Database," and
then click OK.
8. In the "Save Query 'EnterShipper' In" dialog box, select Microsoft
Active Server Pages (*.asp) in the Save As Type box, and type
EnterShipper.asp in the File Name box. Note the folder where the
exported files will be stored, and then click Export.
9. In the Microsoft Active Server Pages Output Options dialog box, type
the name of a System DSN on your Web server that points to the
Northwind sample database.
For more information about how to define a system DSN, search the Help
index for "ODBC, setting up data sources," and see the following
article in the Microsoft Knowledge Base:
ARTICLE-ID: Q159682
TITLE : ACC97: "Data Source Name Not Found" Err Msg Opening Web
Page
Web server location where your ASP files will be stored. For example,
if you will store the ASP files in the \ASPsamp folder on the \\PubTest
server, type http://pubtest/aspsamp/ in the Server URL box. Click OK.
output creates two files: EnterShipper.HTML and EnterShipper.asp.
server computer where you have both Read and Execute permission. Read
permission is necessary to browse the HTML file, and Execute permission
is necessary to run the ASP file. This must be the same folder
indicated by the Server URL that you entered in step 10.
For more information about configuring Microsoft Internet Information
Server (IIS) permissions, please refer to the IIS Help Index, and see
the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q162975
TITLE : ACC97: Permissions Necessary to View HTML, IDC, and ASP
Files
1. Use Notepad or another text editor to open the EnterShipper.ASP file.
2. You need to modify the ASP script so that it will use the value passed
from the HTML form and insert it into the Shippers table of the
Northwind sample database. Near the top of the ASP file, you will see
some script that looks as follows:
<%
sql = "SELECT Shippers.CompanyName FROM Shippers "
If cstr(Param) <> "" And cstr(Data) <> "" Then
sql = sql & " WHERE [" & cstr(Param) & "] = " & cstr(Data)
End If
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
%>
Change this part of the script to the following:
<%
sql = "INSERT into Shippers (CompanyName) values ('" &_
Request.QueryString("[EnterName]") & "');"
conn.execute sql
sql = "SELECT Shippers.CompanyName FROM Shippers"
If cstr(Param) <> "" And cstr(Data) <> "" Then
sql = sql & " WHERE [" & cstr(Param) & "] = " & cstr(Data)
End If
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
%>
4. Start Microsoft Internet Explorer 3.0, or another Web browser program.
5. Type the URL in the address box of your Web browser to view
EnterShipper.HTML. For example, if you saved your files in a subfolder
called ASPsamp in the \InetPub folder on your Web server, type:
http://<servername>/ASPsamp/EnterShipper.HTML
Note that the URL depends upon the relative path to your files on the
Web server.
[EnterName] box and a Run Query button. Type a name into the box and
then click the Run Query button. A new record with the name you typed
is inserted into the Shippers table in the Northwind sample database.
Note that the ASP file requeries the database and returns an HTML table
that displays the records in the Shippers table.
Following is an example of how to create a Microsoft Access 97 form with a record source that returns no records. Then you export the form to ASP format and modify it to create a data entry form.
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.
Creating the ASP Files in Microsoft Access:
1. Start Microsoft Access 97 and open the sample database Northwind.mdb.
2. Open the Customers form in Design view.
3. Set the form's RecordSource property to an SQL statement that will not
return any records:
SELECT * from Customers WHERE False
4. Save the Customers form and close it.
5. Select the Customers form in the Database window, and then click Save
As/Export on the File menu.
6. In the Save As dialog box, click "To an External File or Database," and
then click OK.
7. In the "Save Form 'Customers' In" dialog box, select Microsoft Active
Server Pages (*.asp) in the Save As Type box, and type Customers.asp in
the File Name box. Note the folder where the exported files will be
stored, and then click Export.
8. In the Microsoft Active Server Pages Output Options dialog box, type
the name of a System DSN on your Web server that points to the
Northwind sample database.
For more information about how to define a system DSN, search the Help
index for "ODBC, setting up data sources," and see the following
article in the Microsoft Knowledge Base:
ARTICLE-ID: Q159682
TITLE : ACC97: "Data Source Name Not Found" Err Msg Opening Web
Page
9. In the Server URL box, type the uniform resource locator (URL) of the
Web server location where your ASP files will be stored. For example,
if you will store the ASP files in the \ASPsamp folder on the \\PubTest
server, type http://pubtest/aspsamp/ in the Server URL box. Click OK.
Customersalx.asp, to a folder on your Web server computer where you
have Execute permission. This must be the same folder indicated by the
Server URL that you entered in step 9.
1. Use Notepad or another text editor to open the Customersalx.asp file.
2. You must modify portions of the ASP script so that the only navigation
button that displays is the Add New button. At the bottom of the ASP
file you will see a series of <OBJECT> tags with the following Object
IDs: nav_btn_MoveCancelUpdate, nav_btn_MoveDeleteRecord,
nav_btn_MoveCommitRecord, nav_btn_MoveLastRecord,
nav_btn_MoveNextRecord, nav_btn_MovePrevRecord, and
nav_btn_MoveFirstRecord. Delete each of these <OBJECT> tags, making
sure that you delete everything between the beginning <OBJECT> and the
ending </OBJECT> tag.
NOTE: Make sure you do not delete the <OBJECT> tag with the
nav_btn_MoveAddRecord Object ID.
Sub UpdateRefreshBtn()
nav_btn_MoveCancelUpdate.Caption = "Cancel"
End Sub
Delete the line between the Sub and the End Sub lines so that it looks
as follows:
Sub UpdateRefreshBtn()
End Sub
5. Start Microsoft Internet Explorer 3.0, or another Web browser program.
6. Type the URL in the address box of your Web browser to view
Customers.asp. For example, if you saved your files in a subfolder
called ASPsamp in the \InetPub folder on your Web server, type:
http://<servername>/ASPsamp/EnterShipper.HTML
Note that the URL depends upon the relative path to your files on the
Web server.
letter Customer ID into the customer ID box. Type a company name into
the company name box. Click the Add New button. A new record with the
name you typed is inserted into the Customers table in the Northwind
sample database.
For more information about exporting ASP files, search the Help Index for "ASP files" or ask the Microsoft Access 97 Office Assistant. In addition, please refer to your ASP documentation that is installed when you install Microsoft Internet Information Server (IIS) version 3.0.
Additional query words: dynamic update intranet Internet
Keywords : kbinterop IntPubWiz IntAsp
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: May 18, 1999