New programming standard for Access VBA

[Mike Smart]

Greetings

I've published a new programming standard for Microsoft Access VBA on my site http://www.learnaccessvba.com.

Most of the "generally accepted" standards are included but I've also included standards for such things as field names, many-to-many link table names, foreign key names... that sort of thing that you don't often see formally defined.

I'd like to further refine the standard for the next edition of my book so would really appreciate any suggestions for your own "favourite standard" for inclusion. If you disagree with any of the standards posted (and can logically support your position) I'd also love to hear from you.

Mike

 

[doco]

I'd like to further refine the standard for the next edition of my book so would really appreciate any suggestions for your own "favourite standard" for inclusion.

Will receive royalties for use of suggestions?

Good "favorite"
Bad "favourite" (looks like a name for a rock)

 

[stopher]

One standard I would recommend is that you get you spell checking done by a Brit not an American

Looks like a useful resourse. I'll add it to my favourites

Stopher

 

[doco]

LOL!

Still looks like a name of a rock!

Two countries separated by a common language..

 

[Mike Smart]

Thanks for the input on American English/UK English. I would appreciate some feeback on the standards as well!

You may be interested to know that all materials on my site are written in American English. I've even included a statement to that effect in: Introduction To The Free Course - 1.

I did begin by writing it all in UK English but then I ran one of my courses in Washington DC and realised (or should that be realized)! how confusing that can be (especially as all Access properties are, of course, spelt in American English).

My usual style remains UK English, however, so you'll need to figure things out sometimes. My favourite Americanisms are "fanny pack" and "suspenders" both of which mean something entirely different in UK English.

Have fun

Mike Smart

 

[MarkK]

I like your standards. I like ALL_CAPS for constants.
I prefer "m_" to prefix module level variables.

strSomeVariable
mstrSomeVariable
m_strSomeVariable

I disagree that all routines require error handling. Check out the code the button wizard writes. Ug. Work with a module full of those?
And is it true that even little old ladies in the UK grow 'pot plants?'
Cheers,
Mark

 

[Mike Smart]

I like your standards. I like ALL_CAPS for constants.
I prefer "m_" to prefix module level variables.

strSomeVariable
mstrSomeVariable
m_strSomeVariable

I disagree that all routines require error handling. Check out the code the button wizard writes. Ug. Work with a module full of those?
And is it true that even little old ladies in the UK grow 'pot plants?'
Cheers,
Mark

Hi Mark

Thanks for the feedback.

The reason I prohibit m_strFirstName in my code is so that when I see an underscore I absolutely know that I'm dealing with a constant (but the all caps is, admittedly, a dead giveaway too). The other reason is that it is just plain old fashioned for me. It isn't an important rule though so be like Douglas Bader and cross it off your personal list.

Error handling IS an important rule though. How long does it take to cut-and-paste error handling when you QA the app? It takes me about 5 seconds a sub so why leave it out? I often leave error handling off when I'm developing the app but always include it everywhere before delivery to the client.

Some of the code the wizards write is truly awful by the way - don't look there for inspiration! There's a chapter in my book (but not in the free course) called "Improving Wizard Generated Code". It begins with the Tolkien quote "Do not meddle in the affairs of wizards, for they are subtle and quick to anger" but my experience is that you often have to ignore Tolkien's advice.

Have fun

Mike Smart

 

[Bat17]

1-12 Do not use the addition operator for concatenating strings.
Use & for concatenating strings and + only for arithmetic operations.

I would argue with this one as there are times when propagating Nulls in strings can be useful.

3-1 Primary keys are always meaningless and have the data type: AutoNumber.

Have a problem with this one as I have a number of tables that already have a unique required long number. Seems to add redundancy to add an autonumber as well

As you have already barred Global variables in 1-11 I would stick with it in 6-1 and get rid of the 'g' option.

8-2 All variables must be strongly typed.
This includes variables declared as parameters for subs and functions.
Good: Dim strFirstName as string
Sub DeleteCustomer( lngCustomerID as Long)

not quite sure on this as it prevents the error trapping of badly passed data but arguably that should be caught before it gets this far but then this conflicts with the philosophy in 9-1


Peter

 

[Mike Smart]

Dear Peter

Thanks a million for the feedback. This is just what I need to kick my thought processes into new directions!

> Quote:
> 1-12 Do not use the addition operator for concatenating strings.
> Use & for concatenating strings and + only for arithmetic operations.
> I would argue with this one as there are times when propagating Nulls in
> strings can be useful.

