Check the state of indices in an access table when a workbook starts?

What do you mean by "check the state of the primary indices"?

To be clear: A Primary Index is the Index on a table that is identified as "Primary". It is essentially a unique, non-null index.

Check that is in a healthy state.
First that it is does exist,
Second that it is not corrupt.

Is there some sort of verification that can be done when a database is 1st opened?

I'm still not sure what you mean in regard to the xlsm workbook.

Access databases have indexes, but I don't know of a way to verify whether they're good, bad or otherwise. Indexes are recreated each time your compact the database, so if there is concern regarding the state of the indexes just compact the database.

Hi PatHartman,

While I agree with your method of using an Access database, I don't see how this is relevant to the topic at hand. Can you please explain?

Thanks that actually was a big help.

@LSMConsulting,
While the OP never said he was having a problem, why else would he be asking how to verify indexes?   The problems of multiple users simultaneously opening a shared monolithic app are well documented so that is where I started.

The solution doesn't really fit the question. Perhaps if the author could return to indicate how splitting the app into a FE/BE setup resolved their question about checking the state of indices, the solution might be more clear.

I'm well aware of the issue with shared apps, but I'm just not clear how that comment resolved the issue.

@LSMConsulting,

I'm well aware of the issue with shared apps, but I'm just not clear how that comment resolved the issue.

If the index problem was the result of corruption, splitting the app would have fixed it because the indexes would have been rebuilt.  A compact and repair would also probably have fixed the index but if the app was monolithic and opened simultaneously by multiple users, it would be only a matter of time before corruption occurred again.

If the index problem was the result of corruption,

My point is simply this: There was no mention of and index problem, or of corruption. The author asked how to verify the indexes of an Access table when an xlms worksheet is opened. That can't be done - at least as far as I know.

As I've stated all along, I'm well aware of the way compact/repair works, and I fully agree that a monolithic app should be split into FE and BE segments. That still doesn't explain how doing so would resolve the asker's question.

And please don't get me wrong. I don't care about the points (in fact, I think the question should be deleted), I just think the accepted comment doesn't resolve the question as stated by the asker. It may have resolved a different problem, but unless/until the author returns we have no way of knowing this.

Aren't you being a little pedantic?  I'm pretty new here but I've been answering Access questions in forums for 15 years and was an Access MVP for a while.  Sometimes you have to read between the lines.  The OP thinks he knows the solution (validate the indices) so asks how to implement that but never tells us what problem he is trying to solve.  Isn't the goal to solve problems and throw in a little education in the process?  To do that, sometimes we have to figure out what the real question is.  And in this case, I think the question is - why is my file not properly indexed?  If we put our blinders on and answer only the stated question, everyone looses.  Especially in this case since the answer is - you can't.  You can identify what indices are present but not if they are valid.

I'd also like to expand my answer.  I too was driven down the wrong path by the question (which was actually the OP's vision of the answer).  I think the question might have been, why is my recordset not in "key" order?  The answer to that question is different.  In a relational database, tables are unordered sets of data and so are queries.  The ONLY time you can predict the order of a recordset is if you include an Order By clause.  Access tends to fool us into thinking that recordsets are naturally in primary key order but that is not the case at all - EXCEPT - immediately after a compact.  Part of the compact and repair process copies every table in pk sequence and rewrites it that way in the compacted database.  But as soon as you start updating the table, records could be displaced.  This happens because table rows are variable length and are written end to end.  So if you modify a column and its new size is larger than the old size, Access can't physically put the record back where it was because it won't fit so it marks the old record with a pointer and puts the replacement in the nearest available free space.  So, if you compact frequently and don't do a lot of updating that would change the size of a particular record, you will think I'm making this up because you've never seen a record out of sequence when you opened the table in datasheet view - but I'm not.

You can identify what indices are present but not if they are valid.

Exactly my point - except that a Comact/Repair would fix this (as I suggested in my initial comment). Splitting the app into FE and BE would not necessarily do so, unless the user also performed a Compact at the same time.

I do agree that the goal is to get the user back up and running, and I'm certainly not disqualifying your contributions. I've seen you name on many other forums, and I have full respect for you and your knowledge of Access, and welcome your contributions here.

That still doesn't change the fact that someone who is searching for this same issue, and who came across this posting would not find the accepted answer to be a solution.

Splitting the app into FE and BE would not necessarily do so, unless the user also performed a Compact at the same time.

Splitting the database does effectively do a compact and repair as it creates the BE.  That's how compact works.  Access creates a new database and copies in all the old objects.  As part of the process for tables, it orders them into PK sequence and rebuilds the indexes.  You could prove this for yourself if you have a large enough table that is out of PK sequence.  You would see that in the new BE, the table was created in PK sequence.

I believe I said earlier that cr would also fix the problem.  But that is a band aid if the problem is being caused by corruption which is why splitting the database (which has the same effect as a cr on the data) is a more complete solution since it goes to the root of the problem and reduces the potential for future corruption.

Splitting the database does effectively do a compact and repair as it creates the BE.

As I mentioned earlier, I'm well aware of how CR works. Splitting the database might do that, depending on the methods used to create the BE. If you created a new database and imported all FE objects to that new one, then the BE database (the original one) would be in the same state as it was when you started.

thermoduric:

The question presented here was "how do I check the state of indices in an Access database". My comment was that you cannot, and that if you're concerned with those indices you should perform a C&R (http:#a39554119). That was ultimately the solution, and it directly addressed the question.

Splitting the database is definitely a good idea, but is not a solution and has no real bearing on the question as posted. It's sort of like advising someone to change the oil in their car when they complain about their front tires being out of alignment. It's a good idea, but it's not going to help those tires get back in alignment.

With ALL of that said - perhaps a better idea would be a split between my comment and PatHartman's:

http:#a39557015
http:#a39554119

Splitting the database does effectively do a CR.  Even though a CR may have fixed the immediate problem, it doesn't solve the long term problem which is a monolithic application.  Unsplit applications are prone to corruption and one CR is more of a band aid than a fix.  I believe that LSMComputing's insistence on answering the exact question and only that does not serve the general good.
Personally, I don't care what happens to the "points".  That isn't why I answer questions and I don't care what LSMComputing thinks of my answer.  I will continue to think outside the box and add value where I can.  What matters is what the OP thinks and he hasn't been back to this thread since LSM started the argument.