Change the Caption of a field in a tble

[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.

 

[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!

 

[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.

 

[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!

 

[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.

 

[isladogs]

Views are updateable in Access if a unique index is applied, either in e.g. SQL Server or when linked in Access.

 

[spaLOGICng]

Mine are???

Tables are, Views are not, not by default. The PK has to be set manually or programmatically against a linked table to a VIEW.

You have to seed the Primary Key on the TDF for the Linked View. The only way around this is to copy an existing Table that has a PK with a Column in the VIEW going by the same name. For example, ID Column. When you copy the Table, rename it as the VIEW Name, and then go into the External Data>Linked Table Manager and then relink the Table to the correct VIEW, the PK will not be deleted. The PK will not be deleted, But by default, the PK is not defined.

Alternatively, you can click your right mouse button on the Table in the Navigation Pan, select Refresh, you will next be prompted to select your PK Column from the available columns.

 

[spaLOGICng]

Views are updateable in Access if a unique index is applied, either in e.g. SQL Server or when linked in Access.

Yes, they are, but we should always start with a PK. But it has to be imlemented via VBA or by refreshing the Table,

Ex: CurrentDb.Execute "CREATE UNIQUE INDEX [PK_Name] ON [Tdf_Name] ([Column_Name]) WITH PRIMARY;"

 

[isladogs]

Yes, they are, but we should always start with a PK. But it has to be imlemented via VBA or by refreshing the Table,

Ex: CurrentDb.Execute "CREATE UNIQUE INDEX [PK_Name] ON [Tdf_Name] ([Column_Name]) WITH PRIMARY;"

Create SQL View Index

This article explains how to create an index on a SQL Server view in Access so that the data can be edited

www.isladogs.co.uk

 

[spaLOGICng]

Create SQL View Index

This article explains how to create an index on a SQL Server view in Access so that the data can be edited

www.isladogs.co.uk

I prefer to have a defined Primary Key before any standard index.

 

[isladogs]

I prefer to have a defined Primary Key before any standard index.

Perhaps you should have tried the code in my link before making your comment. It does create a PK on one or more fields.

 

[isladogs]

Perhaps you could explain the meaning of 'corporal' in relation to indexes in your first sentence.

This link may be helpful to the discussion
https://www.digitalenginesoftware.com/blog/archives/61-Indexes-on-Linked-TablesViews-in-Access.html

 

[isladogs]

A true index created on a view in SQL Server is recognised by Access.
A pseudo index created within Access is, as stated, only used within Access, so not recognised by the server.
I haven't seen any examples of a pseudo index corrupting data in SQL Server and indeed am unclear how that could happen.

Agree completely that the pseudo index is lost when links to views are broken and then the views are relinked. In such cases, indexes would need to be re-created. However, refreshing the link using the LTM does not destroy the pseudo index

 

[spaLOGICng]

Perhaps you should have tried the code in my link before making your comment. It does create a PK on one or more fields.

Perhaps not. Furthermore, the code in your link will only create a PK in a linked Table/View, not a local Table. The Object knows that a PK is necessary, and it is solving the other half of the riddle for you. I prefer to keep all my methods uniform. I create PKs for intended reasons, and I create Indexes for an intended reason.

I did not and do come here looking for arguments. There will always be more than one way to skin a cat. Some people prefer to skin it the ADO way. My only objective here 1) to ask questions for the experts in the event I am stumped (which is very seldom). 2) answer questions when members are stumped, and/or 3) help to correct or clarify an answer as needed, so long as I am able.

Lastly, you did not insert the link into any of your reply's until after I provided the snippet of my preference. I decided to only post a snippet sample of what I use. Very seldom do I ever provide full functions. I am just throwing water on the seed. How people use is it from there is entirely up to them. I could care less if they use my method or use your method, or any method they may find on the internet.

Have a great rest of your day!

 

[isladogs]

For someone who supposedly doesn't come here looking for arguments, your last reply seems to be trying hard to create one.

None of us here knows all the answers.
I was merely pointing out that the code in my article to create an index on a SQL view is creating a unique PK index, as you would have realised if you had tried it

 

[spaLOGICng]

For someone who supposedly doesn't come here looking for arguments, your last reply seems to be trying hard to create one.

None of us here knows all the answers.
I was merely pointing out that the code in my article to create an index on a SQL view is creating a unique PK index, as you would have realised if you had tried it

Not at all.

You told me that "Perhaps you should have tried the code in my link before making your comment." I was merely pointing out that you did not provide a link to your code until after I made my comment about the PK.

As I mentioned, your code does create the PK, but only for linked Tables/Views, not local tables. As for the code in your link, it has been around for more than 10 years. I did not need to try it again because I already knew the answer. The code in that snippet creates the PK for you, because the linked Table/View needs the PK before it can be updated. As I said, it is solving the riddle for you. But in reality, it is only a partially true method. If someone attempting to create a PK on a local table using that method, it would have failed, in the sense that it would have only created an Index on the Column(s) specified, not a PK. To me, the method is half-baked.

That said, why would I not use the code in your link? Because I have a Database Generator that begins in Excel, on worksheet, I define all the Tables, Columns, Properties, PK's Indexes, Validation Rules, etc., right there rather than using the respective design interface. I can create full databases a lot faster in this manner than I can using the designing interfaces. I have a switch that will create a script to generate the DB in SQL Server or Access. Because the method you are using does not create a PK on a Local Table, I prefer just to keep the CREATE INDEX scripts uniform, i.e., CREATE INDEX..., CREATE UNIQUE INDEX..., or CREATE UNIQUE INDEX... WITH PRIMARY. If I intend to create a PK, I will use the method that I have for either case. I am not going to mix my code just because there are other methods available.

As I said, we all have different ways to skin a cat. I have a reason why I use the method that I shared. If the method in the link works for you, that is fine. What remains is whether or not the OP was able to get past this hurdle.