Fog Creek Software
Discussion Board




Good resources for Access XP development?

A friend of mine has an Access XP-based application that his company is using, and he wants me to take a look at making some improvements to it. Trouble is, while I've done a great deal of work with databases (SQL server, Oracle, MySQL, Postgresql, etc. etc.), I've never even touched Access.

This looks like a good oppertunity to expand my skillset, if only I had a really good reference for working with Access. Can anyone recommend to me books, websites, etc. that deal with Access XP-based application development? Any help would be appreciated.

Wayne Earl
Friday, February 07, 2003

Access XP (also called Access 2002) is almost the same as Access 2000 , which was itself similar to Access 97.    Access 2000 and Access 2002 use VBA6 as their programming language, which is almost identical to straight VB.  A good introduction to programming Access using VBA is:

Beginning Access 2000 VBA by Smith & Sussman, published by Wrox. 

The "bible" of Access books is the 2 volume set by Getz and Litwin, the Access 2002 Developer's Handbook, v. 1 and v. 2.

My favorite site for help with Access is www.tek-tips.com, which has five or six forums devoted to Access that get huge amounts of traffic.  Microsoft Newsgroups could also be quite helpful.

Herbert Sitz
Friday, February 07, 2003

What you really want is information about Microsoft Jet, which is the database engine used in Microsoft Access and Excel.

A book I liked was "Microsoft Jet Database Engine Programmer's Guide" by Dan Haught and Jim Ferguson. Published in 1997, it's still a very good book. (http://www.amazon.com/exec/obidos/ASIN/1572313420/qid%3D1044653906/sr%3D11-1/ref%3Dsr%5F11%5F1/002-8354863-5725669).

WNC
Friday, February 07, 2003

Wayne,

http://www.customguide.com/access2002.htm

I think the Eval version should suit you just fine,..

best,

Prakash S
Friday, February 07, 2003

WNC may be right that what you really want is information about Jet.  But perhaps not.

Access really comprises two big parts: (1) the Jet database engine and (2) the form/report/ui/VBA framework that you can use to build applications that access the Jet database. 

Jet and Jet SQL have a few peculiarities, but really isn't too different from most other SQL dialects.  The main thing to remember with Jet is that it's a fileserver-based system rather than a true database server. 

You could build a non-Access-based program to access data in your friend's Access/Jet database in much the same way as you write programs to access any other back end.

If you're modifying a program that is already written using the Access 2002 user-interface, though, that will mean working within the framework of Access forms and reports, and using VBA as the programming language.  It would take a bit more to come up to speed on these than it would to just learn how to use a Jet database as a back-end for a non-Access user interface.

Herbert Sitz
Friday, February 07, 2003

I second the recommendation for the Getz/Litwin/Gunderloy books; they're among the few three-inch books on the shelves that are actually full of enough good information to warrant their size.

But those books assume you're reasonably familiar with Access; if you've done so little Access that you really need to start from nothing, try this book, which takes you through designing tables, queries, forms, reports, etc. in Access:

http://www.course.com/catalog/product.cfm?isbn=0-619-02089-X

You should also check out the newsgroup comp.databases.ms-access, where a lot of professional Access developers post to ask and answer questions.

Kyralessa
Friday, February 07, 2003

You have got some first rate advice here already.

Also, use the newsgroups.

Comp.databases.ms-access

And the Microsoft ones (which are their busiest ones they have!!).

Microsoft.public.access.forms
Microsoft.public.access.formscoding
Microsoft.public.access.reports
Microsoft.public.access.multiuser

Bookmark: www.mvps.org/access

Read the access 10 commands:
http://www.mvps.org/access/tencommandments.htm

Get some books…for sure!!!
There used to be good book included with every office cd.

A copy of the cd version (access97) can be found at:
http://www.microsoft.com/accessdev/articles/bapp97/toc.htm

The above is very good start, and will apply mostly to xp also.


A few more things:

I never have considered ms-access a database. It really is a GUI database client system.

Of course, in fact the database engine is most often JET, but then again you often will see the product used as a client to sql-server or whatever.

So, in effect while sql-server has no tools for the front end, ms-access really does not have any tools for the “back end”, or the database!

However, when you create a ADP project, you are working directly with sql-server. The table designs and manager actually is a mini replacement for the sql enterprise manager when you do this. The jury is still debating as to if ADP projects are worth the trouble right now.

Regardless,  your sql skills, and concepts of database stuff will give you a big jump start. Main thing you will miss is that you don’t have server side sql procedures. (you will miss this…very much!!).  This just means your designs will not rely on server side stored procedures. Often the solution in place of stored procedures is to use several queries based on EACH other. The other cool thing is that you can define a function in VB and use that as an expression in the column in the sql. (again, this is often a substitute for t-sql code). (ms-access people miss the fact of VB functions when going to t-sql!).

