|
Building an Application from the Ground Up (All Because I Love Italian!)
I recently started a course in Italian, and I'd like to tell
you why. First, I'm half Italian-my maternal
grandmother and grandfather came over on the boat.
Second I would love to be able to converse with my
clients, friends and acquaintances in Italian. Third,
Italian is the most beautiful language in the world (I
know some of you might argue with this last point and
might make a case for languages such as French or
that language they speak in Britain, but for my money
there is no more beautiful spoken word than the
language of Italy).
One of things about learning a new language is that
there is a very clear component of memorization.
Among other things, you have to learn a whole new
vocabulary and a whole new set of syntax rules.
Unless you're in the happy circumstance of being
immersed in a culture where the language is spoken,
memory plays a critical role.
Back when I was a full-time student, I found that one of
the most effective ways to memorize discrete facts
was through the use of flash cards --those 3 x 5 cards
with a question on one side and the answer on the
other.
Being a software guy, I really wanted a program for my
computer that would create electronic (or virtual)
flashcards.
I looked around for a bit on the Internet to see if there
was already an application that would meet my
needs. I was especially looking for a Microsoft Access
application since I've found that every time I buy a
canned program there are features that I'm either not
very happy with or which I think are really important
that are missing from the program. Almost inevitably I
end up writing my own software in Microsoft Access.
With this month's newsletter I intend to begin the
process of showing you how to build an application
from the ground up. I'll share with you my best
practices and rationale for making various decisions
along the way. I believe in this way you'll be able to
see how an effective and efficient application is built.
| Infrastructure |
 |
|
When I begin a new application I start with a template
that has evolved based on past projects. This
approach helps me make sure that I don't reinvent the
wheel for every task I need to accomplish.
With this template I'll get a standardized look for my
forms and reports; the template will include a
customized tool set (many of which have been
described in prior versions of the Access Wizard) that
will help me manage lists within the application. This
template also contains generic code that will make my
development more efficient. I won't be going into great
detail about these tools in this series of articles,
however know that what I'm presenting represents the
core elements of building an effective application.
The other important factor is the name for the
application. As a working name I'm going to use
FashCards.mdb. The version that I'll build this in is
Microsoft Access 2000. Although this is neither the
most recent nor most powerful version of the Access,
I've chosen 2000 because I may wish to share this
with other people. Access 2000 tends to be a version
that runs with most Access installations today. An
application developed in Access 2000 will run on
newer versions, but if I develop the application in a
newer version it is not likely to run on previous
versions.
|
| Table Design - The Foundation |
|
|
If you're a regular Access Wizard reader, you know that
I'm an absolute fanatic for designing the tables
correctly. This comes before building forms or reports
and before writing code. I believe that getting the
tables right is as critical to a database application as
building a solid foundation is in the construction of a
new home.
So before we begin to design the tables we have to
give some thought to what we want our application to
do.
First we want to be able to store words in both English
and Italian. As it turns out, my wife is going to Chile in
January. She is in the process of learning Spanish,
so one of the things I'd like to do with this application
is to use it for more than one language, or even go
beyond languages to things like math facts.
Another goal is to have stacks of words and then to
test that stack as a group.
Third, I'd like to incorporate functionality so that as a
set of words is tested, I won't see words repeated if
I've translated them correctly; however, if I've gotten
the word wrong, I'd like that particular word to come up
again in the stack and keep coming up until I get it
correct (after all, repetition is important in language
learning!)
Fourth, when I finally master particular words, I'd like a
mechanism that would allow me to indicate that I've
learned this word and have no need to see it again.
Lastly, I'd like to be able to test the translations in both
directions: from Italian to English and English to
Italian.
|
| Conclusion & Next Steps |
|
|
To summarize, I've determined the major functionality
for the program, given it a name, and selected the
Access version that I'll use for development. The next
installment of this series will be to design our tables
and to begin testing them.
|
| Trap of the Month - Importing Excel Zip Codes - Beware Of Leading Zeros |
|
|
If your work involves importing addresses from Excel,
you may find that when you import zip codes from
Excel into Access you get numbers rather than text.
The reason this happens is that Access will look at
the first few lines of your imported data and make a
decision as to what that data is. If it looks like a
number and has no special formatting, Access will
assume your data are numbers.
In fact, zip codes are really not numbers, they're digits.
The leading zero for zip codes in New England is
indeed important (a zip code must have 5 digits, as
we know), so Access should treat these digits as text
rather than numbers. One easy way to fix this problem
is make a small change in Excel before you import the
data. Simply highlight the column containing ZIP
codes, then choose Format | Cells | Text. When you
import the ZIP codes into Access with the data
formatted as text, your leading zeros will be preserved.
|
|
Tip of the Month -- Give Your Forms a Caption |
|
|
|
When you create a new form, the caption that shows
up in the blue banner at the top of the form, by default,
is the form name you give it when you create the form.
If you are using a naming convention, such as starting
the form name with "frm" and using mixed case, then
it's unlikely that the form name will be something that
is user-friendly.
This is something that you may easily fix. In the design
view of the form, select the form itself (you can do this
by clicking on the gray box in the upper left-hand
corner of the form), open the properties box (View,
Properties), select the format tab, and type a revised
title for the form in the caption area.
Make it a habit to give your forms meaningful titles;
your users will appreciate it!
|
|
