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

Is there a way, when a xlsm workbook opens, to check the state of the primary indices?
If Yes, could that be done using VBA?
If Yes could you provideany examples?
tesla764Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
tesla764Author Commented:
Check that is in a healthy state.
First that it is does exist,
Second that it is not corrupt.
0
tesla764Author Commented:
Is there some sort of verification that can be done when a database is 1st opened?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
PatHartmanCommented:
If you are having trouble with a database, it might be because you have multiple people opening the same physical file.  To properly share an Access application, the database must be split into a BE which contains ONLY tables and a FE which contains everything else.  

The BE is placed on a network share and the FE links to the tables there.  The FE is distributed so that each individual user has his own personal copy located on his C: drive.  When you make changes to the FE, you distribute a new copy to each user.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
tesla764Author Commented:
Thanks that actually was a big help.
0
PatHartmanCommented:
@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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
PatHartmanCommented:
@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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
PatHartmanCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
PatHartmanCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
PatHartmanCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.