The agony of building an Application

[Sreedhar]

This post is not about any specific issue but about building the Application
itself. Please have patience to read the tale of an ametuer "programmer(?)".
Please help if it sounds meaningful.

Three months ago, when I was first exposed to Access, I was amazed to see
the ease with which one can build databases. I was hooked and started by
building my own Application. Learning VBA further enhanced the functionality
of my db.

The problem now is:
The code I wrote initially was very awkward. As I got more and more
acquainted with VBA and its possibilities (still exploring), my Application
grew in size and utility and is fairly successful in meeting the objectives I
set out at the beginning BUT it all became bits and pieces - a modification
here and an improvement there !
So I have decided to re-build a comprehensive Database with enhanced
features and started looking at Sample dbs and I am more confused than ever.
Also, re-writing the code is not as simple as I thought - sometimes, I'm
beating my head against a wall to find what went in my mind when I wrote that
in the past.

Ok, the question is:
Is there any standard documentation or blue-print that answers the following
questions and acts like a checklist ?
How should I approach when I set out to build an Access Application ?
How should I translate my objectives into what should be done with Access ?
How to make sure I don't miss anything ? (and avoid the agony of doing it
all again)
How to know what IS possible and NOT possible with Access ? (and avoid that
"errr... I should have added this to my db!" feeling)
What is the reasonable time it takes to build a feature-rich Access db?
What are the best practices to do it ?

 

[Allen Browne]

Whew. Big question.

Firstly, there will be many here who can identify with your journey.

There are 2 ways to build a database:
a) Go and do a computer engineering degree. Lean all about normalization,
interface design, specification, project management, implentation, and
coding at a generic level. Then start at the practical level, trying to
apply all you have learned, until you finally gain some experience, learn
when to apply the rules and what is impractical or inefficient. As you gain
experience based on theory, you finally arrive at the place where you are
able to build a database.

b) Start messing with Access, and discover what you can do with it. Find out
the hard way that writing lots of code to do stuff is no substitute for
having the a normalized data structure. Try each of the 6 ways Access gives
you do do something, until you figure out which is the most efficent one.
Read the code the wizards create for you, and begin to mess with them until
to start to understand what they are doing. Get frustrated enough to ask
lots of questions and read lots of stuff until you begin to discover the
better solutions. Get frustrated enough with starting over so many times and
never coming to the end of the project ("Oh, can it do that too?") that you
eventually realize it is worth the effort to do a complete written
specification of what the project should achieve before you start.

With Access, I suspect that (b) is the route most people take. Whichever
approach you take, you finally end up with the meld of theory and experience
that you need to develop applications. Of course, you never stop learning,
as you discover new approaches, see what others are doing, and come to terms
with the versions.

Answers to your specific questions in-line. It assumes you have some theory
and experience.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How should I approach when I set out to build an Access Application ?

Start by writing a specification that defined what functionality the
database needs to provide, what data needs be stored, and what reports need
to be produced.

I find this document will be around 4000 words for a simple project, or
10000 or more for an involved one. This is terse (no wasted words) and not
too technical (the client who wants the database should be able to
understand it.)

How should I translate my objectives into what should be done with Access
?

During the specification process, I end up building a data structure that
provides everything that needs to come out of the database.

How to make sure I don't miss anything ? (and avoid the agony of doing it
all again)

While writing the spec and building the tables, constantly look for hidden
"manys", e.g. client needs many addresses (so separate address table)? Keep
it as simple as possible, but make sure the structure you have created will
handle every case. Ask questions and clarify until both you and the client
are certain that this will handle every case.

How to know what IS possible and NOT possible with Access ? (and avoid
that
"errr... I should have added this to my db!" feeling)

Don't be too hard on yourself: sounds like the process you have gone through
is giving you the very experiecen you need to ensure that you are able to
pick these.

But don't skip the specification! Would you try to build an ocean-going
yacht just by nailing boards together, without plans and engineering the
shape of the hull and applying the laws? Don't try to build the database
without the spec!

What is the reasonable time it takes to build a feature-rich Access db?