Do you mean string data contained in a variants containg NULL

Something like this:-

Dim varFirstName As Variant
Dim varLastName As Variant

varFirstName = Me.txtCustomerFirstName
varLastName = Me.txtCustomerLastName

If IsNull(varFirstName + varLastName) Then
Call MsgBox("You must enter both first and last names")
Else
Call MsgBox("Well done, you provided both names")
End If

If this is what you mean wouldn't the code be more readable simply using IsNull's around the assignations? Perhaps I'm completely misunderstanding you. So that I can understand the point can you give an example of when using the + operator to concatenate strings would provide the best (or a unique) solution to a real-world scenario?

> Quote:
> 3-1 Primary keys are always meaningless and have the data type:
> AutoNumber.

Have a problem with this one as I have a number of tables that already have a unique required long number. Seems to add redundancy to add an autonumber as well.

There's a few different reasons behind the logic of primary keys being meaningless. The most important for me is the "set in stone" rule that the primary key must be stable and NEVER CHANGE. Access does provide the possibility of cascading updates to allow you to break the above rule but I consider that to simply be a work-around for bad design and not a desirable thing to ever do (and I think Codd would agree too).

If your existing long is meaningful it is potentially subject to change. For example a company may have purchase order numbers as POnnnnnn where nnnnnn is a unique number. This seems a fine Primary Key until one day the MD demands that purchase numbers are set in the form nnnnnnPO instead.

In my systems the user never sees the primary key (or even knows it exists). For me this is a fundamental rule of good database design.

> As you have already barred Global variables in 1-11 I would stick with it in 6> -1 and get rid of the 'g' option.

Did you notice that 6.1 says: "Note that, despite this standard, it is nearly always poor programming practice to use any globally
visible variables in an application". I did think about leaving it out entirely but there is always the case where you inherit some terrible code that is riddled with globals and, in this case, adding a prefix makes the mess easier to understand.

> Quote:
> 8-2 All variables must be strongly typed.
> This includes variables declared as parameters for subs and functions.
> Good: Dim strFirstName as string
> Sub DeleteCustomer( lngCustomerID as Long)
>
> not quite sure on this as it prevents the error trapping of badly passed
> data but arguably that should be caught before it gets this far but then
> this conflicts with the philosophy in 9-1

I agree with you here. There's a school of thought that suggests checking variable types on the way in to subs and raising an error for bad data types. Code would be even more robust if this were done (though I don't do this myself, for me it is overkill). I'm very unhappy that VBA6.x does automatic type conversion that can't be switched off (and very happy that this is fixed in vb.net). I'd almost describe this behaviour as a "bug" in VBA that the above simply works around.

But you can still stay within the standard and do the above. Simply declare the variable as Sub DeleteCustomer( varCustomerID as Variant).

Look forward to your clarification on null propagation.

Mike

 

[Bat17]

I don't have an actual sample to hand but i was thinking more along the lines of

strIn = "in("
strIn = strIn & (Me.myfield1 + ", ")
strIn = strIn & (Me.myfield2 + ", ")
strIn = strIn & (Me.myfield3 + ", ")
strIn = strIn & (Me.myfield4 + ", ")
strIn = strIn & ")"

Though obviously you would still have to clear the trailing ',' with this contruct

Peter

 

[Mike Smart]

I don't have an actual sample to hand but i was thinking more along the lines of

strIn = "in("
strIn = strIn & (Me.myfield1 + ", ")
strIn = strIn & (Me.myfield2 + ", ")
strIn = strIn & (Me.myfield3 + ", ")
strIn = strIn & (Me.myfield4 + ", ")
strIn = strIn & ")"

Though obviously you would still have to clear the trailing ',' with this contruct

Peter

Peter,

My first thoughts were that the following is more readable?

strIn = "in("
If Not IsNull(Me.myfield1) Then strIn = strIn & Me.myfield1 & ", "
If Not IsNull(Me.myfield2) Then strIn = strIn & Me.myfield2 & ", "
strIn = strIn & ")"

But then I slept on it and began to question the need for the rule at all! It was only in the following contrived example that using the addition operator instead of the concatenation operator produced different results:

Dim varOne As Variant
Dim varTwo As Variant
Dim strAdd As String
Dim strConcatenate As String

varOne = 1
varTwo = 2
strAdd = varOne + varTwo
strConcatenate = varOne & varTwo

