Design tips!

General development support

http://msdn.microsoft.com

The Microsoft Developer Network is an invaluable resource for developers of Access or any of the other Microsoft technologies. For a modest annual fee they supply around 1.3Gbytes of reference material on CD or DVD including white papers, problem reports, code samples, workarounds, hints, service releases, links to other resources, etc

Check out the Microsoft site for other resources, such as TechNet, which may be of help.

Query design

Make your queries run faster!

If you have a multi-level query (a query containing other queries) but need records for (say) a single client to be returned, a query may start to run very slowly as the amount of data in the database increases. Even though you apply a filter criteria to the top level query to return records for the single client, Access isn't able to apply the same filter to the sub-queries. The sub-queries will process every record and pass the results up to the top level query, which then throws away 99% of the data before returning the query results. This effect is particularly bad when the sub-query is an aggregate query.

One solution is to apply a filter at each level of the query. Create a global function such as GetglClientID() which returns the value of a global long-number variable glClientID. Place this function call in the criteria of each query and sub-query of your multi-level query. Before you run the query load the global variable with the ClientID of the client you are interested in, then run the query. This simple trick can easily reduce a query's run time from 30 seconds to 1 second.

Union queries

Union queries are not supported by the graphical query design tool in Access so most people never get to try them. Which is a pity, because they are very powerful in certain situations. They let you combine data from two or more tables (or queries) into a single set of data.

SELECT [ClientID] as ID,[ClientName] as Name,[StartDate]
FROM [qryActiveClients]

UNION ALL SELECT [SupplierID] as ID,[SupplierName] as Name,[StartDate]
FROM [qryActiveSuppliers];

This example takes client data from one table, supplier data from another and returns the whole lot as a single data set. The main rule to be aware of is that each SELECT... must return the same number of fields and they must have the same names. In this case they are [ID], [Name], [StartDate].

Form and report design

Align controls accurately and quickly

Select a group of controls using the mouse with the SHIFT key held down. Then align them using Format|Align|Left...Right (etc).

To adjust the size of a control, or group of controls, hold the SHIFT key down and press the up/down/left/right arrow keys. Size changes by a single pixel for each key-press.

To adjust position hold the CTRL key down and press the up/down/left/right arrow keys. The control moves by a single pixel for each key-press.

Use CTRL-C to copy a control (or anything for that matter). Use CTRL-V (or SHIFT-Insert) to paste.

Visual Basic

The Screen object

The screen object allows you to process a control on a form without having to tell the code which control. Put the procedure below in a global module. Type "AllCaps" into the AfterUpdate event of any control on any form and the text will be converted to upper-case.

AllCaps()
Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase)
End Sub

nZ function

Converts a null value into a default value. Very useful not just in visual basic but also in queries, reports and forms. Any null value in an arithmetical function will return null as the answer. This can be a real pain and result in a blank fields on a report when what you really wanted was a zero. The nZ function fixes this problem by supplying a default value.

Function Parameters

When you call a procedure you usually supply parameters. A parameter may be something simple such a number, or it may be something far more powerful. Any object can be passed as a parameter: for example, a form may call a global procedure, supplying a "Form" type object 'pointing' to itself. The global procedure can then operate on any of the controls on that form and has access to any of the public procedures in it.

This is another example of how parameters can be used. If you create some code to operate on a set of records, but you want to keep things flexible as to how the record-set is defined, pass the query which finds the records in as a parameter. For example:

Public Sub ValidatePostcode(sSQL as String)
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
        Set rs = db.OpenRecordset(sSQL)
        ... etc

The parameter sSQL contains an SQL string. This is a text string representation of a query. You might want it to return all addresses, a filtered group of addresses or a single address... You don't have to decide yet. You make the decision when you call it.

Optional Parameters

Optional parameters are parameters which may be passed to a procedure but may be omitted. If you have a procedure which has already been called from 100 places in the application the last thing you want to do is add another parameter and have to modify and retest the whole application. If you need to supply an extra item of information to the procedure in one particular instance the solution may be to add an optional parameter and supply it only when needed. For example:

Public Sub ValidatePostcode(sSQL as String, Optional ynVerbose as boolean = False)

The optional parameter "ynVerbose" is used to trigger user messages and progress status information.

Getting around in code

Open any code module. Press CTRL-F to show the Find dialog. Select 'Current database' to search the whole application. Type in a word, or part of a word, to get to where you want to start working. (This saves you having to remember where you put a particular procedure...)

Right-click on any word in the code and select "Definition". If the word is a procedure call Access takes you to the procedure; if it's a variable it takes you to the variable declaration. To return to where you came from right-click and select "Last position".

Keep code compiled

Compile and save all code every time you edit. The compiler is a great tool for keeping your code legal. It will alert you to any name conflicts, syntax errors, and a wide range of other problems. Fix them straight away - don't let them build up!

Indent everything