Depends on what's in in of course. By the time you write the spec, verify it
handles 100% of the cases, buid the data structure (tables with fields of
the correct data type, with the right properties and descriptions, indexes,
and relationships with referential integrity), construct the interface
(forms and subforms, with applied business rules, validation, code to the
helps complete the entries, and warnings for questionable entries, and
blocking of dangerous data), create the output (reports with their queries,
+ exports by file, upload, or email, and interface them with multiple
optional criteria applicable to each report), complete and debug the code,
write the documentation, construct the test data, complete the testing,
split the database, and constuct the installation, you are talking about a
couple of weeks work for the smallest project, or many months work for the
larger ones.

What are the best practices to do it ?

Wow, that's probably beyond this question, but stay tuned, read the
websites, and get some books.

Here's a free utility that will check your data structure of issue you may
have missed:
Database Issue Checker
at:
http://allenbrowne.com/AppIssueChecker.html

It cannot tell you that you should have used a different set of tables (the
Table Analyzer on the Tools menu can suggest that), or relationships, but it
can catch some of the common mistakes.

Hope that helps.

 

[tina]

comments inline.

Ok, the question is:
Is there any standard documentation or blue-print that answers the following
questions and acts like a checklist ?

see http://home.att.net/~california.db/tips.html#aTip1. in your case, since
you stress the documentation issue, i strongly recommend the book mentioned
in the tip.

How should I approach when I set out to build an Access Application ?

see above link.

How should I translate my objectives into what should be done with Access

?

see above link.

How to make sure I don't miss anything ? (and avoid the agony of doing it
all again)

you can never really be absolutely *sure*, because processes grow and evolve
over time, and often the applications that support them must do the same.
but thoroughly analyzing the business process, and broadening the scope of
the analysis as much as seems reasonable, can help get you a bit ahead of
the game. again, see the link above.

How to know what IS possible and NOT possible with Access ? (and avoid that
"errr... I should have added this to my db!" feeling)

the line between what "is" and "is not" do-able in Access, is fuzzy. it
depends on the developer's skill level, and to some extent on his/her
ability to search the internet for the many, many resources and solutions
available. and there are expert programmers around the world, in the private
sector, who are constantly pushing the boundaries of what can be
accomplished using Access.

What is the reasonable time it takes to build a feature-rich Access db?

a lot of it depends on the process that the database must support, and what
features are needed (or wanted) to do the job. also, the time spent building
a database is inversely proportionate to the time spent planning it - the
more quality time you invest in process analysis and data modeling, so that
the "blueprint" is correct and complete, the less time you'll need to spend
on implementing that blueprint in Access. and finally, your experience and
skill set also play a big role: if you've done a particular task a hundred
times, you can do it easily without a lot of effort; but if you're learning
how do to that thing for the first time, or have only done it a few times,
then naturally it'll take longer. personally, i've built enough databases to
have several "standard" solutions to certain common needs, that i use over
and over - often with minor tweaking to fit specific requirements. i also
learn something new, often minor and occasionally major, in every single
database i build.

What are the best practices to do it ?

see the link above; suggest you read all the tips and follow the links to
other websites, and from those to yet others...

hth

 

[Marshall Barton]

Welcome to the world of software development ;-)

