Access 'features' that should be deprecated (1 Viewer)

Well I'll be darn. My Quick Basic background paid off. Would you believe only 21 Reserved words and 1 Special character in a query. I'm blown away. If he ever comes up with a relationship and design checker...I'm toast.

RuralGuy said:

Well I'll be darn. My Quick Basic background paid off. Would you believe only 21 Reserved words and 1 Special character in a query. I'm blown away. If he ever comes up with a relationship and design checker...I'm toast.

Click to expand...

Show off...!
Or was that all in one query?
BTW if you want to fall foul of another forum restriction try just writing

yes

Click to expand...

or

no

Click to expand...

as your reply

I'll admit I hesitated to post the results but the facts are the facts. I've gotten the "Not enough words in your reply" before and had to find a way to fluff up my responses.

ridders said:

With regard to captions, I agree with the point you raised if you are trying to understand databases created by others.
However, for my own databases where I will obviously know my own naming conventions, I don't see it as an issue.

Click to expand...

My only experience with Captions was on another developer's work and the Captions didn't bear even a passing similarity to the field names. It was quite impossible to make sense of.

I can see where Allen is coming from by using them to insert a space in a name.

Allen sets a caption for all table fields.
He adds a space before each capital letter in a field name. For example:
- LastName => caption = Last Name
- VehicleID => Vehicle ID etc

Click to expand...

Using quite different table captions might even work during the form and report design phase. I do something similar sometimes with table level lookups because it saves time on the forms with the Wizard generating the combo row sources from them. However I always remove them from the table once the first few forms are done. (After that I often copy and paste groups of common controls between forms.)

I work with databases where the fieldnames are quite different from the business terminology so I need to edit the form and report label captions to something quite different anyway. Moreover they are often hybrids of MSSQL Server and Access so the field caption facility isn't always readily available.

I also like to edit the names of the control labels on forms so I edit the default captions at the same time.

The reason I thought you had misunderstood the way field captions work is because you only mentioned how they affected the Datasheet View when in fact they apply to the label captions on all types of forms generated by the Wizard.

Sorry if I have offended you. It certainly wasn't my intent.

Well I've run it on a currently used system (inherited) and was amazed to only have received 329 warnings. Although apparently one of the queries somewhere has ' in its output field name, which throws an error. Also it tries to open a report that's formatted for a printer I don't have, and I don't want to save it with the default printer settings...

Not helped is the number of fields called [Description] or [Note] in a load of tables. I wasn't aware Note was a reserved word... I can see that Description might be.
It also found a cracking table field name I didn't even know existed [MLR/SLR32+]

I'm glad to say not responsible for most of these, but will have to put my hand up for some... Very interesting!

@Galaxiom
Don't worry Greg - I wasn't offended but I admit I did feel the need to defend myself!

When I first started I knew nothing about naming conventions or reserved words.
As a result, there were lots of spaces in field names as well as other reserved words & special characters that I no longer use.
Of course I didn't ever use field captions at that time as the field names were already readable!

Recently I tried using Allen Browne's mixed case spacing caption approach but on balance decided to abandon it as it confused me!

However, I do use other methods of improving readability in tables
e.g. checkboxes for all boolean fields and alternate rows in green as below (complete with 2 reserved words)



I have code that applies these settings automatically to all linked SQL / Access / Excel tables when I relink my FE database to BE datafiles.

This is just for my own benefit - end users of course just interact using forms etc where I widely employ user friendly captions

@Minty
Oh dear, your 'reserved word & character score' is also much 'better' than mine.
Mine is also a db that I inherited back in 2006 & most of the reserved items pre-date my involvement.
Its still widely in use in several schools and works extremely well
Its now a 130MB+ FE behemoth with some 16000 objects but I was still surprised how high the score was.
Much of this are 'fields' due to crosstab queries which I use a lot.

However, even restricting the count to tables gives a total of 499!

The field name '% 3+ A-A*' is one of my own - I added the '?' just for effect in my last post
When I created it I had considered 'Percent3OrMoreGradesAOrAStar' but preferred the shorter version!

I'm hoping someone else will admit to a worse score than mine but somehow doubt that's going to happen

