Change the Caption of a field in a tble

[ebs17]

when creating a form

Thinking is good, thinking fully is better. In a normal bound form, the caption of the label attached to the control is displayed. The field name in the table doesn't matter at all.

For cases where you have to work with field names from tables on the surface, you will use a column alias in queries.

SELECT FieldNameA AS NeuerName [, FieldList]
FROM TableX

 

[MarionD]

Thinking is good, thinking fully is better. In a normal bound form, the caption of the label attached to the control is displayed. The field name in the table doesn't matter at all.

For cases where you have to work with field names from tables on the surface, you will use a column alias in queries.

SELECT FieldNameA AS NeuerName [, FieldList]
FROM TableX

Thank you.

 

[isladogs]

Setting a field caption can occasionally be useful for readability e.g. to display a space or use full words in a field name
e.g. field ErrNo with caption Error Number ; YrGrp => Year Group etc

Contrary to Pat's comments in post #3., you CAN change field captions for linked BE tables in the FE.
This works because changing captions is NOT changing the structure of the linked table itself.
The captions used will persist after the database is closed including after compacting the FE.

Doing this is equivalent to using an alias on a field name in a query

 

[MarionD]

Setting a field caption can occasionally be useful for readability e.g. to display a space or use full words in a field name
e.g. field ErrNo with caption Error Number ; YrGrp => Year Group etc

Contrary to Pat's comments in post #3., you CAN change field captions for linked BE tables in the FE.
This works because changing captions is NOT changing the structure of the linked table itself.
The captions used will persist after the database is closed including after compacting the FE.

Doing this is equivalent to using an alias on a field name in a query

Thank you so much for your friendly and helpful comment! I am just off to Switzerland for a weekend in the mountains... will get straight back to work after that.

 

[Pat Hartman]

I just thought that one could change it in the table first, then it displays that name as caption when creating a form.
In German it's called Beschriftung...(Caption)

This would not change anything existing. It would only affect newly created objects.

It seems like having a "nice" caption would be convenient but it turns out to have a thorn. That thorn is how it affects queries. Users NEVER see queries. Only YOU and your bound objects see queries so captions are no benefit for queries. In the past, not only were the captions shown when you opened the query directly - which you have to admit can be confusing - but they also were returned when you opened the query using VBA. That meant, that in your code instead of using the table names, you would be using the caption names - Verrrrrrrrry bad. That has been fixed. But they are still confusing if your caption names are very different from your column names. You have to keep in your mind two names for the same column.

The ONLY time a caption is useful is when you are creating NEW forms/reports and how many of those do you have to create? Usually we have only one form per table for maintenance. And when you are creating unbound forms, the captions don't come into play at all.

You can simulate the effect of captions when you are creating reports or queries for export to Excel. Just bind them to a query and in the query use alias'. That way if you use the wizard to build the basic report, the wizard will use the alias names. Of course if you have to write code in the report, this is the worst possible solution since the query will be returning the "nice" names rather than the real names. So, I wouldn't even do that. Bottom line. I don't use them.

 

[Pat Hartman]

The captions used will persist after the database is closed including after compacting the FE.

You're right. I remember discovering that. I immediately forgot it since I would never do that What happens to other apps that link to the BE? The BE is not actually modified so do they see the captions? If they don't then this is a terrible practice.

 

[spaLOGICng]

The bigger question is why are you doing this? Hopefully, you aren't allowing the user to make this change.

Also, changes to tables MUST be made in the BE database. You cannot make changes to linked tables this way. You would need to define a link to the BE db rather than the CurrentDB.

Here is a use case...

When I create a new DB starting from scratch, I usually begin with an Excel Workbook. I have an Excel Workbook Template that I use to construct Database with. Why? Because even though using the built-in Table Designer is easy enough, it is somewhat counterproductive. I prefer to begin with a List. I use a Table in Excel. This also acts as the beginning of a data dictionary, so it has multiple purposes. In the Excel Table, I can add everything I need, including Primary Keys, Indexing, and Relationships. I can change the Ordinal Position of the Tables and Feilds in this list. I can create the DB as many times as I want until I am happy with. I can scroll up and down the list rather than have to open the Table Designer. I can also create lookup columns in this list.

So, as you can see, there is a use case for updating the Caption and or Description in VBA.

Personally, I usually do not offer a Bigger Question until I at least answer the question. If there is a better way, I will offer that as a solution, but we should not be answering a question with a question unless it is to learn more about the question, itself.

