 |
 |
 |
 |
 |
Tips for Microsoft Access
The following notes are intended to get you there quicker. It is entirely your decision as to where you apply these and where you feel you should not. Although the choice is yours, many of the "ways of working" have been built into applications that have been running successfully for 10 years and more.
Starting a new Database
Naming conventions
Unless you intend to stick with the wizards, make all object names short (i.e. Tables, Queries, Forms, Reports, Macros and Modules). It is always a balance between understanding what these mean in a few years time set against typing effort.
The convention of preceding object names with tbl…., frm…. qry…. etc avoids the possibility of confusing objects or does it? Not really, it merely makes names (and typing) more involved. Yes, it avoids the mistake of trying to name a query with the same name as a table, but the system warns of the attempted duplication anyway.
Similar comment applies to Fields names and Controls on forms, keep the names short. You will quickly realise the benefit when your application builds and you start to add code to forms / modules.
Keeping track of the objects created
In the database Window, right click on the table/form/query/report etc. and use the properties description to note what the item is. You may need to right click - view - details, before doing so.
Precede the properties notes, described above with a number and use the sort ascending/descending to group items.
Queries v Macros
There are good and bad ways of designing any software application and Access is no exception. The same task can be accomplished in Access in many different ways. What is best, is down to what fits your situation.
Use of Queries and Macros will get you by, but eventually you will realise that not only can you find this process tedious, that the whole world and his dog can see how you have gone about the job and fiddle with your efforts.
Think of the end point. Do you want to protect your application from the "fiddlers" of this world or is this a purely personal thing.
Use what Microsoft have built to show how to go about the job.
Don't under-estimate the power of the Northwind application. Really scrutinise this to find how that application was put together and how it can help you. Like your own application should be, Northwind appears simple to use and the user is unaware of the complexity that goes to making this work well.
As for the other applications on offer when starting a new database in Access (the mdz's), don't be fooled, a lot of work has to be done to make these fit your own situation. Working from another designers starting point is not always a good idea.
Security
Protect your entry into the database with a database password. This isn't water tight.
Protect entry to your application with user passwords, better but not completely foolproof.
Create you own mdw (locking file) and make sure that the mdw is used by setting up a full command line in the shortcut. Create your own username and password and be sure to give yourself all administrator rights.
The command line in the shortcut will read something like..
"c:\program files\microsoft office\office11\msaccess.exe" c:\myapp\billing.mde /user jim /pwd jimpassword / wrkgrp c:\myapp\jim.mdw.
Check that you can do all you need to do by using this name/password. When satisfied, remove rights from "Administrator". Use this approach together with the following. Be sure to keep the MDW.
Convert your application to an MDE instead of leaving it as an MDB. This will allow the "fiddler" to get into the application but disable his ability to change code.
Use the "runtime version". This creates an EXE file and allows you to set as much protection as is reasonably practicable.
Writing Code
All that complicated code? You need to ask yourself if all that error checking really necessary. e.g. when you allow the wizard to create a command button and you then examine the "on click" code, you notice a lot of lines that are intended to take care of failure.
On Error GoTo Err_Command10_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "MyForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command10_Click:
Exit Sub
Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
Do we really need to plan for failure to this extent? How about
DoCmd.OpenForm "MyForm"
Yes, the purists will say bad practice but realistically, after testing and application in depth to resolve the consequences of unforeseen actions by a user, all that error checking eventually becomes redundant.
If your application runs the same using the short version as opposed to the long which will you choose?
Slow speed of opening objects.
(The recommendations here are taken from internet sources other than Microsoft)
The change from Microsoft Access 97 to more recent versions slowed the system down. Like many other software applications, there seems to be a reliance on the user upgrading his/her kit to allow for the loss in performance. The most noticeable problem is in accessing data held on a server from a client application. It is difficult to understand how this issue cannot be faced more directly when it is possibly one of the main reasons why many networked users give up on anything written in Access 2000/2003.
To increase the speed of accessing tables situated on a file server, from a client application on a local machine, set up a single record table in the data and open this from your own splash screen, in the client. The performance improvement for Access versions beyond 97 has to be seen to be believed.