Index Linked Table

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

We have a system that each night exports several tables of data to Access 97
format.

We use Access 2000 and 2002 and have several programs built that link to
those exported tables.

The exported tables basically get re-created each night when that batch
system re-exports current data.

I'd like to be able to have the varios Access 2000 and 2002 programs link to
those tables, but with various indexes or build indexes into the code.

For example, program #1 links to a table called PARTS. As exported from the
overnight application the table PARTS has an index pre-set on the ITEM
field. This is fine for program #1's functions so great! :)

Program #2 links to the same PARTS table, but a more appropriate index for
this program would be on the NAME field.

There are several other programs that link to the same table, but would have
different index field needs.

All of these programs are run and used by many users throughout a day then.

Is there anyway to define different index fields for these linked tables on
a per use basis?

I want to do this because if I can define the indexes of the various tables
in the various programs, then I can get the programs to run much faster.

There are roughly 100 exported tables that the programs get their data from
and each export table has anywhere from 5 field to 150 fields and anywhere
from a few hundred records to a few hundred thousand records. The various
programs link to several tables and combine, calculate, etc to provide
various outputs.

Any way to set indexes on link tables without affecting other items that
might link to the same table?

thanks
 
You can add indices onto tables programatically, but there's an easier way -
I'm just not sure if it's going to meet all your requirements.

- add the indices that you want onto one of the existing tables
- instead of completely recreating the tables, run a delete query to delete
all the old rows, and then an append query to add the new ones.
 
Thanks for the info. Unfortunately, I can't control how the existing tables
are built - its a built-in function of the software that exports them and we
don't have access to that software beyond running it.

I was not successful building the index programmatically either - since the
linked table is in use in other programs and by other users, when I try to
create the index in vb, I get a message that the table cannot be locked to
allow the index. I'm using the CreateIndex method to do this. Is there a
different way to do it programmatically that creates the index within that
particular app for just that run-time and doesn't need to lock the table?
 
Hmmm. Doesn't sound good. Do you know what is locking the tables? Is it a
service that the 3rd party software is running? I've never tried it with
Access but I'd guess that it should be possible to stop the service(s), add
your indices programatically, the restart service(s).
 
Is there a
different way to do it programmatically that creates the index within that
particular app for just that run-time and doesn't need to lock the table?
Click to expand...

No. The index must be in the same database as the table, and they are
tightly linked. If you don't have exclusive access to the table, then
you cannot index the table.

John W. Vinson[MVP]
 
Back
Top