The programming language as mentioned here is VBA. It really is the same as VB for all intensive purposes.

So, you have a tall order here. You need strong VB programming skills and you have to learn a new IDE.

The other major difference from VB is the forms. The forms model in access is considerably more complex then the VB forms. There is about 40% more methods and events for a access form. The large portion of these extra features are due to the concept that forms are data bound. Learning the forms is the secret to unlocking ms-access. You have to learn how forms work in access (this is #1 on your list).  This why it is a true RAD database tool.

Forms in access are most interesting since they can be imbedded in each other (sub forms). Forms also can be flipped into what is called continues mode to create a data grid.

A continues form thus results in all controls (including buttons) to repeat. That means that a simple set of controls on a form will repeat over and over to become a grid.

The real beauty of a continues for as a grid means that once you learn how to use a basic form, then all that stuff you learned can now be applied to a continuos form Hence, that same rich forms model that has all kinds of events and methods with VB code can now be used for a complex grid. You don’t have to go out and learn another complex grid control. (a large amount of learning is saved here). Further, since it is a form, the data is editable (if you wish).  The values for a cell can also be based on a vb functon (really nice!).  Here is my concept on how a search form in ms-access should work. Note also how the controls “repeat” in the “sub-form” screen shots.

http://www.attcanada.net/~kallal.msn/Search/index.html

Sub forms are another key concept that accomplishes many things.

My thoughts on sub-forms:

http://www.attcanada.net/%7ekallal.msn/Articles/fog0000000005.html

I cannot stress how you need to get a book. 1 hour working with a book is like 20 hours of playing around.

Also, some real nice tips on ms-access development are contained in a article of mine when I converted a pick application to access. It is  good read for any project you start!

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

Last but not least:
    Ms-access is not viewed as a serious tool by a large portion of the developer community. Be prepared to get some egg on your face when you mention ms-access. Just say your are writing some sql and VB code. Since that is what ms-access works with anyway.

Ms-access has it’s place, and is a great tool. When access is used right, and for the right reasons, then it can turn a given number of dollars into more useful code then just about any tool available.

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

Albert D. Kallal
Saturday, February 08, 2003

Just to illustrate what CAN be done with Microsoft Access, you can visit our Web site at http://www.bid2win.com.

Our product sells for $6,000 a seat, and is built 100% in Microsoft Access 97 with all code in VBA.  It's the result of about 18 man years of work.

Here's a screen shot of the "heart and soul" of the system: http://www.bid2win.com/product/images/lgEstimate-Detail.gif.

Dave
Saturday, February 08, 2003

Oops.  Those periods are screwing up the links:

http://www.bid2win.com

http://www.bid2win.com/product/images/lgEstimate-Detail.gif

Dave
Saturday, February 08, 2003

Dave --

Bid2Win does look like an impressive product.  I'm not surprised that it could be done in Access; Access is one of my main tools and I know it's far more capable than it's usually given credit for.  But it is surprising to actually find someone marketing an Access shrinkwrap product like Bid2Win, and being successful to boot.  Good job.

I'm curious how much data and how many users you've found a straight Access solution (front end Access97, back end Jet) with Bid2Win to work well with.  At what point do you develop issues with stability, database corruption, etc.?  And is Bid2Win offered with a SQL Server (or some other database server) back end?

Herbert Sitz
Monday, February 10, 2003

We typically recommend a Terminal Services installation for multi-user access, to keep the speed snappy.  The performance is OK with a network connection to the database, but in environments with more than 3 users, it slows down a bit.

Our clients typically have a small user base--5 to 15 users is the typical installation, with our larger installations being around 20 users.  Our larger clients have many offices with the same small user base, which makes this solution work.

We're in the process of porting the app to C# with SQL Server on the back end. We evaluated refactoring the current app (Joel: "never re-write from scratch") but we deemed the complexity of ripping out the innards in Access coupled with the difficulting in finding top-notch VBA developers made it worth it to move over to a more "shrink-wrap" appropriate development environment.

People love the Access app and choose it every day, 9 times out of 10, over our competitors. They're going to be VERY pleasantly surprised when we move them to SQL Server.

Dave
Monday, February 10, 2003

"They're going to be VERY pleasantly surprised when we move them to SQL Server."

...by which I hope you mean MSDE?  Because I, for one, wouldn't be very pleasantly surprised to have to buy a license for SQL Server.  ;>

Sam Gray
Monday, February 10, 2003

Conveniently, they're virtually the same binaries, except for that performance governor they have in there, so yes, MSDE will be supported out of the box.

Dave
Wednesday, February 19, 2003

*  Recent Topics

*  Fog Creek Home