Question what is good practice in access?

[pteare]

Hello,

I'm currently in the process of gutting and redoing a bookings database (access 07). I built it last year, and it was the first database I've built. As such, it's not great! I'm re-doing it to make it more efficient and faster for me to use (I'm the only user so it doesn't have to be too user friendly...)

As I'm re-doing it I thought it a good idea to correct all my novice errors and make it more standard. things like I'm re-naming all my table tblTableName, as I've been looking into these forums lately and I see everyone does that and it doesn't half make the code easier to understand!

My question: is it good practise to put any formatting of fields in the tables themselves or in the forms that access them? For example putting in a default value of a date field so it puts today's date in, should that be in the table or in the form? does it matter?

Also any other top tips for standards i should use to make life easier / a good place to look up such things?

I hope I'm not opening a can of worms here!

Many thanks,

Phil.

 

[georgedwilkinson]

As I'm re-doing it I thought it a good idea to correct all my novice errors and make it more standard. things like I'm re-naming all my table tblTableName, as I've been looking into these forums lately and I see everyone does that and it doesn't half make the code easier to understand!

You are opening a can of worms.

I never, ever, ever, use the prefix tbl before my table names. Tables are the most important part of the system and should be named for easily discussing the system, not to conform to some odd standard that no other professional DBMS suggests.

I always name table names after the object in the real world or business world they mimic. For instance, Addresses mimics a real live address in the real world. Parties mimics a real live person or organization in the real world.

Name junction tables after the objects they represent: PartyAddresses, PartyEmailAddresses, etc.

In the case where you create a junction table between a table and itself, describe the relationship in the name: PartyRelationships is the relationship between 2 parties, for instance. You'll see some of this when kitting parts. I have a ton of these in my systems because it can represent obsolescence, alternative addresses or parts, aliases, or whatever.

And you may have noticed, I always name tables with plural spelling: Parties, Invoices, Addresses, AddressTypes, PartyAddresses, InvoiceLineItems, etc.

I always name columns in tables with a singular name (with a few exceptions): AddressType, TaxableType, etc.

Use "types" liberally throughout your system.

If you name any object in Access with "usys" at the front of its name, it will become a hidden object. That doesn't seem to matter much in Access 2007 since the only easy thing to do in 2007 is un-hide objects.

Name other objects after some standard with a prefix indicating the type of object/control: cbo, txt, lbl, frm, etc.

Throw every bit of advice I just gave you and spend a ton of time normalizing your design. It just really is that important.

My question: is it good practise to put any formatting of fields in the tables themselves or in the forms that access them? For example putting in a default value of a date field so it puts today's date in, should that be in the table or in the form? does it matter?

My personal preference is to format the fields of the table so that the objects that are based on them will inherit their formatting. So every time you create a field on a form or report with a given column, it will inherit the combo-boxes and so-forth.

The down side to this is that people can then easily use the table for data entry, which is a no-no. So you can overcome this by basing a query on each table, applying the formatting to the query's fields, and basing your forms and reports on the query.

Good luck.

 

[Banana]

George has good pointers.

Regarding prefixing the tables, I do it myself, though I do prefix it with 'tbl' for base tables, 'jct' for junction tables, 'lkp' for lookup tables. One minor benefit is that it helps organizes the groups of tables in the Database Window. The name after the prefix, I use similar rules to George's.

In other RDBMS, that would be kind of useless, but Access is a special case in that there's several objects (e.g. tables, queries, forms, reports) that could share same name (e.g. tblCustomers, qtrCustomers, frmCustomers, rptCustomers, which each lists all customers but in a different formats and for different purpose).

Furthermore, for queries, I further differniate the queries- qlkp for rowsources of comboboxes and listboxes, qrst for updateable recordsource for forms, qrpt for nonupdateable recordsource for reports, qprp for preparatory queries (e.g. queries that are then referenced in another queries but never referenced directly themselves). Ditto for forms- frm for actual forms, sub for subforms.

In my case, it serves as reminder as to their function. But that's just me.