As for the Linked Table, you absolutely do not need to connect to the BE to set the Caption, or the Description for the matter. Additionally, the Table can be open, and you can still change the Caption or Description.

Not only can you do this for a Linked Table in Access, but you can also do this against any Linked Table.

 

[Pat Hartman]

I can also create lookup columns in this list.

Poor practice. Much has been written about the problems with table level lookups. I won't say any more.

As for the Linked Table, you absolutely do not need to connect to the BE to set the Caption, or the Description for the matter. Additionally, the Table can be open, and you can still change the Caption or Description.

That is because it doesn't change the BE. That means that the definitions you see in the FE will always conflict with what is actually defined in the BE. Another poor practice. If your successor happens to open a table in DS view, he will see the Captions rather than the actual column names. PLUS -- if you have to relink the table, all your captions will disappear!!!!!! It is for reasons like this that professionals hate the novice "features" MS is prone to add to Access. They are not well thought out and will leave you hanging. Do NOT do this. Just because you can does not mean that you should.

Changing the caption in the BE is better but you are adding a caption which will save you save you a minor amount of time given the number of forms/reports we create per table but which will cause confusion for the life of the application and the names your successor will call you cannot be repeated in polite company.

MS thinks of Access as a development tool for dummies and that is how it is marketed. Therefore MS too often includes "features" that non-developers think are useful and love but which professional developers hate. You have latched onto two of them.

 

[spaLOGICng]

Poor practice. Much has been written about the problems with table level lookups. I won't say any more.

That is because it doesn't change the BE. That means that the definitions you see in the FE will always conflict with what is actually defined in the BE. Another poor practice. If your successor happens to open a table in DS view, he will see the Captions rather than the actual column names. PLUS -- if you have to relink the table, all your captions will disappear!!!!!! It is for reasons like this that professionals hate the novice "features" MS is prone to add to Access. They are not well thought out and will leave you hanging. Do NOT do this. Just because you can does not mean that you should.

Changing the caption in the BE is better but you are adding a caption which will save you save you a minor amount of time given the number of forms/reports we create per table but which will cause confusion for the life of the application and the names your successor will call you cannot be repeated in polite company.

MS thinks of Access as a development tool for dummies and that is how it is marketed. Therefore MS too often includes "features" that non-developers think are useful and love but which professional developers hate. You have latched onto two of them.

I am just providing clarification that certain properties of a TableDef can be altered in the FE.

As for Column Names versus Captions, there is absolutely no rule about how to name a Column version a Caption on a Column. In fact, a lot of secured applications have very generic names, like P1050... what the heck is P1050! None of your concern, that is what I am told, just give it a Column Caption as Product Vendor. To them, the P and the 10 and the 50 are significant. That information is in their Data Definition Library. A lot of Developers are not privy to that information. If a client asks me to engineer a database as obscure as possible, that is a method I would adopt.

As for lookups, I do not have a problem with them. When I am creating Forms, they are inherited automatically. It makes more sense to add them to the Table rather than the Form. If you have a Table that is sourced to MANY Forms, it just saves a lot of time in the development phase. so, it is just a matter of preference. As for problems, when I encounter them, I find a solution. But for the Lookups, I have never experienced a problem. Furthermore, after the development phases complete, there is nothing stopping a developer from going back an deleting them. As for, I handle all that programmatically. It is just as easy to delete a property as it is to create it.

I am way outside the box. My developing concepts and methodology are streamlined to me. I can ramp up a full-scale Application in fraction of the time it takes most. It works and I never once have had a complaint. My applications are built to empower an organization for years. They are designed in such a way that if they decide to scale up or sideways into other frameworks, they can do so with minimal effort. My coding style is minimalist. I utilize as much of built-in objects as possible before resorting to coding.

Once again, just because you have a coding preference, do not say it cannot be done if in fact it can be done. To Quote "... changes to tables MUST be made in the BE database. You cannot make changes to linked tables this way. You would need to define a link to the BE db rather than the CurrentDB." Explain that it can be done, but then give the caveats or impact of doing so may have.

Have a great day!

 

[Pat Hartman]

I can see that you don't need or want my help so I will stop wasting my time. You have a lovely day also

 

[isladogs]

You're right. I remember discovering that. I immediately forgot it since I would never do that What happens to other apps that link to the BE? The BE is not actually modified so do they see the captions? If they don't then this is a terrible practice.

