SSMA_TimeStamp field never gets updated

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

 

[Pat Hartman]

Sorry to be so persnickety but there have been so many red-herrings thrown into this discussion that are totally irrelevant, I couldn't let this pass.

 

[Pat Hartman]

That was what I was driving at, not that it creates the index, but that the two-table-name syntax was similar.

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.

 

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

 

[Pat Hartman]

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.

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.

 

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

 

[Pat Hartman]

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.

I'm pretty sure you have experienced it, you just have not actually looked at any of your own applications in the way I described so you could see it. That is very frustrating. This conversation should have ended long ago.

 

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

 

[Pat Hartman]

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

Did it peak your interest sufficiently to look at one of your existing BE's and see if you had duplicate indexes for your FK's? If you didn't know already that Access automagically creates an index on each FK when you create a relationship, you may have lots of duplicates.