SSMA_TimeStamp field never gets updated

[GPGeorge]

It's HIDDEN. That's why your screenshot of the Index property sheet does not show it. That is, in fact, the whole basis of this entire discussion.

Access creates an index, automatically, on fields defined as Foreign Keys. These indexes do not appear in the Index Property Sheet. I.e. "hidden".

Pat provided VBA to reveal it. See her screenshot of the Analyzer report, as well.

Run this, please, on your sample accdb.

Public Sub ListIndexes()
Const adSchemaIndexes As Long = 12
Dim cn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim i As Long


Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblBloodPressure"))
With rs
    ' enable next three lines to view all the recordset column names
'    For i = 0 To (.Fields.Count - 1)
'        Debug.Print .Fields(i).Name
'    Next i
    Do While Not .EOF
       Debug.Print !TABLE_NAME, !INDEX_NAME, "Is Primary Key " & !PRIMARY_KEY
       .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub

 

[spaLOGICng]

It's HIDDEN. That's why your screenshot of the Index property sheet does not show it. That is, in fact, the whole basis of this entire discussion.

Access creates an index, automatically, on fields defined as Foreign Keys. These indexes do not appear in the Index Property Sheet. I.e. "hidden".

Pat provided VBA to reveal it. See her screenshot of the Analyzer report, as well.

Run this, please, on your sample accdb.

Public Sub ListIndexes()
Const adSchemaIndexes As Long = 12
Dim cn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim i As Long


Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblBloodPressure"))
With rs
    ' enable next three lines to view all the recordset column names
'    For i = 0 To (.Fields.Count - 1)
'        Debug.Print .Fields(i).Name
'    Next i
    Do While Not .EOF
       Debug.Print !TABLE_NAME, !INDEX_NAME, "Is Primary Key " & !PRIMARY_KEY
       .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub

I ran your Script George, and I ran one that I have to list All Index, neither show the FK Column as an Indexed Field.

I truly think it is a glitch with the Documenter/Analyzer Report and not with Access as a DB itself.

Pardon my scribbles.

 

[GPGeorge]

Your screenshot of the Index Property sheet for Table2, in Post #49, shows 3 visible indexes.

Your screenshot of the VBA output, in Post #51, shows 4 indexes for Table2, in both the VBA Pat provided and in yours.

In other words, both ADO and the Analyzer report think there are 4 indexes on your Table2, not the 3 shown in Post #49.

That is why I became convinced this is a real thing, that the index exists, and that it is hidden from the design interface for some reason.

 

[spaLOGICng]

Your screenshot of the Index Property sheet for Table2, in Post #49, shows 3 visible indexes.

Your screenshot of the VBA output, in Post #51, shows 4 indexes for Table2, in both the VBA Pat provided and in yours.

In other words, both ADO and the Analyzer report think there are 4 indexes on your Table2, not the 3 shown in Post #49.

That is why I became convinced this is a real thing, that the index exists, and that it is hidden from the design interface for some reason.

I believe you are replying to me...

As I said, I believe the issue is the Analyzer Report. There is no such thing as a hidden index. I cannot find any documentation anywhere on the topic.

I believe the developer of the report made some assumptions when creating it, assuming that all FK columns were indexed. Why would that person assume such? Because it is a rule of thumb that a column in a FK should be indexed.

So I truly believe this is a Report Issue and not a DB Index Issue.

 

[GPGeorge]

I believe you are replying to me...

As I said, I believe the issue is the Analyzer Report. There is no such thing as a hidden index. I cannot find any documentation anywhere on the topic.

I believe the developer of the report made some assumptions when creating it, assuming that all FK columns were indexed. Why would that person assume such? Because it is a rule of thumb that a column in a FK should be indexed.

So I truly believe this is a Report Issue and not a DB Index Issue.

Your own screenshot of the VBA reveals the 4th Index. It is not visible in the property sheet. If you don't like the term "hidden", fine. Come up with another term that describes the fact that the Index property sheet does not display the index which ADO finds.

To be honest, my initial reaction was the same as yours. Having seen the output of the VBA procedure, I was forced to accept that I can be wrong, and was wrong in this case.

 

[spaLOGICng]