As with any serious activity, knowledge and experience are
the critical factors (I'm constantly surprised at how many
people don't seem to get this simple truth). You now have
enough experience to recognize the need for deeper
knowledge. Instead of gathering knowledge through more
frustrating experiences, search your local library and the
internet for an interminably long list of discourses on the
subject. Somewhere out there you should be able to find
some of it that is worth more than one in depth reading.

I suggest that you start with something about how to create
a high level specification of the application, including
areas that may or may not ever see the light of day. The
big picture is here is extremely important. Like the cover
on a jigsaw puzzle's box, you should constantly refer back
to it for answers to questions about "how does this part fit
into the whole?"

Another fundamental area is something called Stepwise
Refinement. This is a way to breakdown the specifications
into tables and their relationships along with writing the
detail requirements for functionality, then break that down
further to a high level modular design, then detailed
component design, to skeletal implementation of each area,
and on to component implementation.

Don't forget that the refinement concepts apply to your test
plan as well as the application's documentation, both of
which should proceed in parallel with the rest of the
project.

Don't despair. It is a huge subject, but the fact that you
recognized the symptoms and the need get a grip on things
puts you way out in front of crowd. Now that you have
actual experience with the consequences of a haphazard
approach, articles, books and disussions will be much more
meaningful. And, don't forget that no matter how far down
this path you go, there is no such thing as perfection so
carry on, even when you're not sure about the "right" way to
do something.

 

[Larry Linson]

What is the reasonable time it takes
to build a feature-rich Access db?

It all depends, as others have said, on the _requirements_. That is, what
the database application must do, and the data with which it can work to
accomplish those ends.

In the mid-to-late 1990s, I worked for around five years, off and on, often
with others working on a development team, just expanding and enhancing an
Access client application that used an Informix server database as its
datastore. The basics of this application had been created by others, and
the client had purchased a license to convert it to the client-server model
and to expand its functionality for their own use. Near the end of this
period, we performed Y2K analysis and remediation. One of the things we did
was to "clean up" and eliminate those objects that we could verify were
unused; after the clean-up, the application still had approximately 1,000
objects (tables, queries, forms, reports, macros, and modules -- and almost
every form also had code in its module). This application was for corporate
real estate tracking for a Fortune 100 company. They used it to coordinate
between the company and the real estate companies to whom they had
outsourced major portions of the actual purchase, sales, leasing and
subleasing of thousands of properties owned, leased, or rented by the
company.

Less-ambitious projects can be done in less time... it all depends on what
you consider "feature rich" to mean. I've done Access database projects in a
few days that met the customer's needs and requirements -- for that customer
they had all the features needed. I've done others that required weeks or
months, and sometimes (like that big one) a development team.

What are the best practices to do it?

"Best practices," like beauty, is in the "eye of the beholder." They are
also very specific to the development model you are using -- which can range
from a "classic" model of creating the requirements, doing the design,
implementing the design, testing the implementation, and deploying the large
project to "current" methods involving prototyping and "agile development"
where you do not attempt to define the entire project, but very quickly
determine the users' needs for, design, implement, show the users, get
feedback, and modify tiny segments of a large project, a few days at a time,
or a week, and then release that segment to the users for production while
you proceed on the next "piece."

In a previous incarnation as a "mainframer," I was involved in producing a
"Procedures and Guidelines" manual for a contract services organization. The
very first release filled two 3" ring binders, printed on both sides of each
page. And, there was constant work keeping it up-to-date, because as soon as
you collected the information from successful projects using a particular
technique, the techniques would evolve and you'd have to do the same for the
new development methodologies. It's certainly not a topic that can be
covered in a newsgroup answer.

Larry Linson
Microsoft Access MVP

 

[Sreedhar]

Initially, I was very apprehensive about posting this thread and finally did
post, out of sheer frustration and I needed one outlet. Frankly, I didn't
expect any replies.

But, true to the spirit of MVPs, I got words of wisdom, each word worth its
weight in gold, and I can't thank you enough.

It's really soothing to hear from all of you, it really helps to know the
issues involved from experienced professionals like you.

I'm thankful forever to the MVPs and their spirit of giving never-ending
support to the newbies.

 

[RD]

Hi Sreedhar,

You've received some excellent advice from better heads than mine but I thought
I'd jump in anyway.

I started with Acc95 and a book. Almost all the books will cover the basics of
good database design and some of them also talk about good application design.

Since then my learning has all been the "old fashioned" way. I went out and
made mistakes. Don't worry about "awkward code. Finesse and elegance will come
with time and practice. I'm always looking for a better way to do something
I've done a hundred times.

You mention beating your head against a wall looking at old code. One thing I
learned early on is to comment the heck out of my code. I recently met up with
an old tech lead of mine from a project I left back in '01. He had kept most of
the code from that project and allowed me to download it onto my data stick.
Even with some of the far out stuff I was doing back then, reading my code was
made much more easy because of the copious commenting I did.

Some suggested books:
Building Access Applications - John L. Viescas - Microsoft Press
Access 2003 Bible - Prague, Irwin and Reardon - Wiley Press
Access 2003 VBA - Cardoza, Hennig, Seach and Stein - Wrox
Fixing Access Annoyances - Mitchell and Callahan - O'Reilly

Have fun!
RD