In this contrived example strAdd will contain “3” and strConcatenate will contain “12”.

My second thoughts are that the rule is useful mainly simply for code readability as using the addition operator would be unlikely to introduce a bug. If anybody else has had to fix a bug caused the the incorrect operator I'd be very grateful for your feedback.

I also like your code better now, it is more elegant and it is apparent exactly what you are doing.

So I will re-draft the rule.

Best Regards


Mike

 

[RoyVidar]

"In my systems the user never sees the primary key (or even knows it exists). For me this is a fundamental rule of good database design." - I think you need to realize that what is a fundamental rule to you, is only that, a fundamental rule to you. The notion that "Primary keys are always meaningless and have the data type: AutoNumber.", is also one of these things that are fundamental for some. The sad thing, is that for some it becomes so fundamental they go on being completely fundamentalistic about it.

Even if you believe Codd agree with you (which I don't believe), there is no fundamental rule in RM supporting your rule. To put it mildly, it is controversial. There are some of us who use the state code as primary key for the state table, and use composite primary keys in junction tables, this is not only allowed, but some even recommend this

Besides, what type of primary key to use, has nothing to do with programming standard in Access VBA, it's a database design or implementation issue.

Another database design issue, is naming of tables and columns. I question the need to create a separate new Jet standard, when there are general, accepted standards that can be used across platforms. There's even an ISO standard (ISO 11179). Most of us will sooner or later work with other platforms than Jet, then it doesn't make sense, in my view, to chose a proprietary style, especially one deviating so much from generally accepted standards.

Among the rest of the stuff, I think you have chosen paths deviating too much from generally accepted standards, some already commented on, to make these "standard" widely accepted. For most of us, it's enough to use whatever adapted subset of Hungarian/Reddick/Lezinsky/other we use (or the one we're forced to use at work, in a specific project...). The most important is probably to choose a style and stick with it. Well, one shouldn't choose something deviating to much from the generally accepted standards, as it would make it harder for those unfortunately enough to have to manage it afterwards. For those who haven't yet chosen a style, I would much rather recommend looking into what is suggested for .Net development, ISO standards etc, so they don't get stuck in proprietary styles/standards.

Another thing - usually when people post things of use, they will post in the faq section of the site, and not link to what seems like their own commercial web site marketing books, courses, fora and whatnots to download something with huge copyright statements across each page, and "full book available from..." in the header section.

If this isn't pure marketing, then it's pretty d@rned close...

 

[Mike Smart]

Hi Roy

Thanks for your comments.

> to download something with huge copyright statements across each page, and "full book available from..." in the header section.