Your code will be easy to follow if you indent everything you can. For example:

Set db = CurrentDb
    Set rs = db.OpenRecordset("qryClient")
        With rs
            Do until .Eof
                If !Balance < 0 then
                    If MsgBox("Exit loop?", vbQuestion + vbOKCancel, "Stop scan") = vbOK Then
                        Exit Do
                    End If
                End If
                .MoveNext
            Loop
        End With
    rs.Close
db.Close

Use 'With ... End With'

Use With ... End With wherever possible. This is a shorthand notation to tell Access that, from this point on, you are referring to a specific object. The object can be something simple like a recordset, it may be a form object, a database object or anything else. You can nest With loops.

With rs
    .AddNew
        !Lastname = [txtLastname]    'pick value off an unbound control on a form
        !Firstname = [txtFirstname]    'and save in a new record
    .Update
    .MoveLast
End With

Transactions

When you perform a transactions in visual basic all the changes you make to your data are held in memory. If you commit the transaction the changes are saved to the hard disk. If you rollback the transaction the changes are dumped. This is useful in an 'all' or 'none' situation, for example if you are transferring funds out of one account and into another.

You can use transactions in another way. If you have a long running set of query-based processing you can present a summary of the results to the user and offer them the chance to continue (commit the transaction) or cancel (rollback the transaction). This is quicker than working it all out, asking the user, then working it all out again...

ws.BeginTrans
    '
    '...do processing
    '
if MsgBox("Continue...") = vbOK Then
    'either commit the transaction
    ws.CommitTrans
else
    '...or dump the transaction
    ws.Rollback
end if

WARNING!!! The above code is just fine in a single-user database. In a multi-user database it should be used with caution! Until the transaction is either committed or rolled back every record in the transaction remains locked. If the user goes off to make a cup of tea before responding to the prompt this could be a while!

General

Shortcut keys

<Windows> key + M = minimize all windows
<Windows> key + E = open 'Explorer'
Hold <SHIFT> down when opening a database to bypass the Tools|Startup... options.

Naming conventions

Imagine a multi-level query where tables and queries are mixed up together - it can be very hard to see how the query works or where it "bottoms out". You have to keep coming out of the query, find out about each object embedded in it, go back in ... This can be a total nightmare! If you name tables "tblXXX..." and "queries "qryXXX..." things start to get much easier. If you have several sub-queries (or sub-sub-queries) embedded in a main query call them something like:

qryClient
qryClientSub1
qryClientSub2
qryClientSub2sub1
qryClientSub2sub2

Use the same idea for forms and sub-forms:

frmClient
frmClientSub1
frmClientSub2

...and reports and sub-reports:

rptClient
rptClientSub1
rptClientSub2

In visual basic code prefix variables according to their type. For example intClientType would be an integer. This can be shortened to iClientType, or changed to giClientType to show that it is an integer with global scope. Use prefixes 's...' for a string, 'yn...' for a boolean, 'l...' for a long, etc.

Don't use spaces, hyphens, underscores or other unusual characters for names. Use upper-case and lower-case to achieve the same effect. Spaces upset SQL and visual basic and force you to enclose names in square brackets.

The 'Leszynski/Reddick' convention formalizes this notation. Curiously, Microsoft don't mention naming conventions at all in the help system.

Use Access Help

Access is huge and its not easy to remember everything - just remember how to use the help system instead! There are two separate help systems. Get into them by pressing F1.

In a code module this opens the Visual Basic reference. Type a word in the Index tab, go to a subject, then use "See also" to home in what you need. Copy code from the "Example code" sections and follow hyperlinks to find more. The process is a bit random but very quick.

If you are designing a form or report select a property in the properties window and press F1. This takes you into the Microsoft Access Reference help system, which covers graphical design and event handling.

Recover corrupted database (Access 97)

Specific objects, usually forms, in an Access 97 database front-end may become corrupted. If you try and copy or delete a corrupted object, or view and compile code, a fatal error dumps you out of Access. Sometimes you cannot even tell which object is corrupt - all you know is that a 'compile all' causes a fatal error.

Keep frequent backups (hourly when developing a large database). If the problem occurs create a new database and import all objects from the corrupt database into it. When the import process hits the corrupt object a fatal error ensues. This tells you which object(s) are corrupt. Repeat the process, avoiding the corrupt objects, then import them from your last backup. If your database is secure then you will need to re-establish permissions on imported objects. You will also need to set up the Tools|Startup... options.

Microsoft never owned up to this problem. However, they have fixed it in Access 2000.

The KISS principle

"Keep it Simple and Stupid". Its flash to play around with the registry, use exotic ActiveX objects, declare Windows API's, etc. While this is fun for the developer keep in mind that the real goal is to meet the business needs. The more you can "dumb down" the design the wider will be the pool of developers who can take over in an emergency. By all means use advanced techniques, but use them when they are needed not just to be flash!