I wasn't impressed with "Access for Dummies". It contained code examples
with Dim statements such as:
Dim strA, strB, strC as String
which is incorrect. In the above example, strC is type String but strA and
strB will be of type Variant. Should be coded:
Dim strA as String, strB As String, strC as String
The author also says that Access only allows 5 or 6 simultaneous users.
Wrong again. Don't know the exact limits, depends on many factors, but 5 or
6 is way too low.
Also, I recommend you stay away from "Visual" textbooks. All those pictures
just seem (IMO) to detract from the substance - like too much ice in your
cup of soda and you end up with a cup of ice more than a cup of soda.
Of course, no textbook is perfect. Some areas of a text will be well written
while other areas in the same text might not be so well written. You might
therefore want to look over several texts with the hope that each fills the
other's gaps.
As for what advantages Access provides over Excel, I think I can help you
here. I started with a company as an Excel expert, using Excel spreadsheets
to maintain lists of data. Ok, Excel can maintain a basic list of data, but
it is not so easy to use a form for data entry with Excel. True, Excel does
provide an Auto Form feature - but if you use that (non-customizable) form
you can't use Excel's AutoFill feature, which is one of Excel's main selling
points.
Worse, you can't force the user to enter good data using Excel. You also
can't force a user to enter required data. Well, actually you probably can,
but you would have to write macro routines for such purposes. Very awkward.
With Access you can quickly generate a perfectly customizable data entry
form and put masks on fields to force the user to enter good data and define
fields as required - meaning the user can't just skip over these fields and
leave them blank. There are limits here, of course, but you can accomplish a
great deal in this regard with Access.
Also, Access queries are much more powerful than any corresponding features
Excel provides. For example, Excel has a "Subtotals" feature which is nice,
but the subtotaled data is not exportable and can't be used as input to
another program or macro. Access provides the same capability as a simple
Totals query which is exportable and can be used as input to another
program.
What else? Oh, the biggie. Excel restricts workbooks to one user at a time.
So if you have (or might ever want to have) several users entering data into
a list simultaneously, forget it. There are workarounds in Excel which seem
simple - use separate workbooks with the same structure and then combine
them, for instance. Sounds easy, right? Not so! You will see.
Yes, I know Excel says that it allows shared workbooks, but these are full
of restrictions. I couldn't make this feature work. Maybe you can, but I
don't recommend going there.
Maybe even bigger than the previous biggie: Excel tends to encourage
flatfiles that look pretty but are limited in function and scope. Lots of
horizontal columns with pretty colors, etc. So beautiful you want to cry
when you see them. Usually garbage, useless for any other purpose. By that I
mean you generally can't use the data from a flat Excel file as input to
another program. Difficult to explain why this is so, unfortunately.
The main advantage to Excel is you can get started much more quickly than
with Access. A pseudo-advantage is it is full of tricks and "power
techniques" that make the user feel intellectually stimulated but in fact
simply encourage poor database development.
The big drawback to Access: the learning curve is steep and you should have
some prior knowledge as to how to properly organize a database. If you don't
have such knowledge you can get into trouble.
Bottom line: Excel is for amateurs. If you are an amateur, you should
probably stay with Excel while cautiously "feeling out" Access.