Your own screenshot of the VBA reveals the 4th Index. It is not visible in the property sheet. If you don't like the term "hidden", fine. Come up with another term that describes the fact that the Index property sheet does not display the index which ADO finds.

To be honest, my initial reaction was the same as yours. Having seen the output of the VBA procedure, I was forced to accept that I can be wrong, and was wrong in this case.

Are you referring to the purple line references here? It actually does not list the Field name. Apart from that, the FK Column Name is "Tbl1_ID", not TestID.

Once again, and for the record, Access will be default automatically create indexes for columns that begin with or end with certain words. However, if you use an Underscore between the words, it will not work.

I do need to concede to some degree, only because I cannot put my finger on the Root Cause. I absolutely know for a fact there is NO documentation on HIDDEN Indexes.

However, to isolate the Tables out of the Application the OP shared to this thread, I created a new empty Access Application and imported the two Tables into the new DB. Access created the Index on the FK Column.

This is truly baffling. I wish I could read the Logic in the behind the Import Wizard.

I am leaning to a Virtual Index. When I look at the Indexes in the original Table2 that I created, it only shows three indexes...

And when I view the Index on the new DB that I imported the Tables into, I see four Indexes.

So maybe Access is half-baked in some regards.

I believe that the term "Hidden Index" is the name @Pat Hartman gave to it for the unknown. It is definitely an issue, and there has to be a logical reason for this. The Reports, even VBA came after the fact. The original Foundation for Access as Database was born way before any of those features were implemented. The original programming was performed in Macros, which we all have become so accustomed to hate.

I have a hard time though believing it is a real index. I believe certain developers of the application has made some assumptions that FK's are always indexed. The Fact that the VBA produced the alias "Table1Table2" rather than "Tbl1_ID" is a clear indication that the index does not exist. It is NULL. But it only makes sense to correct the issue.

We also know that Access is much more forgiving to RDBMS principles of development and make some assumptions for common mistakes, like they way it handles dates, or even nulls for the matter, as compared to SQL Server which is very rigid and makes no assumptions.

I trust the developers of SSMA, that if the index TRULY existed, it would have been created on migration.

The Upsizing Wizard was referred to earlier, but that is also a product of Access Development Team, not the SQL Server Development Team.

Therefore, I just feel that the Access Development Team has made some helpful changes to correct development errors in an RDBMS. I do think this question is worthy of presenting to the Office Insider Group.

In the meantime, I recommend using the naming standards as listed in the Auto-index option, or even add to it, based on your desired naming conventions. I always use a Number Type for FK's and always end them with ID. That solves the hidden index riddle enough for me.

 

[GPGeorge]

If you prefer to call it a virtual index, rather than a hidden index, I'm fine with that. It is there, whether it is "virtual" or "hidden".

ADO identifies it in the table schema in Access.

I do not trust the makers of SSMA as much as you do. It has come a long way since I first saw it demonstrated at an MVP Summit in Redmond a dozen years or so ago, but it is not entirely a finished product.

"The Fact that the VBA produced the alias "Table1Table2" rather than "Tbl1_ID" is a clear indication that the index does not exist. "

How can a name tell you that the index it represents does not exist? I think that naming convention actually reflects the fact that this index is on a Foreign Key field for the relationship between Table1 and Table2. Give me a minute, though, to modify the sub Pat shared.

How does SSMA designate Indexes created on Foreign Key fields by default? What naming convention does it use? Similar to this or entirely different?

 

[GPGeorge]

If you prefer to call it a virtual index, rather than a hidden index, I'm fine with that. It is there, whether it is "virtual" or "hidden".

ADO identifies it in the table schema in Access.

I do not trust the makers of SSMA as much as you do. It has come a long way since I first saw it demonstrated at an MVP Summit in Redmond a dozen years or so ago, but it is not entirely a finished product.

"The Fact that the VBA produced the alias "Table1Table2" rather than "Tbl1_ID" is a clear indication that the index does not exist. "

How can a name tell you that the index it represents does not exist? I think that naming convention actually reflects the fact that this index is on a Foreign Key field for the relationship between Table1 and Table2. Give me a minute, though, to modify the sub Pat shared.

