|
Bucaro TecHelp Newsletter
Maintain Your Computer and Use it More Effectively
to Design a Web Site and Make Money on the Web.
~ ~ ~ May 16, 2006 Volume 6 Number 5 ~ ~ ~
|
Create an Access Database Using Only Notepad
Part 10 Code for Add Customer Form
By Stephen Bucaro
In this series of articles, you'll learn how to create a Microsoft Access database using only
a basic ASCII text editor, like Windows Notepad. That's right, you don't need Microsoft's Office
suite, or Access database application, or Visual Basic programming environment in order to create
an Access database. All you need is your handy text editor.
In this example we will be designing an Order Entry System database. In previous parts of this
series you learned how to: create an HTML Application (HTA); determine if the required Microsoft
Data Access Components (MDAC) were installed on your computer; design database tables following
rules of normalization; and about ActiveX Data Objects Extended (ADOX) Field Properties.
- If you missed previous parts of this series of articles, you can still collect the entire series.
Past issues of the newsletter are available in the Archive section of bucarotechelp.com
In Part 8 of this series you used Notepad to enter the code to add records to the Customers
table of the Order Entry database. The problem with this code was that you had to edit the code
each time you wanted to add a new customer. The reason I did it that way was to show you the bare
bones basic code required to manipulate an access database. But editing the code each time you
want to add a new customer is extremely impractical. In this article you'll use Notepad to enter
the code to create an AddCustomer form that lets you add records to the database without having
to recode the module each time.
- In Part 9 of this series you used Notepad to enter the code to display the records in the
Customers table. For now, you'll still need to use that code to view the customers that you add.
To create an AddCustomer form, navigate to the folder where you saved the database and create a
text file there. In that text file, type in the tags that create an HTA Application, as shown below.
<html>
<head>
<HTA:APPLICATION NAVIGABLE = "yes">
</head>
<body>
</body>
</html>
In the body section of the HTML page (in other words between the <body> tag and
the </body> tag) enter the html code to create a form, as shown below.
<form id="AddCustomerForm">
First Name: <input type="text" name="FirstName"><br />
Last Name: <input type="text" name="LastName"><br />
Street: <input type="text" name="Street"><br />
City: <input type="text" name="City"><br />
State: <input type="text" name="State"><br />
Zip: <input type="text" name="ZipCode"><br />
Phone: <input type="text" name="Phone"><br />
Email: <input type="text" name="Email"><br />
Notes: <input type="text" name="Notes"><br />
<input type="button" value="Add" onclick="addCutomer()">
</form>
Now save the file with a name with an .hta extension, for example AddCustomerForm.hta.
Then double-click on the file name to execute the script. A window will open displaying the
AddCustomer form. The form doesn't actually work yet because we have not added the VBScript
code to access the database.
In the head section of the HTML page, just below the HTA:APPLICATION tag, add the
code shown below which delineates a VBScript code block.
<script language="VBScript">
</script>
- Look back at the form's submit button, and you'll see that the button's
onclick event executes a function called "addCutomer".
We'll enter code between the <script language="VBScript"> and </script>
tags, that will pull the entries from the form text boxes and place that data in a new
record in the Customers table. The first line of code we need to enter, just below the
opening script tag, is the statement to create a subroutine named "addCutomer" as shown below.
Sub addCutomer()
The first line of code in the addCutomer Sub is the declaration of four variables as shown below.
Dim objConn, strConnection, objRS, TheForm
The next line, which assigns the entries from the form text boxes in the TheForm
variable is shown below.
Set TheForm = Document.forms("AddCustomerForm")
Below the variable assignment, enter the lines shown below, which open a connection to
database, create a recordset object, and call the recordset object's AddNew
method in preparation to add a new record.
Set objConn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Order Entry\orders.mdb'"
objConn.Open strConnection
Set objRS = CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM tblCustomers", objConn, 3, 3
objRS.AddNew
- We explained this code in Part 9 of this series, so I will not be explaining it in
detail here, but it's important to change the Source= path in the connection
string to the path to your database.
Next enter the lines shown below, which take the data from the individual form text
boxes, via the TheForm variable, and places them in the proper fields in the
new database record.
objRS("FirstName") = TheForm.FirstName.Value
objRS("LastName") = TheForm.LastName.Value
objRS("Street") = TheForm.Street.Value
objRS("City") = TheForm.City.Value
objRS("State") = TheForm.State.Value
objRS("ZipCode") = TheForm.ZipCode.Value
objRS("Phone") = TheForm.Phone.Value
objRS("Email") = TheForm.Email.Value
objRS("Notes") = TheForm.Notes.Value
objRS.Update
The last code statement above calls the recordset object's Update method to
actually add the new record to the database.
Finally add the lines shown below to close the record set, close the connection,
clean up memory, and mark the end of the addCutomer subroutine.
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
End Sub
Now save the file and execute it again. A window will open displaying the AddCustomer
form. This time when you enter data in the form and click on the [Add] button, the
data will actually be added to the database.
- If you received an error message when you executed the file, then (assuming that you
where previously able to successfully create the database) check your code for typos.
In this article you learned how to create a webpage form to add data to an Access database.
This code is by no means complete. You could add code to validate the form entries, to handle
errors, to provide feedback as to the results of the operation, to make the form a little
more fancy, and so on. I'm proving bare bones code for educational purposes.
In the next part of this series of articles, you'll write code to create a form that
lets you delete customers from the customers database. You'll also create a webpage that
will work as a "switch board" for the database, in other words, a page that gives you
access to all the modules of the database.
Disclaimer : Although every precaution has been taken in the preparation of this
material, Bucaro TecHelp assumes no responsibility for errors or omissions. Neither is any liability
assumed for damages resulting from the use of the information contained herein. This information is
provided with the understanding that Bucaro TecHelp is not engaged in rendering legal, medical,
accounting or other professional service. If legal advice or other expert assistance is required, the
services of a competent professional person should be sought. By using this material, the user assumes
complete responsibility for any and all damages resulting from that use.
| 
Free Downloads
October 3, 2006