|
Bucaro TecHelp Newsletter
Maintain Your Computer and Use it More Effectively
to Design a Web Site and Make Money on the Web.
~ ~ ~ April 13, 2006 Volume 6 Number 4 ~ ~ ~
|
Create an Access Database Using Only Notepad
Part 9 Code to Display the Records in 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 8 of this series you used Notepad to enter the code to add records to the Customers table
of the Order Entry database. In this article you'll use Notepad to enter the code to display the
records in the Customers table. 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 HTML Application, as shown below.
<html>
<body>
<HTA:APPLICATION NAVIGABLE = "yes">
</body>
</html>
Just below the HTA:APPLICATION tag, add the code shown below which creates an html
div and a delineates a VBScript code block.
<div id="txtData"></div>
<script language="VBScript">
</script>
We'll enter code between the <script language="VBScript"> and </script>
tags, that will read all the records in the Customers table, appending the data to
a text string as it reads, and then it will place the text string (which will actually
be html code) into the div for display.
The first line of code we need to enter, just below the opening script tag,
is the declaration of four variables as shown below.
Dim objConn, strConnection, objRS, strHtml
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")
The RecordSet object holds the 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.
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.
You'll find a description of its syntax and parameters in the Part 8 of this series.
Just below the objRS.Open line, enter the line shown below, which initialized the text
string with an html tag to begin rendering a table.
strHtml = "<table border=1>"
Next enter the block of code shown below.
While Not objRS.EOF
strHtml = strHtml & "<tr><td>"
strHtml = strHtml & objRS("CustomerId") & "</td><td>"
strHtml = strHtml & objRS("FirstName") & "</td><td>"
strHtml = strHtml & objRS("LastName") & "</td><td>"
strHtml = strHtml & objRS("Street") & "</td><td>"
strHtml = strHtml & objRS("City") & "</td><td>"
strHtml = strHtml & objRS("State") & "</td><td>"
strHtml = strHtml & objRS("ZipCode") & "</td><td>"
strHtml = strHtml & objRS("Phone") & "</td><td>"
strHtml = strHtml & objRS("Email") & "</td><td>"
strHtml = strHtml & objRS("Notes") & "</td><td>"
objRS.MoveNext
Wend
This code creates a While/Wend loop. The code Not objRS.EOF defines that,
while the loop is NOT at the end of the record set, execute the statements between the
While and Wend commands. The statements between the While and Wend
commands append the fields of the records to the text string, along with more html
code to build the table.
Just below the code for the While/Wend loop, add the line shown below, which
appends the table closing tag to the text string.
strHtml = strHtml & "</table>"
Now we have all records, along with all the code necessary to build a table around
the records, stored in the text string. SO add the lines shown below, which is the
code to close the record set, close the connection, and clean up memory.
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
The last code we need to add is the line shown below, which places the contents of
the string inside the div.
txtData.InnerHtml = strHtml
Now save the file with a name with an .hta extension, for example showCustomer.hta.
Then double-click on the file name to execute the script. A window will open displaying the
data in the Customers table. Obviously, if you didn't add a least one record to the
Customers table, as described in Part 8 of this series, the window will be blank.
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, you have successfully created an Access database and
added a record to it and displayed that record. Congratulations!
Now, I'm going to ask you to do something strange. Delete all the files that you
created related to this example, including the database. I'm going to give you the code
to create the Order Entry database, including all five tables, which you can download
by clicking here.
Since you already know how to program code to add records to a table and code to
display records in a table, there's nothing to stop you from creating and using the entire
database right now. However, you might prefer to just hang on to the code for a while
because in the next part of this series we'll begin creating forms that let you add records
to the database without having to recode the module each time.
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