How does SSMA designate Indexes created on Foreign Key fields by default? What naming convention does it use? Similar to this or entirely different?

And that is pretty much all I need to convince me that there is an index there. Access created it when the Foreign Key constraint was created, and its name reflects that aspect if its origin. Call it virtual, call it hidden, or call it an unexplained phenomenon. It's there.

If SSMA doesn't recognize it and migrate it, that could be an oversight or a design decision.

 

[GPGeorge]

Who told you that? I think VBA was added in A95. Earlier versions used a different version of basic, not macros. Similar and mostly convertible, but different.

I am not OK with that designation. There are actual virtual indexes that are created when you link to server-side tables or views that do not have a PK or unique index defined. These virtual indexes do not in fact exist because indexes exist only in the database that holds the table the index applies to. The only use of the virtual index is for Access to be sure that you end up with an updateable result once the action query is sent to the server.

It ignores them. That is what we are talking about. Access creates them as table1table2. SSMA does not create any indexes on FKs automatically, nor does SSMS.

The optimal solution is to determine if there are duplicate indexes and only migrate one. That covers the people who know about the hidden indexes as well as the people who unknowingly create a duplicate index because they didn't know that Access had already created one when the relationship was defined.

or, Add an option so that the developer can choose.

or, if Microsoft intended to ignore the hidden indexes, OK - but at least warn us.

I think what SSMS does is employ the same syntax for such indexes as the naming illustrated in your example. That was what I was driving at, not that it creates the index, but that the two-table-name syntax was similar.

 

[GPGeorge]

Who told you that? I think VBA was added in A95. Earlier versions used a different version of basic, not macros. Similar and mostly convertible, but different.

I am not OK with that designation. There are actual virtual indexes that are created when you link to server-side tables or views that do not have a PK or unique index defined. These virtual indexes do not in fact exist because indexes exist only in the database that holds the table the index applies to. The only use of the virtual index is for Access to be sure that you end up with an updateable result once the action query is sent to the server.

It ignores them. That is what we are talking about. Access creates them as table1table2. SSMA does not create any indexes on FKs automatically, nor does SSMS.

The optimal solution is to determine if there are duplicate indexes and only migrate one. That covers the people who know about the hidden indexes as well as the people who unknowingly create a duplicate index because they didn't know that Access had already created one when the relationship was defined.

or, Add an option so that the developer can choose.

or, if Microsoft intended to ignore the hidden indexes, OK - but at least warn us.

Okay. Virtual indexes is not such a good choice then. However, I'm fine with calling them something other than "hidden" as long as the actual concept and reality of them is recognized. And, of course, that they are accounted for.

 

[GPGeorge]

You're right. When SSMA converts indexes defined by Jet/ACE, it changes the names to make them unique and so includes the table and sometimes column names in them.

I've seen similar names generated in SSMS when creating indexes as well. Sorry I was not clear that I was referring only to the naming convention.

Did you see my post to the Non-NDA email list? Would you like to augment it?

 

[spaLOGICng]

View attachment 114128

And that is pretty much all I need to convince me that there is an index there. Access created it when the Foreign Key constraint was created, and its name reflects that aspect if its origin. Call it virtual, call it hidden, or call it an unexplained phenomenon. It's there.

If SSMA doesn't recognize it and migrate it, that could be an oversight or a design decision.

By default, Access will create an Index on any begins or ends with any of the names in the auto-index box as seen here.

As to why they were not created automatically in the OP's database, I am not sure, unless the tag words were changed or removed. It should have automatically created indexes for the PersonID Column when it was added.

It also explains why Indexes were created on the Test DB when I imported the Table1 and Table2. i.e., "AutoIndex on Import/Create". I am therefore left with the impression that the OP removed the Index. I have never had this fail on me.

Anyway, this is just all new to me. I just never had the issue appear for me to ever question it.

