I need some resources for using ms-access effectively.

Question

Update: Management has seen the light, we're going with php and mysql. Thanks for your help.

Subject: Access 2007

If it were my choice, i'd write up a web-based app w/ perl and mysql... But the higher-ups aren't good with computers, and know more about microsoft excel than they do the command line. They are great at organizing and problem solving, just not programming and DB stuff.

With that said, I've basically inherited a project that is half-way done. I'm just a helpdesk technition with some programming experience, and at the moment I'm the only one with any knowlege about programming who'd be able to work on it.

By having everything inside of a simple access file, they can backup it and version it etc... It doesn't involve running any servers, or having any special programs (all our machines have access, on a site licence). It is for an inventory system, where we scan barcodes and enter in information such as location, model number, owner, etc. The idea is we can port this little thing around as we are adding things to it. Leaps and bounds ahead of a simple excel file we had before.

So my problem is, I have very little experience with access, and i'm required to make all sorts of fancy forms and reports for it. I've done a very very little of VB6, but don't know my way around worth beans.

The person before me set up a decent little database, I'm charged with making a usable front end.

Please help me with resources. Stuff having to do with best practices, gui guidelines, etc, would be a plus.

Answer 1

I'm pretty sure 2007 version comes with a lot of handy wizards which will do most of the work for you. Try and get a handle on those before subjecting yourself to a bunch of unneeded manual labor.

Edit: Learn how to create queries. You can then pipe your queries into different reports and it should allow you to pretty much do anything. If you need a lot more customization... well... you'll be learning some VB and getting dirty with Access. That will probably take more than what we can help you with here.

Answer 2

I did development on a large Access project for several years. In some ways it is easier to develop business applications in Access than it is in VB6. You have built-in data binding, visual query creation, and excellent report capabilities to name a few. However, there are some things you should know about Access before you start:

1. The database engine behind Access does not scale well. Unless this is a small application with a few (ideally less than 5) concurrent users, you will wind up porting the data to SQL Server (which is less difficult than it sounds).

2. The data tables MUST be kept in a separate MDB file from the actual application MDB. This is done by using "linked tables" to the data MDB.

If you are just starting out in Access you really should get a good book. The definitive work on building an Access application used to be the Access 2002 Desktop Developer's Handbook. Remarkably, the core parts of Access have not changed that much (apart from the ribbon) since then. The book has a companion called the Access 2002 Enterprise Developer's Handbook, required reading for anyone building an application with more than 2 users.

If you have never worked with Access before, and are not familiar with Windows-style programming, you're not ready for these books yet. There are plenty of good (and current) books out there that teach the basics. I mention these two books because, if you ever have a question about developing in Access, the answer is probably in one of these two books.

So if your boss is really going to force you to do this, make him spring for these two books.

Finally, the indispensable web resource on Access programming:

http://mvps.org/access/

Answer 3

Probably the best resource I ever found was the Access 97 Developers Handbook (a real gem).

These are also very good:

• The Access Web

• Tony Toews Access Page

• SSW Rules to Better Access Applications

• SSW Rules to Better Access UI

• SSW Rules to Better Access Databases

Answer 4

Here's a site I like ... it's got a mapping between how to do things in SQL Server versus how they are done in Access. So, if you know how to do it in TSQL, but can't figure it out in Access, this site is very helpful.

Answer 5

You've got some good answers. To follow up on Robert Harvey's comment with respect to splitting the database into a front end (FE) with linked tables to the back end (BE).

The Front End (FE) contains the links to tables, queries, forms, reports, macros (if any) and VBA code. The Back End (BE) contains tables, indexes, relationships and a startup form that tells the user to not think about making any changes and to exit immediately

You really want to put the FE on each machine or place in a user specific directory on the server. This will help avoid some weird error messages when users are changing the same forms record source, filters and such as well as corruptions. It is also much easier to implement a new version of the database with changed queries, forms, reports and VBA code.

I specifically created the Auto FE Updater utility so that I could make changes to the FE MDE as often as I wanted and be quite confident that the next time someone went to run the app that it would pull in the latest version. For more info on the errors or the utility visit http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater supports creating a directory named after the user on a server. Given a choice put the FE on the Citrix server to reduce network traffic and to avoid having to load objects over the network which can be somewhat sluggish.

Answer 6

This site has some good video training that brought me up to speed on 2007 version.

http://www.keystonelearning.com/

Answer 7

I found that a couple of hours with a good introductory book on database design ( from the Local library) really helped me to understand a lot of the help that is already out there. and the concepts in the help documents. I'm still no db guru, but at least I have a better understanding of what it means to design a database, and its importance to IT. (which I used to think meant HardWare management, and software installation).