Access Best Practices

[jrmbach]

I have been using MS Access 2000 for over ten years. I understand most of the basics, but I’m still not secure with the “best practices” for building an Access application. For example, I have typically created a Form or Report from a Table. Over the years I have learned more about Queries. I now create a Query from the Table and use the Query to select certain records for a Report and to sort the records in the Report. Also, a Form is typically sequenced by the Record Key. Is it possible to sequence a Form by a different Field by using a Query as the source of the data for a Form, i.e.. sorting the Query by a Date Field that is not the Record Key?


Is it a good practice to always create a Query from each Table and use that Query as the source for each Form and Report?


Is it better to sort records with a Query or in the Report itself?


I frequently use Table Lookups for some fields. If I want to add a Record to the Lookup Table, I go straight to the Table, add the Record and resort the Table. Is it better to add the Records using a Form, create a Query to resort the Table and use the Query for the Table Lookup?


Can you recommend a book that covers these “best practices”? The books I’ve seen either cover only the Access basics or cover complicated applications without explaining the reasons for their implementation decisions.


Thank you.

[June7]

I don't think query is inherently better than table as form RecordSource. I often bind forms directly to tables. Use query if you want to build in static filter criteria or create fields with expressions. Just depends what you want to accomplish in the form.

Query sorts are ignored by reports if report Grouping & Sorting features are applied.

I NEVER set lookups in tables, especially if alias is involved. I prefer to see the true value of the field, not the alias. http://access.mvps.org/access/lookupfields.htm

[orange]

Further to June's advice, I recommend you adopt a naming convention that does not allow spaces in field names.
There are lots of tips in Crystal's book

[ranman256]

I too think it's whatever works for you.
Note 1, sorting the query is useless once it hits the report...the report takes over sorting.
Note 2. I have used a loose Microsoft naming convention , all tables start with t . no need for tbl
tables start with t
queries start with qs (select) qa (append), etc.
reports start with r
macros start with m
This is really helpfull in programming, when you see code and go ..what is this object? Boom, you know where to look.

[John_G]

Hi -

I tend to agree - much of the time there are no "best practices" - it really is what works for you.

For your question on Table Lookups - are you talking about a lookup in a form, using a combo box, or a lookup field as part of the table itself? IMO, if it's the latter, don't use them. They are an abomination introduced by Microsoft, and cause more problems than they do convenience.

If you are talking about tables used as the source for dropdowns on a form, I usually just update them directly (without a special form), especially if updates to the table don't happen very often.

My 2-cents worth.

John