Fog Creek Software
Discussion Board




MS Access

Can anyone recommend a *good* book on Access? Something that explains how to use the VBA part of it rather than all the Wizards.

Most books I see don't even tell you how to execute an SQL query from code.

Any tips would be highly appreciated - need to use Access 2000/XP for an advanced college course.

Thanks

Access Newbie
Thursday, August 15, 2002

Or am I missing the point if I want to do queries from code?

Access Newbie
Thursday, August 15, 2002

I have an old Book, that was MS Access 97 unleashed.  There are a gazillion similar books out there.  Do a quick search on Amazon and check the customer reviews.

The main benfit to Access is the WYSIWYG development and the Query By Example Stuff.  However, you can execute SQL.  Create your table, and then go to the query designer page.  On my old access (I don't know if it is still there) there are three icons corresponding to different views of the query.  The one the says SQL will allow you to do free form SQL.  My advice is to use the QBE tool to get the query started, and then hack the genereated SQL

Adam
Thursday, August 15, 2002

Ipersonally love oreilly, and found their COokbook series to be qwuite good, so I'd check this out:  http://www.amazon.com/exec/obidos/ASIN/0596000847/qid=1029443815/sr=1-5/ref=sr_1_5/102-7970261-9169714

Adam
Thursday, August 15, 2002

You aren't missing the point if you want to assemble ad hoc queries in code, and some books out there are much better than others.

Access Cookbook is good, but (like the name implies) it's really just a smorgasbord of different items, not much focus to things even though there are lots of interesting tidbits.

Best book for learning programming in Access that I've seen  (and used, a lot) is Beginnning Access 2000 VBA, a Wrox Press book by Smith and Sussman.  Has some great sections on how and why to assemble queries in code.  Lots of other good stuff.  Gives you a more structured approach to learning than others.  Has possibly best introduction to Access event driven programming that I've seen.

After the introduction from that book, I think the best book is actually the 2 volume Access Developer's Handbook v. 1 and v.2.  Widely considered the bible of Access programming, but not the best to learn with. 

Herbert Sitz
Thursday, August 15, 2002

i used to be an Access programmer by trade, and i the 3 books mentioned by Herbert were incredible.  you're right, most of the books on Access are fluff - they just tell you how to follow the wizards.  But these books were indispensable to making Access to some pretty amazing stuff.

nathan
Thursday, August 15, 2002

i just realized my last post was a "what he said" post.  <shrug>  sorry about that.

nathan
Thursday, August 15, 2002

Hum,  would spend as much time learning about data design and normalizing as I would learning VB (VB is the programming language that ms-access uses). Note that the event model, and form model in ms-access is quite a bit more complex then is in VB, and thus forms in ms-access have a steeper learning curve then does VB.

The next thing I would learn is to use SQL. My first query language was the amazing Recall/English from Microdata (that technology later became Pick, and then was licensed to Prime Information, and IBM’s UniVerse database  systems). Knowing a query language made my transition to sql quite easy.

Sql is probably the only thing I have used for more than 10 years in my IT career. Hence, sql is my 2nd query language that I have learned, but I use it on just about every platform and system I touch.

Even those 3 dimensional pick data base systems now have sql built in! (so does IBM’s MV database products).

In other words, sql is a standard, and virtually any database system you work on these days will allow you to use sql. I used sql in FoxPro 10 years ago, and I still use sql today. I am running MySql on this little notebook as I type this! It is a data standard, and learning it is one of your best investments in time. It will also force you start thinking in a relational sense. Sql is going to be around for a long time. You might learn VB, C, C.net or what ever way cool fad comes along, but you will still be using sql 10 years from now. Not much else in the computer world stays so the same as sql.

What to change all abbreviated Cites in a ms-access database from “NY” to “New York?

You can write code in ms-access (VB) that brings up a record set, and changes all occurrences of the “NY” city. You also can fire up the query builder, and build a update query.  You can also use “in-line” sql in code.

When you are real comfortable with sql, you will not even bother with the above approaches. You simply whack ctrl-g (bring up the debugger window), and then type:

Currentdb.Execute “update tblNames set city = ‘New York’ Where city = ‘NY’  ”


Boom...done...no code, no procedures...no fuss. Learn sql, you will use it 20 years from now....


Also remember that a good data design is 100 times better then great code, and a bad data design. With a good data design, a application practically writes it self. I also have some great ms-access tips in article of mine where I converted a application from a non-sql system to a sql based system in ms-access.

Check out:
http://www.attcanada.net/%7ekallal.msn/Articles/fog0000000003.html


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Thursday, August 15, 2002

Albert -- Great advice.  I agree; I wish I'd said that myself.

To do well programming Access -- or any database -- the most important thing is to start with a sound database design. 

And the books listed above really don't give enough information on how to design your table structures.  So you should study up on that. 

Microsoft has a pretty good 20 page knowledgebase article on database design:  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q234208

There's actually quite a bit of good stuff on database normalization in the "SQL for Dummies" book.  (I've found that a lot of the 'for Dummies' books really are quite good.) 