I've actually taken this on board. I really didn't think that the watermark would offend anybody but I can see now that I was wrong (and there isn't really any need for it) so I've taken it off the PDF's this morning. I've also removed the header section from the HTML pages too. Do let me know if you think the site still looks commercial. I'm not putting any advertisements on my site (not even Google Adwords) but I did think it reasonable to have a link to my own Book listing on Amazon. The single link to my training site on the homepage was only to let people know what I do for a living not to try to sell training courses. Do you think I should remove it?

>

"In my systems the user never sees the primary key (or even knows it exists). For me this is a fundamental rule of good database design." - I think you need to realize that what is a fundamental rule to you, is only that, a fundamental rule to you. The notion that "Primary keys are always meaningless and have the data type: AutoNumber.", is also one of these things that are fundamental for some. The sad thing, is that for some it becomes so fundamental they go on being completely fundamentalistic about it. Even if you believe Codd agree with you (which I don't believe), there is no fundamental rule in RM supporting your rule. To put it mildly, it is controversial. There are some of us who use the state code as primary key for the state table,

I'm massively agreeing with you here Roy. Simply clinging to a quality standard as an "article of faith" is plainly silly. No articles of faith here, but you do need to rationally state the advantage of using a meaningful primary key. Discussion is rational and argument is pointless. While I feel quite secure with this rule at the moment I have a completely open mind and am ready (and even eager) for you to convince me that meaningful primary keys have an advantage. I know that this subject is controversial so that's an even better reason to put it to bed.

> and use composite primary keys in junction tables, this is not only allowed, but some even recommend this

You've clearly read something in the standard that leads you to think that composite keys aren't allowed. Is there something I've written that gives this impression? Can you tell me where it is so that I can re-draft it more clearly?

>Besides, what type of primary key to use, has nothing to do with programming standard in Access VBA, it's a database design or implementation issue.

The standard is about writing data-centric applications using Access and VBA. I think you have to address database design issues too if the standard will contribute to developing solid applications. Solid code over a poorly implemented database will still result in poor applications and my aim is to address all areas - not just code syntax - in this standard.

> Another database design issue, is naming of tables and columns. I question the need to create a separate new Jet standard, when there are general, accepted standards that can be used across platforms. There's even an ISO standard (ISO 11179). Most of us will sooner or later work with other platforms than Jet, then it doesn't make sense, in my view, to chose a proprietary style, especially one deviating so much from generally accepted standards.

I use the same conventions with SQL Server. Which items do you regard as Jet specific? Which items do you percieve as deviating from generally accepted standards? I'm really interested in your feedback.

> For most of us, it's enough to use whatever adapted subset of Hungarian/Reddick/Lezinsky/

I agree that the core of the standard should be generally accepted best practice and I actually quote Charles Simonyi in rule 1-6 and link to the full draft of his original historic work both on my site and in my book.

As you've observed, the standard addresses more than naming conventions (important though these are). I don't think there's anything in the excellent Hungarian/Reddick/Lezinsky/ conventions that contradict anything in the standard. Let me know (specifically) which items you feel need changing if you disagree. It is the parts of the standard that are missing from the above works that make it wothwhile (and I have great hopes of reaching consensus on the parts that are controversial by anybody that wishes to engage in rational discussion). My aim for the standard is to provide a superset of generally accepted best practice and not a subset - and you can help me a lot with your feeback.

>The most important is probably to choose a style and stick with it. Well, one shouldn't choose something deviating to much from the generally accepted standards, as it would make it harder for those unfortunately enough to have to manage it afterwards.

I completely agree that generally accepted standards should be the foundation of the standard. I spend much of my time helping companies to fix systems that were built with major design defects when they may have worked fine if the simple-to-understand rules in this standard had been followed.

I really appreciate your feedback and do hope that you will find it worthwhile to comment on the points above.

Best Regards

Mike Smart

 

[Mike Smart]

Dear Peter

I would argue with this one as there are times when propagating Nulls in strings can be useful.

Here's my re-draft:-

1-11 Do not use the addition operator to concatenate strings except when propagating nulls.

Use & for concatenating strings and + only for arithmetic operations.
There are not many cases will you will get a different result if you use the addition operator instead of the concatenation operator to concatenate strings but here’s an example:

Dim varOne As Variant
Dim varTwo As Variant
Dim strAdd As String
Dim strConcatenate As String

varOne = 1
varTwo = 2
strAdd = varOne + varTwo
strConcatenate = varOne & varTwo

In the above code strAdd will contain “3” and strConcatenate will contain “12”.

The primary reason for the rule is to maximise code readability. Use of the addition operator should be reserved for propagating nulls. When a null is added or subtracted from any other value the result is also null. For example:-

strIn = "in("
strIn = strIn & (Me.myfield1 + ", ")
strIn = strIn & (Me.myfield2 + ", ")
strIn = strIn & ")"

Is functionally equivalent to:

strIn = "in("
If Not IsNull(Me.myfield1) Then strIn = strIn & Me.myfield1 & ", "
If Not IsNull(Me.myfield2) Then strIn = strIn & Me.myfield2 & ", "
strIn = strIn & ")"

The example is for illustration only and does not address the issue of the trailing comma.

Any further feedback appreciated if you feel that it can be improved.

Best Regards


Mike Smart

 

[Mike Smart]

Another database design issue, is naming of tables and columns. I question the need to create a separate new Jet standard, when there are general, accepted standards that can be used across platforms. There's even an ISO standard (ISO 11179).

This standard is published on the web http://metadata-standards.org/11179/#11179-5.

The standard makes interesting reading but doesn't (and isn't intended to) provide the type of specific rules for table and field naming included in my standard. More importantly, the paper is written at a very academic level and would not be comprehensible to most of the Access developers that I teach. If anybody has a contradicting view upon this I would, as always, welcome the feedback.

Best Regards

Mike Smart

 

[Moniker]

I'm massively agreeing with you here Roy. Simply clinging to a quality standard as an "article of faith" is plainly silly. No articles of faith here, but you do need to rationally state the advantage of using a meaningful primary key. Discussion is rational and argument is pointless. While I feel quite secure with this rule at the moment I have a completely open mind and am ready (and even eager) for you to convince me that meaningful primary keys have an advantage. I know that this subject is controversial so that's an even better reason to put it to bed.

Often times, a meaningful primary key is the best decision to make in that the meaningless primary key doesn't identify anything about the record's content. For example, where I work, one of our driver tables contains a composite key that consists of a provider's ID (the PIN), a Market_ID, and a Measure_ID. While the Market_ID and Measure_ID were arbitrarily assigned, the PIN uniquely identifies the provider and it is part of the composite key used in every other related table. (Additional tables add additional identifiers, such as an Address_ID to identify specific providers with multiple addresses.)

By using a meaningful primary key, we can deduce a lot of information. For example, most of our constituents do not know that we have assigned Market and Measure ID values, and they only provide us with the provider's PIN. Since that PIN is part of the primary key, we can drill down to the data they are looking for. By this, I mean that if we know we got a phone call from someone in one of the New York offices and they wanted information on PIN 12345, we can look up PIN 12345, narrow it down to that region, and proceed from there. If we were just using an AutoNumber and they called asking about "Smith Hospital", we could potentially have 100 Smith hospitals with little way to identify them apart.

Keep in mind that the vast majority of our users have no idea that the PIN is a major component of the primary key (much less what a primary key even is), and that's fine. However, had we not made the design decision early on to make the primary key meaningful, our jobs would be a lot more difficult for what is basically an arbitrary reason.

 

[RoyVidar]

"No articles of faith here, but you do need to rationally state the advantage of using a meaningful primary key."

No, I have no need to do that. That debate has versed through all the newsgroups and webfora, in addition to other sources. All the pros and cons, both the real and the imaginary, of surrogates and natural keys has been reiterated ad nauseum. If you need this information, then a web search should provide ample possibilities of spending time reading hotheads from either side totally failing to communicate with each other

In addition to Monikers explanations, for the Access community, if you search for instance CDMA and/or the microsoft.public.access... NGs, Tom Ellison is one who favours natural keys, and have given very good and elaborate explanations in support of his view.

Here are som very few threads by a quick search
http://groups.google.com/group/micr...sdbdesign/browse_frm/thread/27157ee5b706d59a/
http://www.dbforums.com/showthread.php?t=515216
http://www.pcreview.co.uk/forums/thread-2595065.php

Me I use both, and attack anyone trying to deny me that with vigour

"I know that this subject is controversial so that's an even better reason to put it to bed."

So, you intend to settle the surrogate vs natural key debate once and for all <grin>

 

[RoyVidar]

"You've clearly read something in the standard that leads you to think that composite keys aren't allowed. Is there something I've written that gives this impression?"

Yes, 3 (3-1, 3-2, 3-3), but in perticular 3-1, i e what you quoted, from what I quoted from your doc. "Primary keys are always meaningless and have the data type: AutoNumber."

I would say that gives such impression <grin>

 

[RoyVidar]

"I use the same conventions with SQL Server. Which items do you regard as Jet specific? Which items do you perceive as deviating from generally accepted standards? I'm really interested in your feedback."

Not Jet specific, but as far as I can see, it isn't close to the recommendations I've seen for platform independent conventions either

Here are some links with a little information on parts of ISO 11179
http://groups.google.com/group/comp.databases.ms-sqlserver/msg/a27e73bb76e55c43
http://en.wikipedia.org/wiki/Data_element_name

 

[Mike Smart]

"You've clearly read something in the standard that leads you to think that composite keys aren't allowed. Is there something I've written that gives this impression?"

Yes, 3 (3-1, 3-2, 3-3), but in perticular 3-1, i e what you quoted, from what I quoted from your doc. "Primary keys are always meaningless and have the data type: AutoNumber."

I would say that gives such impression <grin>

Hi Roy

3-1: "Primary Keys are always meaningless and have the data type: Autonumber"

I suppose I thought it was obvious that many-to-many link tables consisting of concatenated foreign keys were an exception... but nothing should be assumed.

I've decided to break 3-1 into two in order to discuss them seperately:-

(a) Primary keys are always meaningless.
(b) Primary keys should always have the data type: Autonumber in Jet databases.

And I suppose reasonable wording for (b) that would avoid possible confusion could be:-

(b) Primary keys should always have the data type: Autonumber in Jet databases (except in the case of composite keys within many-to-many link tables).

In the case of 3-2 this wouldn't apply as both components of the composite key would always be required.

In the case of 3-3 I take your point again and will append a similar qualification to 3-1 to avoid any potential confusion.

Thanks for the feedback.

Best Regards


Mike Smart