When I ask CoPilot the question, this is what I get (see #2):

Microsoft Copilot: Your AI companion

Microsoft Copilot is your companion to inform, entertain and inspire. Get advice, feedback and straightforward answers. Try Copilot now.

sl.bing.net

In Access VBA, when working with ADO (ActiveX Data Objects), you might encounter situations where it appears that a virtual index is created on a foreign key (FK) column even though there is no visible index. Let’s explore this further:

1. Foreign Keys and Indexes:
   • A foreign key is a constraint that establishes a relationship between two tables. It ensures that values in a column (the child table) correspond to values in another column (the parent table).
   • Contrary to popular belief, a foreign key itself does not automatically create an index. It is a separate concept from indexes.
   • However, it makes sense to index all columns involved in foreign key relationships. Indexing these columns can improve query performance when enforcing referential integrity.
2. Why FK Columns May Appear Indexed:
   • When you create a foreign key relationship in Access, the underlying database engine (such as Jet or ACE) may implicitly create an index on the FK column.
   • This index is not always visible in the Access user interface, but it exists to optimize query execution related to referential integrity checks.
   • The index allows the database engine to efficiently validate that no other rows reference the row being modified or deleted.
3. Seek Method and Index Property:
   • In ADO, the Index property is used in conjunction with the Seek method. However, not all providers support this property.
   • If the underlying provider does not support the Index property, consider using the Find method instead.
   • To determine whether a Recordset object supports indexes, you can use the Supports(adIndex) method1.
4. Indexed Property in Access:
   • In Microsoft Access, when you add a single-field index in the Indexes window, Access automatically sets the Indexed property for the field to “Yes.”
   • In Visual Basic, you can use the ADO Append method of the Indexes collection to create an index for a field2.

In summary, while foreign keys themselves do not automatically create indexes, it’s good practice to index the columns involved in FK relationships. This can enhance query performance and ensure referential integrity. If you encounter unexpected behavior related to virtual indexes, consider checking the underlying database engine’s behavior and any hidden indexes that may exist.

 

[GPGeorge]

You're getting there. It took me a while to come around on this one.

I always remove the autoindex code words because they can create redundant indexes. That's why they do not appear in the sample database Pat provided as well.

Point #2 -- yes, "implicit" and almost always "hidden".


I just uploaded a video explaining how I learned about hidden indexes on tables in Access, why they are there, and why it might be important. It isn't as exhaustive as your investigation, but does highlight the issue.

 

[spaLOGICng]

Anyone who knows anything about relational databases has already turned off this "feature" by removing the list of suffixes so no indexes are automatically generated. It was there to help people who didn't know enough to index columns used for searching.

This has nothing whatsoever to do with the discussion regarding why SSMA ignores the hidden indexes created by Access when you create a relationship between two tables.

I was beginning to like you Pat.

My apology for going off on a tangent but I am not the one that brought up SSMA or Hidden Indexes to begin with. Sometimes I need to dispute things in a way where I will be corrected. Notwithstanding, I was merely showing the Auto-index feature as part of my RCA, because I had never experienced the issue you all were discussing, and I need to understand why. There are some people that appreciate my attention to detail and there are others that feel it is a waste of time.

Personally, I find the Auto-index feature EXTREMELY convenient. That is just my preference. We all have our own preferences, it just happens to not be one of yours, and it certainly does not have any bearing on what one knows or does not know about an RDBMS. I have never had a complaint yet in my 35 years of experience. I do not claim to know it all. In fact, I would say that 90% of over the last 10 years, I had very little involvement in Local Access Table Development. It has mostly been SQL Server and Azure SQL DB. I have migrated close 80 medium to large Access and SQL Databases to Azure SQL DB over the past three years. Never once have I had an issue. but I do analyze all JOINS and INDEXES and where missing I do add them. I never once suspected a fluke with Access.

In closing, I do thank You and George for forcing me to investigate the how what and why of a Hidden Index, which we have learning is more appropriately known as an "implicitly created index".

Thank you for the Video George!

Have a great evening!

 

[GPGeorge]

There are further developments forthcoming. I'll wait for Pat to weigh in. However, this screenshot is relevant to that further discussion. The point is that the hidden index is different from ordinary indexes. It's created, as we had surmised, as part of the relationship involving the field.

I'm not sure that a corollary exists for SQL Server indexes.

 

[cheekybuddha]

This conversation should have ended long ago.

Amen!

 

[AccessBlaster]

While boring for "experts" I find these debates educational, most of us don't wade this far into the weeds.