I largely disagree with your views about this being poor practice.
Nor do I see this as a feature for dummies

Although I very rarely make use of this feature, it does have some value.
If captions are applied to field names in a table (whether local or linked), the field names are still displayed when creating other objects such as queries & forms & indeed would still be used when you create code based on that table.
However, when the query datasheet is viewed or the form is opened in form or datasheet view, the captions are shown.

In other words, the feature is internally consistent and there is absolutely no confusion to end users or developers.

Refreshing existing links has absolutely no effect on the captions. Obviously replacing the links would mean the captions are lost

If another database links to that table in the original source BE database, those captions are of course not seen as the BE structure was not changed in any way

However, if another database 'imports' the table from the FE database as a linked table, the captions are transferred with the linked table
In both cases, the behaviour is consistent throughout the database where the table is being used

As I've already stated, its a feature I rarely have any need to use myself.
However, I see no risk of confusion for either developers ...or end users, who will of course have no access to the tables or queries anyway.

 

[Pat Hartman]

In other words, the feature is internally consistent and there is absolutely no confusion to end users or developers.

Except that if you open the table in DS view, you see the captions and if you open a query to view it, you see the captions but if you open a query in VBA, you see the actual column names. While no one except the developer would ever open a table or query in DS view, as the developer, I certainly don't want to see the captions if I open the table or a query to browse the data, I want to see the real column names. Being a consultant, I work on way too many applications during the course of a year, many of which were not created by me, to have to try to remember dual names.

Here's one gotcha'. This is a picture of the results of Three queries. Followed by the SQL, Followed by the spreadsheet that got exported.

The only column with a caption is [Account Number]. The caption is AcctNumTestCaption.
Query1 and Query3 have an alias for [Account Number] which is AcctNumAlias. HOWEVER, neither query shows the Alias. All three show the Caption.

Now for the Tricky part. When you Export these three queries to Excel you get different results.
Query1 and Query3 both show the Alias (which is what we were hoping). But Query2 shows the Column Name rather than the Caption. Therefore, one of the primary uses for a Caption would be for exports, but that doesn't work requiring you to resort to using an Alias to send a "nice" name to Excel

So, to summarize, an Alias does NOT override the Caption when you view a query. Nor does it override the Caption when you use the wizard to make a form. However, the Alias WILL override the Caption when you export to Excel or Text.

And finally, do not lose sight of the really nasty gotcha' that happens when you have to relink tables and all your Captions and Descriptions that were so carefully entered on the linked table go bye-bye.

However, if another database 'imports' the table from the FE database as a linked table, the captions are transferred with the linked table
In both cases, the behaviour is consistent throughout the database where the table is being used

However, if another database just links to the BE, the Captions and Descriptions are not there so they won't be imported.

We're going to have to disagree on this one Colin.

I don't ever want to open a table in DS view and NOT see actual column names and actual data. This is one of the reasons I NEVER format anything at the table level. I don't want to obfuscate reality. I've been burned too many times by novices doing stupid things. Perhaps it is the worlds we operate in. I can only control what I build myself and the clients aren't paying me to refactor dumb design decisions except in the rare instances where I insist.

 

[isladogs]

We're going to have to disagree on this one Colin.

Yes indeed.
I'm aware of the issues when exporting to Excel.
In fact I wrote about this as another reason to avoid table level lookups in this article

Table Lookup Fields

Access allows you to use lookup fields at table level. Doing so, allows you to have combo boxes to guide end users with a list of available choices. However, experienced developers normally advise against their use. Arvin Meyer described several issues in his article: The Evils of Lookup Fields...

isladogs.co.uk

We do agree about that being poor practice!

 

[Pat Hartman]

So, you're OK with Access discarding all your Captions and Descriptions when you relink if you make the mistake of declaring them on the linked table?

 

[isladogs]

So, you're OK with Access discarding all your Captions and Descriptions when you relink if you make the mistake of declaring them on the linked table?

I'm not trying to persuade you to start using captions. You certainly aren't going to change your working practices at this point.
I am merely trying to point out that they do have uses rather than being poor practice as you originally stated very dismissively.

As I've said repeatedly, I don't often use field captions but that point has never been an issue for me in the 25+ years of using Access.

Anyway, I thought we'd agreed to disagree.

 

[ebs17]

