|
Bucaro TecHelp Newsletter
Maintain Your Computer and Use it More Effectively
to Design a Web Site and Make Money on the Web.
~ ~ ~ March 28, 2006 Volume 6 Number 3 ~ ~ ~
|
Create an Access Database Using Only Notepad
Part 8 Code to Add Records to a Database
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 7 of this series you used Notepad to enter the code to create a database with only one
table, the Customers table. In this article you'll use Notepad to enter the code to add records
to the Customers table. Navigate to the folder where you saved the database from the last article
and create a text file there. In that text file, type in the tags that create an HTML Application,
as shown below.
<html>
<body>
<hta:application>
<script language="VBScript">
</script>
</body>
</html>
We'll enter our code between the <script language="VBScript"> and </script>
tags, beginning with the declaration of three variables as shown below.
Dim objConn, strConnection, objRS
Below the variable declarations, enter the three lines shown below, which open a connection to database.
Set objConn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Order Entry\orders.mdb'"
objConn.Open strConnection
In the second line shown above, replace the path with the path to the folder where you
saved your database. Just below the lines shown above, enter the line shown below, which
creates an ADO RecordSet object.
Set objRS = CreateObject("ADODB.RecordSet")
As its name implies, the RecordSet object holds a set of records from the table. Just below
the lines shown above, enter the line shown below, which fills the RecordSet with all the
records from the Customers table (of course their aren’t any records in the table yet).
objRS.Open "SELECT * FROM tblCustomers", objConn, 3, 3 ' adOpenStatic, adLockOptimistic
We'll be using the RecordSet object's Open method quite a bit in this series of examples,
so lets examine it a bit right now. You may recognize that the first parameter in quotes
is actually an SQL (Structured Query Language) statement. It basically says "select all
records in the Customers table. The second parameter is the connection object.
The third parameter is a numeration for the "cursor" type. In the comment I note that the
number 3 defines adOpenStatic. This creates a "static" cursor. In other words if someone
else modifies the records while you're looking at them, you won't see the change (until you
repeat the request for the recordset).
The fourth parameter is a numeration for the record "locking" type. The number 3 defines
optimistic locking which locks the records only during the call to the Update method.
Another type of record locking, Pessimistic locking (adLockPessimistic) would keep the
records locked until we released them, preventing anyone else from modifying the records
while you're looking at them (we don't need that).
Just below the objRS.Open line, enter the line shown below, which sets RecordSet object
to add a new record to the table.
objRS.AddNew
Just below the objRS.AddNew line, enter the lines shown below, which defines a record for
a customer named "Jimmy Abalone".
objRS("FirstName") = "Jimmy"
objRS("LastName") = "Abalone"
objRS("Street") = "Main"
objRS("City") = "Phonix"
objRS("State") = "AZ"
objRS("ZipCode") = "85268"
objRS("Phone") = "1234561234"
objRS("Email") = "me@mydomain.com"
objRS("Notes") = "Hello There!"
Just below those line, enter the line shown below, which actually adds the new record
to the Customers table.
objRS.Update
Finally, enter the lines shown below, which close the recordset and the database connection
and clean up the memory objects.
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
Now save the file with a .hta extension, for example addCustomer.hta. Then double click on
the file name to execute the script. A blank window will open (obviously we're not getting fancy
here) and customer Jimmy Abalone's record will be added to the Customers table.
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).
If your script ran without error, I know it's a bit of a let down to have successfully created
an Access database and added a record to it without being able to read the record back.
If you actually have Microsoft Access installed on your computer, you can open the database
in Design View, double-click on the Customers table and view Jimmy Abalone's record. Otherwise,
in the next part of this series, we'll create a script to display the records in 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