The_Doc_Man pointed out in another thread that you must come up with a naming convention that you will actually stick with. Even the greatest convention is useless if you don't consistently use or waffle on its use, so if you're not comfortable with it, reconsider if you really want to be using it. Once you're up to your knees in the project, altering the convention will be a hell on wheels, with a beeyotch' rims.

 

[WayneRyan]

Phil,

The naming of objects of objects seems to have been covered pretty well, but I'd add
that now's the time to remove any "weird" names. Get rid of the embedded spaces and
special symbols in your names.

Also, you have luxury of having used the software for quite some time. Are there any
normalization issues that have made things difficult? Get rid of the repeating fields
and fields with "non-atomic" values.

Do you have any issues/improvements that can make the data entry/reporting easier?

Wayne

 

[ChrisO]

Phil.

Re Banana’s advice on picking a convention and sticking with it.

In the past I have given that advice but now think it’s not quite correct.

Certainly, in any given project, there should be a consistent naming convention.

But that does not mean that we have any control over which convention we will use. When programming as part of a team it becomes the convention of the team that we should follow and that will not necessarily comply with our own.

Also, to have a fixed convention would inhibit the development of our programming style. If indeed it is true that at some point we must remain constant then there can be no development passed that point.

Consistency of style across any given project, even if it’s not of our choosing, is fine.
But be prepared to change your style if you see something you think is better.

For the reasons as already stated by Banana I too prefix table names.

It’s simply a matter of style and it seems to me that there is little place for words like never or always.

Regards,
Chris.

 

[dkinley]

Greetings Phil,

My question: is it good practise to put any formatting of fields in the tables themselves or in the forms that access them? For example putting in a default value of a date field so it puts today's date in, should that be in the table or in the form? does it matter?

My tables are just places to house data. I do not put any sort of formatting or what-have-you so that I don't have to rememeber that there is something there. Everything is done in a form for me and I am consistent in that effort.

Also any other top tips for standards i should use to make life easier / a good place to look up such things?

Per the naming reference, I think that is an individual/team consensus. I would say that standard notation is the Hungarian naming convention. I am not claiming that I or anyone else truly follow it, but it just starts you thinking logically. I myself use tTableName, fFormName, etc., because I am too lazy to write the 3 letter designation. My queries are slightly different, usually like q_fFormNameTheQueryIsFor or q_rReportNameTheQueryIsFor.

I hope I'm not opening a can of worms here!

You are.

I say that because the less you know the more simplistic your database is. The analogy are the first cars - basically 4 wheels and a gas tank. Then came air conditioning, power windows - you get the point. My first database was just tables and then it was like 'Ooo .. forms!'

Same in Access, with the complexity comes many added benefits as you churn out more tricks to do some nifty things such as automation and validation. It is more complex to code, but you are a more learned individual now and want to apply and incorporate every aspect into it.

The trick here is not to spangle it up with every trick, only those that apply and have a real reason for being there.

One of the best resources is you have tons of cool people who are very intelligent and very willing to help out. I'm just half as cool as I think I am. Also, there is the vast repository of sample databases. I know I have spent time over there scrutinizing over many of them just to see if I could improve on what I had done or planning to do.

Good luck!
-dK

 

[Velid]

Not using table prefix means that you can't create table and query with the same name (something I do often to keep it simple) and use it in SQL with VBA. BTW, simplifying database is always a good practice. Use modules and make public functions to make your code reusable also helps. BTW, hi, I'm new here.

 

[neileg]

I'm the total opposite to George. I always prefix my object names, tblMyTable, frmMyFrm, cboMyComboBox, etc. This enables me to use George's argument about meaningful names to apply to all objects, so I can have tblDogs, qryDogs, frmDogs, etc. I try to keep the data in my tables as simple as possible, no formatting (though I will use a currency datatype if appropriate because it has other advantages), no default values. I prefer to apply formatting at the last opportunity, so usually in a form or report. I find this gives maximum flexibility.

 

[pteare]

hi everyone,

