Good question, Dan.
Big one though.
The first (and most crucial) concept to grasp is the idea of how to break
the data into related tables, creating one-to-many relations. Some of these
are obvious, e.g. one client has many orders over time. Some are obvious
once you get them, e.g. one order has many line items. And you need to use a
junction table to break the many-to-many down into a pair of one-to-many
relations. Quite honestly, this process (normalization) is the most
important design choice in getting a database off the ground. Starting
point:
http://allenbrowne.com/casu-06.html
More articles:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
As you start to develop a feel for that, you are already designing queries.
Make sure you understand outer joins and nulls. Starting point:
http://allenbrowne.com/casu-02.html
You can actually learn much about SQL just by switching your queries to SQL
view.
More info:
http://www.sqlcourse.com/intro.html
Next, you will be learning about forms, and the various controls you can put
on them (text boxes, combos, subforms, ...) The controls and the form both
have events that fire when something happens. You need to know which are the
crucial events to become familiar with. Form_BeforeUpdate is the most
important (for validating the record.) Starting point:
http://allenbrowne.com/xbase-02.html
Reports are fairly easy, because you already know about queries and about
controls (like on forms.)
Macros are sometimes handy to get you started, but they are quite limited.
You find yourself wanting to do things where you need VBA code. If you have
never programmed before (like most people starting out in Access), there are
core concepts to learn (loops, branches, variables, procedures, events, data
types, ...) It can feel like tackling a foreign language, and you soon learn
how dumb computers are, i.e. they do whatever stupid thing you told them to
do, not what you intended.
It is quite a learning curve.
Functions such as the Space() in your example are part of the VBA code, but
you can use them in contexts outside of code. In your example, you used the
function in the Control Source of a text box. You can also use them in query
expressions. There are a few key functions to become aware of, such as
DLookup():
http://allenbrowne.com/casu-07.html
The answer to your question about using SQL in VBA is a resounding yes. You
use SQL statements (or sometimes parts of the SQL statement such as the
WHERE clause) incessantly in your code. Example:
http://allenbrowne.com/ser-62.html
Access courses vary in quality and depth. Some just introduce people to what
tables are, what queries are etc. While that's okay for an introduction, it
won't take you far enough to design a database structure, let alone build an
application. So you may benefit from a course, and you will need some books.
There are some new ones about to come out for Access 2007. Zac Woodall has
prepared a list here:
http://blogs.msdn.com/clintcovington/archive/2007/02/15/new-access-2007-books.aspx
Based on the previous edition, I expect the "Inside Out" one (John Viescas
and Jeff Conrad) will be a good balance between covering the basics, yet
really taking you places without wasting your time.
Access is an amazing product, combining many products into one:
- a database engine (tables and queries)
- a user interface (forms, DAPs) and report generator (reports)
- scripting (macros) and a full programming language (VBA.)
Little wonder that mastering Access involves heaps of learning: reading,
applying, experimenting, fiddling, finding out what works and what doesn't,
and which of the 6 ways to do anything will be the most efficient.
So, be patient, and enjoy the journey. If you love this kind of thing, and
see yourself working in the field, it's worth the effort.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Dan Goble said:
You can give a man a fish and he can eat for a day but if you teach him to
fish he will eat forever.
At the chance of showing my ignorance I must ask some questions and would
like anyones input to put me on the right track.
I want to learn more than just the simple stuff. I want to help those
like me like you guys have been helping me. Here are the questions
I intend to learn to write the stuff you guys have been providing. But I
dont completely understand what to learn. SQL I know is behind the
queries and there is a code area and I think that is Visual basic. Macros
are built in software that writes the VB for you.
Correct me when Im wrong
Is there code in SQL that work in VB?
=Space(100) & [SomeField] was the answer to a recent question. It was
placed in the properties of a control. Is this VB?
I want to enroll in a course at my local college or online or something.
What do you suggest and what do I ask for?
Please point me in the right direction
Thanks
Dan
