Access 2000-Index Capacity-Reporting

[Earl Gipson]

Never thought I would be asking this question. I may be exceeding an Access
capacity because some Indexes have disappeared or lost their basis.
Compacting/Repair is done frequently.

Access 2000. Front end is growing and is connected/linked to a variety of
backend databases, sometimes on demand. What I need to know is the index count
and what happens if I exceed the Access capacity at any given time. There are
critical Indexes/relationships that MUST not malfunction. They can be rebuilt
but not without a significant down time. I wish to avoid this by
detecting/approaching an Index limitation.

What constitutes an embedded/permanent Index versus one that is transient and
does not affect Access 20000 specifications/limitations?

The backends (servers) will be migrating/transformed to SQL in a year or so, but
the front end must remain. How can I know the index count of the front end and
prevent malfunctions/errors if I exceed/approach the capacity of Access 2000?

EG

 

[Klatuu]

this statement:
Compacting/Repair is done frequently.

May point to your problem.
How frequently?
Are you talking about the front end, or the back ends?
If the back ends, how are you doing them?


If you need to know how many indexes each table has, you can use a routine
like this:

Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO. TableDef

set dbf = Currentdb
Set tdfs = dbf.TableDefs
For Each tdf In tdfs
debug.print tdf.Indexes.Count
Next tdf
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing

 

[John W. Vinson/MVP]

Never thought I would be asking this question. I may be exceeding an Access
capacity because some Indexes have disappeared or lost their basis.
Compacting/Repair is done frequently.

Access 2000. Front end is growing and is connected/linked to a variety of
backend databases, sometimes on demand. What I need to know is the index count
and what happens if I exceed the Access capacity at any given time. There are
critical Indexes/relationships that MUST not malfunction. They can be rebuilt
but not without a significant down time. I wish to avoid this by
detecting/approaching an Index limitation.

What constitutes an embedded/permanent Index versus one that is transient and
does not affect Access 20000 specifications/limitations?

The backends (servers) will be migrating/transformed to SQL in a year or so, but
the front end must remain. How can I know the index count of the front end and
prevent malfunctions/errors if I exceed/approach the capacity of Access 2000?

EG

Well, one thing to consider: Access indexes exist in the same database
as their table. There is no such thing as a "transient index" nor an
"index count in the frontend" since indexes either exist permanently
in the backend, or not at all.

If you exceed the limit of 32 indexes on a table, you'll get an error
message that you've exeeded the limit of 32 indexes on a table. I've
never seen any other symptom (but that doesn't mean there might not be
other kinds of errors of course!)

What specific symptoms are happening? What evidence do you have that
"indexes are disappearing"? What do you mean by "lost their basis"?

 

[Earl Gipson]

Dear Mr. Vinson,

Some individuals use the sub table datasheet (+) These will disappear and the
database asks whether I want to create one where I had created one to begin with
or it had previously existed.

On rare occasions, I have also had relationships disappear. I do not use
look-ups at all in the table design, since I know these cause future problems.

EG

 

[John W. Vinson/MVP]

Dear Mr. Vinson,

Some individuals use the sub table datasheet (+) These will disappear and the
database asks whether I want to create one where I had created one to begin with
or it had previously existed.

On rare occasions, I have also had relationships disappear. I do not use
look-ups at all in the table design, since I know these cause future problems.

Table subdatasheets have all the disadvantages of lookups and more
(they can be a MAJOR drag on performance). As a rule, it's best to not
let users see *any* sort of table datasheet at all, for exactly this
kind of reason; if you give them a full featured set of forms they'll
not miss them.

Yes, creating a subdatasheet will create a redundant index, if i
understand aright.

 

[Earl Gipson]

Thank you very much. Got of the subdatasheets. They have a full set of forms.
Inertia was the only thing that kept them in use. Just waiting for the moaning.

EG

 

[Earl Gipson]

Your code below showed some things I inherited and that no longer serve a
purpose. There were a couple of tables that had 30 indexes where only 1/2 were
needed/used.

Rescheduled Compact/Repair to when it exceeds a certain percentage of growth
once a week.

Between you and Mr. Vinson, got things back under control.

Thank you

EG