just to say thanks very much for all the ideas. It's very kind of everyone to give up their time and share their experience. Great to hear some debate on naming too! And the normalisation, thanks for the pointers there, I'm going to spend a good while on that first.

Thanks again, I'm sure I'll be speaking to you all again over the process of this!

Phil.

 

[gemma-the-husky]

i general name prefix linked tbls with tbl, and local tables without the prefix - just so it keeps to the two sections together

i also prefix queries with qry, but in the end apps get so big, its hard to keep track of everything, and i find however you name queries, you are almost bound to end up duplicating functionality somewhere.

i do put underscores in object names, and never spaces

 

[dkinley]

i do put underscores in object names, and never spaces

Aye - forgot that one. That is a big one in my list too.

-dK

 

[Banana]

I generally prefer CamelCase over underscore, mainly because it's less work to type out, say, ClientServiceIncident than Client_Service_Incident.

No spaces (and any other special characters), however, is a Must-Have™.

 

[DCrake]

In line with banana I too use CamelCase especially when coding variables in VBA, such as Dim StrMyVariableName As String. The logic behind this has 2 heads; first when writing your VBA type it in lowercase then as soon as you hit return the variable will revert to CamelCase. Therefore you can be confident you typed in its name correctly. Also it makes it easer to read and write.

As far as naming conventions go I tend to be a bit elaborate. Yes I prefix my tables with Tbl, but I also prefix my fields with fld. Why you ask? well when writing my VBA I can differentiate between an unbound control on a form called txtSurname and a field in a table called fldSurname.

Another good practice is to use plenty of documentation in your vba, especially at important point where something complicated is going on. Looking at the same piece of code 12 months down the line will have you thinking "What was I doing here...?"

Also if you have to make an amendment to the code which is signficant to the way it performs I usually state what was changed from and to and dateing the change made.

Typical example is when you were asked to add or drop a piece of validation to the code:

"Added new validation as per intructions from [Whoever] on [Date] [Reason]"

CodeMaster:

 

[mvester]

This post reminds me of a client that built their own Access database. It was a simple table holding resume information from prospective employees.

The table contained field names like:
"The data in this field contains the applicant's birthday"

That was the actual field names. Plenty of spaces and even a ' character. Of course I had to change all the field names before I could anything with their database.

Also, the client wanted to keep references on the database too. That went something like this.
"The data in this field hold the propspective employee's reference last name"
"The data in this field holds the propspective employee's reference first name"

and so on with the telephone number and address.

The second and third reference were in the same table and went like so:
"The data in this field hold the propspective employee's second reference last name"
"The data in this field holds the propspective employee's second reference first name"

The table had the max number of fields and the client requested my services because they had run into a roadblock. They wanted more fields in their table. A few days of work and I had their database normalized with proper field names, some data entry forms and a few reports. And I was able to keep all their data. If there ever an example of how to do everything wrong, this client had certainly achieved it. The dangers of the 2 day MS-Access training course.

 

[gemma-the-husky]

actually, the point that mvester raises is relevant

i find it very handy to use the description field in a table to store notes about the field use, date added etc - although i know it shows in the status bar of forms sometimes.

 

[The_Doc_Man]

Depending on where you work, naming conventions might exist with very serious consequences for not using same. For instance, I don't know of any government agency over three years old that DOESN'T have a naming convention. Usually a bad one, at that. Names have to be way too descriptive in that context.

As to tbl vs. qry vs. frm vs. rpt vs. some other prefix... when left to my own choice, I use no prefixes because I never allow my queries and reports to have the same name. Too much confusion. There is a fog-factor in having too many names that could overlap each other. Using the prefixes allows me to get foggy real quick.

We must also remember this: A one-person database needs no prefixes. It is only a team effort for which conventional naming becomes important.

 

[mvester]

My story was of a client that did everything possible, wrong. I use prefixes for everything except field names. Just a habit I got into a long time ago. I have a second prefix, eg. qryXXXsomequery, to keep related forms, queries and reports together. The vba code in the modules does not have the second prefix because the code, more often than not, is used by other parts of the application.