Change the Caption of a field in a tble (1 Viewer)

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

 

[Pat Hartman]

The "index" that is created when the view is linked is not corporal. It is just information for Access to use when creating updates for the linked view. Just make sure that when you create the view, EVERY PK from EVERY table is included in the columns list so that Access can create a valid unique index definition.

 

[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

 

[Pat Hartman]

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

I am talking about the pseudo index that is created for Views that do NOT have an index defined so that Access knows what combination of fields defines a unique index. Access doesn't create indexes on the server and the server would never use an index created in Jet or ACE even if one should exist. Therefore, the "index" in this case is information only to be used by the Jet or ACE database engine so that it "knows" what the PK would be for this "table" should there be a physical one. If there is an actual index for the view, presumably, it would be recognized when the view is linked and the user would not be prompted for the index column names.

I should warn you all at this point to be very careful about defining this pseudo index for the view correctly. My understanding is that if you do it incorrectly, you might cause the SQL Server BE to be corrupted. Also, The pseudo index has to be recreated whenever the View is relinked so if you automatically refresh links that include a View, it is up to you to run a DDL query to recreate that pseudo index as part of the refresh link code.

 

[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

 

[Pat Hartman]

I haven't seen any examples of a pseudo index corrupting data in SQL Server and indeed am unclear how that could happen.

Then please feel free to ignore the warning. I don't know if it is SQL Server or Access that makes the requirement that only linked tables with PKs or Unique indexes can be updated. If it is Access, then I might be just a little bit worried about what would happen if I defined a unique index incorrectly and Access took it as gospel and then does something stupid because it believed me.

However, refreshing the link using the LTM does not destroy the pseudo index

That could be old information. I haven't done this in a while.

I create PKs for intended reasons, and I create Indexes for an intended reason.

As I pointed out earlier, the pseudo index created by the prompt when you link to a view that has no unique index defined on SQL Server does neither of these. It creates no physical index on either side of the link. It is merely a definition of what combination of columns will make a row of the view unique. You seem to still think that a view cannot be updated using Access. This is the method that makes it updateable if you do not have the permissions required to add an actual unique index server-side.

 

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

 

[spaLOGICng]

You seem to still think that a view cannot be updated using Access.

I never said that. I simply stated that a PK must be created on the linked Table (Access TDF), because in Access they are all called Tables even though we know the Link is to a View, before it can be update from Access,

I affirmed your statement that the PK is lost when the Table is refreshed. To cure that, I maintain a procedure to test and re-create the PKs when this happens.

But for the record, I use updateable VIEWS in Access all the time.

 

[Pat Hartman]

I never said that. I simply stated that a PK must be created on the linked Table (Access TDF),

It is a pseudo index. It is NOT physical.

I affirmed your statement that the PK is lost when the Table is refreshed.

Hmmmm. Colin said it isn't lost. Guess I'll have to check.

 

[spaLOGICng]

It is a pseudo index. It is NOT physical.

Hmmmm. Colin said it isn't lost. Guess I'll have to check.

Yes, any index on a linked table is a psuedo index. I don't think that is of concern here, but it is good to know that is how it is described.

As for losing the PK when the Table is refreshed, as I stated in my first comment on the topic, not when the Refresh method is used from the Popup menu on right mouse on the table. And now that I am testing other scenarios, the view that I am testing now is not losing it, but I know there are instances where it is lost. Apparently, we don't know the exact cause. I am using DSN-less connections. It is not doing it against my Server DB's, but it is doing it against one of my Client's DB several hundred miles away in St. Louis, with a DSN-less connection, and another in California. Interesting, Will investigate.

 

[Pat Hartman]

Once I figured out the index was being lost, I just used DDL to recreate it after the relink. I never tried to track down the reason. I just moved on. The first time i remember it happening was more than 20 years ago. I'm pretty sure it isn't getting fixed anytime soon.

 

[spaLOGICng]

Once I figured out the index was being lost, I just used DDL to recreate it after the relink. I never tried to track down the reason. I just moved on. The first time i remember it happening was more than 20 years ago. I'm pretty sure it isn't getting fixed anytime soon.

That is where I am at now. Just knowing that it can happen and having a solution in place is how I have proceeded with it as well. There are likely many other quirks that we will never know the exact reason for them.