I am just providing clarification that certain properties of a TableDef can be altered in the FE.

That's wrong.
A table is an object, namely an object of the database in which the real table is located. You can only access the real object table and change its properties via the reference to the database.

In the frontend, you cannot change properties of a backend table that is available as a link. What you can do is stick another label on top of a label, and then perhaps make people believe that the bottle of vinegar is actually a bottle of champagne.

I don't know how others work. When I create queries, I orientate myself on the database schema. Table names are there. Then when I switch from query definition (SQL view) to datasheet view, I just want to see the same field names as in the query definition. When my creativity and focus is occupied with creating a correct and functional query, I don't have the time, desire or understanding to first have to look up what a displayed field really is called.

"Only ruins can tell of war." Anyone who had to understand a foreign and somewhat more extensive database schema and had to create more complex queries can certainly understand this. Anyone who clicks together something Pippifax in the QBE will probably not have such problems.

 

[MarionD]

Thank you everyone for the conversation, ideas and lessons learned here.

I have abandoned the idea but have learnt a lot.

THank you all

 

[isladogs]

Thanks for the feedback. It was an interesting exchange of opinions!

 

[spaLOGICng]

That's wrong.
A table is an object, namely an object of the database in which the real table is located. You can only access the real object table and change its properties via the reference to the database.

In the frontend, you cannot change properties of a backend table that is available as a link. What you can do is stick another label on top of a label, and then perhaps make people believe that the bottle of vinegar is actually a bottle of champagne.

I don't know how others work. When I create queries, I orientate myself on the database schema. Table names are there. Then when I switch from query definition (SQL view) to datasheet view, I just want to see the same field names as in the query definition. When my creativity and focus is occupied with creating a correct and functional query, I don't have the time, desire or understanding to first have to look up what a displayed field really is called.

"Only ruins can tell of war." Anyone who had to understand a foreign and somewhat more extensive database schema and had to create more complex queries can certainly understand this. Anyone who clicks together something Pippifax in the QBE will probably not have such problems.

With all due respect, I do not need to be lectured. I am a person that thinks outside the box. I follow principles of development, but I have my own style. Notwithstanding...

A car can go 140mph, but because the speed limit is 70, you do not want me to tell the driver the car can go 140mph because that would be breaking the law if he did so you do not want him knowing the car can go 140mph.

I am only providing clarity. My statement above has been tested.

Certain properties of a Table Def can be updated in Runtime, even while the Table is open in a Recordset, or bound to an open Form or Report.

You are correct that what changes are made to a Table Def in a Front-end will not have any affect on the Back-end. However, the Back-end DB can be opened via VBA and the certain Table Def Properties be updated using the same method.

As for use case or practicality, it depends. As a developer that can spin out applications relatively quickly because of the custom development tools I have created for myself, I can see where I might want to use that method, but I do not. As for having some reason to do it in a runtime application, I currently do not see it, and I have been doing this for a long time, just like you.

So, while it may not be practical, I am not going to sit here and say something cannot be done, when it can be done.

I don't know what the OPs exact requirement is, he/she/they may have a perfectly good reason. I offered some potential reasons. For instance a developer is asked to link to an SQL Database that he does not have developer permissions for. He can only connect to it and go from in Access.

I know that the certain properties will be lost when the linked tables are refreshed. I build methods to cure those instances. For instance, I often connect to VIEWS, which are not updatable by default. You can create a Primary Key on the respective Column using VBA and a simple SQL statement. When the Table is refreshed from the Navigation Pane, the PK is lost. How do I handle that? I have a procedure that will re-create the PK is first opened. Secondly, I rarely ever expose the Navigation Pane in a runtime Application, and it will be disabled so that the User cannot press F11 to expose. Lastly, I have also changed certain other properties on a linked Table Def when developing, so that thise properties are inherited when I create Forms and Reports. I also add a developer DBA module to all of my projects that handle all of this. When I publish the master runtime application, the properties are removed from the TDF. That is perfectly fine with me, because they have already been seeded to the bound control on the Forms and Reports, i.e., row sources for combo and list boxes, their column widths, captions, descriptions, formats, masks. Validation, etc.

Disclaimer: I do not advise anyone to drive their car 140 Mph just because it can go that fast. But rest assured, I am going to find a safe area to test it out on occasion.

 

[Pat Hartman]

For instance, I often connect to VIEWS, which are not updatable by default.

Mine are???