SSMA_TimeStamp field never gets updated

[spaLOGICng]

@spaLOGICng I don't see anywhere in SSMA that last option menu (the one with the pink highlights). What version are you using? Or does that image come from SSMS?

It is in SSMS. I included this because it can generate full Database Scripts, and when you select the Dependent Object option, the Objects will be iterated in the correct order based on hierarchical order of FK's.

I use this when the migration end point is an SQL Server DB. I never use SSMA just to generate a Script. It is too convoluted.

It is on the Advance Tab in the Generate Scripts Applet. Once you are in the List, you need to scroll down.

 

[cheekybuddha]

There have been a lot of recent updates to SSMA, but I have been using it exclusively for years and have never seen a missed index on a FK, so long as the Index was already defined in the Access Table, regardless if it was used in a FK.

Pat's point is that if you create an index on a FK field in Access, there will actually be 2 indices on that field - one automatically created by Access when creating the relationship.

The index created by Access will not be visible in the table's Indexes dialog.

I wonder whether the order in which you index the field and create the relationship plays a part here?

 

[spaLOGICng]

Pat's point is that if you create an index on a FK field in Access, there will actually be 2 indices on that field - one automatically created by Access when creating the relationship.

When I create Tables, I always use Number Fields to relate Tables. Very very seldom if ever will I use a Text Field for a FK. When I create a Number Field, it automatically indexes the column.

The Primary Key will also be indexed. I typically always use the Auto-number Type. I never have problems that others have talked about regarding the Auto-number (AN) columns. I use the AN Columns from my parent table and join to the Child Table. The Target child Column already has an index.

When I migrate these Tables to SQL Server using SSMA, all indexes are created with the PK's and the FK's. so I am not quite sure what the problem is.

 

[Pat Hartman]

@spaLOGICng you still are not understanding the issue. It has nothing to do with text indexes. If you actually want to understand the issue, follow my directions and then run the documentation to see the dup index. Your last example creates the dup index. You can only see it if you bother to follow the directions.

 

[Pat Hartman]

@GPGeorge if you look at the doc report, you only see a single index for each fk. Now create a new FK index on orders. Then run the rot a second time. You. Should see the hidden index.

 

[GPGeorge]

@GPGeorge if you look at the doc report, you only see a single index for each fk. Now create a new to on orders. Then run the rot a second time. You. Should see the hidden index.

I followed your instructions on your sample accdb that you provided. I finally learned what you had been trying to explain. We need to follow up again with a detailed report on the problem for Microsoft. Unfortunately, the Access team doesn't own the SSMA and I don't know how much interaction there is with the team that does.

 

[spaLOGICng]

@spaLOGICng you still are not understanding the issue. It has nothing to do with text indexes. If you actually want to understand the issue, follow my directions and then run the documentation to see the dup index. Your last example creates the dup index. You can only see it if you bother to follow the directions.

I am not the OP, I am a commentor and this is kind of like a sub-thread.

Someone referred to SSMA not creating "HIDDEN" Indexes. I did ask about Sql Serve "Full-Text Indexes" because I really did not know what they were, and I think that has already been answered. Not sure why it never registered in my brain, thought it was actually something else a bit obscure. However, I have been in this field for several decades and never heard about HIDDEN Indexes, so I inquired further. I was responding to @cheekybuddha where their comment was:

"Pat's point is that if you create an index on a FK field in Access, there will actually be 2 indices on that field - one automatically created by Access when creating the relationship.

The index created by Access will not be visible in the table's Indexes dialog." Which at this point in the conversation is several comments and responses deep and is hard to follow because they do not become nested under the comment they are in response to.

I have never seen Access create a Hidden Index, I guess because it is hidden. But I have never known Access to ever create an Index on a Field when it became an FK if it was not already indexed. To the best of my knowledge, Access does not and neither does SQL Server. My test responses above and this one below negate the theory that Access or SQL Server will create "HIDDEN" indexes.