Since I'm doing more work with sql server at the moment, the caption option doesn't work. I also have a need to provide a multi language solution. And whilst we're at it, to maintain a 'standard' back end, some clients call their customers customers, others call them clients and yet others 'consumers', 'shoppers' etc.

So I've now developed my own 'caption' table, which also covers unbound labels, buttons etc and which for the language requirement also includes things like the msgbox messages, some value lists etc.

When the app is loaded - a dictionary is populated from the table based on the profile of the user.

when a form/report is loaded, all controls are looped through and if an alternative exists in the dictionary, the caption/property is substituted.

A user with full admin rights can access the caption table and change the alternative caption if required. Particularly relevant for the language requirement - although a lot better than they were, translators can still come up with the wrong foreign alternative when there is not much context

Hi CJ

Since I'm doing more work with sql server at the moment, the caption option doesn't work.

Click to expand...

But you can apply captions to SQL linked tables in the Access FE - I do so anyway.

I'd be interested in seeing an example of this approach when you've time to upload something

But you can apply captions to SQL linked tables in the Access FE - I do so anyway.

Click to expand...

I didn't know that, but in any event it doesn't cover non table objects such as unbound labels, labels bound to an unbound control, buttons etc.

but in any event it doesn't cover non table objects such as unbound labels, labels bound to an unbound control, buttons etc.

Click to expand...

That's the bit I'd be interested in looking at ... with the dictionary part

I'd be interested in seeing an example of this approach when you've time to upload something

Click to expand...

Here you go.

It's a bit crude but demonstrates the principle I am using. I always distribute .accde's so forms do not change except under program controls. And the reason for doing this is to minimize network traffic, have a basis which will work across any backend server and not slow down form opening.

The basic assumption is no duplicates. So within the context of a company 'view', fields are called the same from the users perspective wherever they are used. i.e. you wouldn't call the a field called customer a customer on one form and a client on another. This applies to unbound labels as well - if a label is captioned Customers on two forms, you cannot 'translate' to customers on one form and clients on another. Similarly a field called customer but with a 'caption' of client would conflict with an unbound label with the caption 'customer' but to be translated to 'Consumer'. Clear as mud I expect

Continuous forms - headers, although associated with a bound control by inference, don't have any actual relationship - although you can possibly determine a relationship by matching on the control left property. This example does not cover this, but it would work for a datasheet form.

I've included code (function popDBCaptions) which scans through tables and forms and creates the necessary records. In theory this would be run once when the project is completed and perhaps needs some records deleting where the translation value is the same as the default.

To be fully complete it also need to run through queries (to pick up names such a fld1*fld2 AS Total) and reports plus most likely vba code for recordsets etc. The way to do queries would be to open the query as a recordset and scan the field collection like a tabledef. Scanning VBA code will very much depend on your style.

Your specific interest is the use of the dictionaries. in the modCaptions module, the function popdctCaptions populates the dictionary (in theory run once when the app opens, but this example runs when you change company) whilst getCaptions interrogates it - which runs once every time the form is opened.

This could be put into a class module, but not really necessary.

Basic rule is

if label has a parent bound to a field, lookup the 'translation' for the field, otherwise lookup the translation for the label caption. If there is no translation available, leave the caption unchanged.

fields ending in PK are ignored as a matter of course (users do not see the PK)
captions of 1 character are also ignored - assumption is it is something like a % or currency symbol.

Let me know your thoughts

Hi Chris

Thanks for uploading this.
Had a quick look and can see its potential.
Its my wife's birthday so won't be able to examine it properly for a day or two.

I saw the UK variant option and immediately thought of ways of linking this to this earlier thread to which we both added silly examples
Perhaps add e.g. cockney, Geordie, scouse and Bristol (with an 'l' on the end of each word). Do you think that's a good 'ideal'?

https://www.access-programmers.co.uk/forums/showthread.php?t=294673

Do you think that's a good 'ideal'?

Click to expand...

don't see why not - sounds like fun!

Bristol (with an 'l' on the end of each word).

Click to expand...

bit like Welsh - just add 'then' to the end of each sentence

Think I've got a book somewhere called 'How to speak Brisle'

A gert lush ideal