Indexing Access Tables for Best Performance - Tutorial

Sophisticated indexing of Access tables will boost your query performance and protect your data integrity.
This extensive tutorial explains the basics about indexes in Microsoft Access for beginners and shows some advanced features and techniques every Access developer should know.
Here is the link to the Jet Showplan setting for in-depth query execution plan analysis: www.techrepublic.com/article/...

[@josephpatrick5381]

Five years down the line. Still the video is comprehensive, meaningful and educative to a person newly exposed to MS Access.

[@codekabinettcomen]

Thank you very much. :)

[@MYJETGLO]

Fantastic tutorial...thank you so much!! I really enjoy your in depth videos, they really help to understand the logic of the subject you are presenting.

[@roseannarabia6461]

Thank you so much!! This is the best video I've watched on the topic and touched on everything I had questions about. You saved me so much time, I feel confident with what I'm doing now.

[@sheridiane5393]

I love your videos, both long and short. Some topics just don’t lend themselves to short videos, so I say go as long as the topic takes you.

[@mohammedalenazi9755]

Thank you for the valuable information. I watched this video from the middle east.

[@henrik5130]

Clear and straight to the point. Understandable even for a beginner like me. Good job! :-)

[@AaronOsler]

This video should have more thumbs up. Great work!

[@7NameNick]

A very helpful exclamation of a subject often missed. the long video is helpful as it builds knowledge in layers and is easy to grasp. Thanks for another good video,

[@kennethwinchester8648]

Good content and clear explanation.
Long detailed videos are good

[@sliceum]

Thanks for three video. Ii is very informative and really to the point. I really like the long video convention. Got all the neccessary information. Good job.

[@cedber2012]

Your tutorials are fantastic, thank you so much for putting so much effort into these videos. I value ALL the content of your videos, so the length is perfect in my opinion.

[@codekabinettcomen]

Thank you very much for your feedback!

[@DaveSemon]

Good video. Take as much time as necessary to cover the topic adequately. Thanks.

[@cujo3097]

this wideo was for me...

[@mafgeology]

Excellent 👍👍👍👍👌

[@torre6064]

I'm trying to test some of my own queries and indexes with this, but I don't know how to create the macro you've described!

[@codekabinettcomen]

I cannot make the databases I used in the video available for download, but the macro is super simple and just running a VBA function and passing the query name to it. I only used the macro to be able to run VBA code from the Access main window directly.
Here is the also very simple VBA function I used:
----------------------------------------------------------------------------
Public Function OpenQryPerformanceTest(ByVal queryName As String)
Dim startTime As Date
Dim executionTimeSecs As Long
startTime = Now
DoCmd.OpenQuery queryName
DoCmd.GoToRecord acDataQuery, queryName, acLast
executionTimeSecs = DateDiff("s", startTime, Now)
MsgBox "Execution time: " & executionTimeSecs & " seconds"
End Function

[@michaelp1856]

What if an index is set to not have duplicates, & later on duplicate data is appended to the table? What will happen - 1. error message, 2. it doesn't get appended, or 3. the previous data is deleted, or 4. something else?

[@codekabinettcomen]

If you try to insert duplicates into a unique index the duplicates will never be inserted.
If this causes an error message depends on the situation. If you use Database.Execute with dbFailOnError there will be an error. If you omit dbFailOnError or use DoCmd.RunSql it will fail silently without error message.

[@jasonfleishman9884]

I prefer long videos

[@colinhursell9176]

Long is better for me.