However, to respond to your comment that it created a Duplicate Indexes, yes. IN most cases they are unnecessary. As for Access, I am not sure that setting a Column as PK includes the INDEX but only the UNIQUE-ness of it, and therefore automatically creates the Index. I am guessing here. I am not sure why it does that. I can see where it could be useful where for a Query you may need to INCLUDE other Columns in an INDEX that has the PK as the Indexed Column for performance. Otherwise, it is unnecessary, confusing, and a waste of resources. My only purpose was to argue the "HIDDEN" Index comment above, and to this day I still do not know what it is. I have read that MongoDB can have a HIDDEN Index, but I have never ever heard of such a thing with Access or SQL Server.

Now I am going to locate your directions and follow them as George has does to see if I can replicate the hidden index.


In this case, the OP created an FK on Surgeries from People. The PersonID in the Surgeries Table is not Indexed.

As seen here:

I then migrated the DB to SQL Server using SSMA, and the Tables were created exactly as they were in Access

What is baffling to me, when the OP created the Column PersonID or any other Field with ",,,ID" at the end, it should have automatically indexed. This is the default setting for Access when installed. Unless otherwise changed, and Index should be created automatically. This is why I went through the exercise yesterday create two basic Tables, with basic columns, created the FK and migrated them to SQL Server using SSMA, to show that the indexes are created, both automatically and during the migration to SQL Server.

 

[GPGeorge]

Whether or not the user manually adds an index on the field designated as a foreign key, Access will create an index of its own on that Foreign Key field. I was not aware of that, and argued with Pat over it until I finally tried it out for myself (advice which I have freely given to others over the years, by the way, to my own embarrassment here).

You brought up another good point in noting that the default setting is for Access to automatically add indexes for a list of field names, such as "ID". That's an additional complication here, and one that needs to be accounted for as well. I always remove that option because I prefer to control indexes. Unbeknownst to me, though, there was this additional, hidden, index being created anyway.

 

[spaLOGICng]

To get Access to add a hidden index, just create two tables. Then create a relationship and enforce RI. do NOT put an index on the FK. If you look at the indexes dialog, you will only see the PK for the child table. However, if you look at the indexes using code or the built in documenter report, you see it.

Design view = Only the PK index.
View attachment 114070
Documentation report shows the second index on Person which I did not create and which does not show on the field or in the indexes dialog.
View attachment 114069

Okay, I am going to follow these steps. but in a nutshell, when I run the DB documenter Report, this is baffling. I do not think it is a REAL index. I think it was an assumption by the person that developed this report for Access.



I created two ID Fields in Table Two. The first is intended to the be the FK. What baffled me is that the Index was not created automatically based on my Settings

it should have created the Index. I therefore created a second Column omitting the "_" (underscore) and it did create the Index as it should have.

I created the FK on the non-Indexed Column:

Access did NOT create an Index on the non-Indexed Column when I create the RI FK:

There is NO index visible in the the Index Dialog related to the FK Column:

And, SSMA did not create an index on the FK when migrated to SQL Server:

But when I run the DB documenter Report, this is baffling. I do not think it is a REAL index. I think it was an assumption by the person that developed this report for Access.

 

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

 

[Pat Hartman]

I have never seen Access create a Hidden Index, I guess because it is hidden. But I have never known Access to ever create an Index on a Field when it became an FK if it was not already indexed. To the best of my knowledge, Access does not and neither does SQL Server. My test responses above and this one below negate the theory that Access or SQL Server will create "HIDDEN" indexes.

If you follow my directions, you will see the hidden index. Just saying it isn't there doesn't make it so.

I then migrated the DB to SQL Server using SSMA, and the Tables were created exactly as they were in Access

But you never looked at the documentation report so you do not know if the hidden index exists.

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

The Documenter report is the EASY way to see the issue. Are you saying that VBA and DAO are broken also???? Because if you use VBA to list the indexes, the hidden index is there. So not only does the documenter see it but so does VBA.

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.

So, you don't believe the VBA either?

 

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

 

[Pat Hartman]

The original programming was performed in Macros, which we all have become so accustomed to hate.

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.

If you prefer to call it a virtual index, rather than a hidden index, I'm fine with that.

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.

How does SSMA designate Indexes created on Foreign Key fields by default?

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.

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

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.

 

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