You may also want to try something like "Database Design for Mere Mortals", which I've heard good things about but not read myself.

For a really excellent treatise on database design I'd recommend "Information Modeling and Relational Databases", by Terry Halpin.  This is actually a book that teaches you ORM (Object Relational Modeling), but since ORM is all about modeling databases, and since ORM seems to be a pretty damn good modeling language (better than ER), this is a great book.  Really good book, lays things out in a first-rate academic style exposition, but you need an introductory text first.

As Albert says on his website, "With a good data design, the application practically writes it self."  It won't write itself, of course, but having the data normalized properly will make the programming much, much easier. 

If your database design isn't normalized, you'll end up having to solve lots of problems in code that could have been handled simply (probably with a single simple query) in a properly designed database.  Improper data design forces you to solve things in code.  You'd be surprised at how little code you have to write in a well-designed database.

As a side note, Albert, I ran across your article on converting PICK databases to Access a year or so ago, poked through it and thought it was interesting, but had no use for it and didn't save the URL.  Now I'm just starting work on a fairly large project for a client who's replacing an old PICK system with an Access/SQL Server solution.  I spent some time trying to find your web site again, but was unable to until I read your post above and went to your site.  I don't know if we're going to try to brng the data from the PICK system over or not; there isn't really any need to.  But I'll review your article again and at least be informed if I have to talk to the PICK programmer about how we want to get the data out.

Herbert Sitz
Friday, August 16, 2002

http://www.customguide.com

it is good as long as u don't use it for training.

CHeers!

Prakash S
Friday, August 16, 2002

Herb,

A google search for "Albert kallal pick convert access"  yields :

http://www.attcanada.net/~kallal.msn/Articles/fog0000000003.html

Bella (click for article)
Friday, August 16, 2002

Thanks everyone - especially Albert! Very sound advice indeed. ;-)

Access Newbie
Friday, August 16, 2002

To Bella:

Gee, I never did submit that URL to the search engines. Clearly one of their "spiders" got a hold of some link, or url, and then my web space was meat for the hungry web spiders. I am not sure what date, or time that occurred at..

That web space was supposed to be temporality space. I stared out testing CityDesk on that temp space. I also needed a temp spot for some of my articles. It was not my intention that the web space gets indexed. (I wanted to wait a while before I submitted the URL's to search engines, as I was supposed to change that to another of my temps sites!). Not a big deal, but I am sure curious as to how those spiders got to my web site.

I am guessing that some of the web spiders must look at newsgroups, as other then here at Joel’s site, no links are to that content.

Those web crawler engines must be very busy servers.

I did a reverse lookup using google (to find which sites link to my site, and I game up with ONE link. That is a link that lists a bunch of city desk sites)

http://tk-jk.net/city/Articles/OtherCityDeskSites.html

I guess from that site, the spiders indexed the rest, and I can find NO sites that link to my pick/ms-access article!

I did not realize that my page could be found by google...thanks...

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Friday, August 16, 2002

*  Recent Topics

*  Fog Creek Home