access97, more than 32 indexes on a table, time to move to MSDE / sql

[lesperancer]

you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come

does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?

does access2000 / 2003 allow more indexes per table ?

is now the time to move to msde or sql / server ?
if so,
I've seen reference to an 'migration' wizard, but I don't see in
access97, is this wizard a sql
server tool ?

and what other migration (access -sql) issues should I be aware of
?

[Smartin]

le*********@nat pro.com wrote:

you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come

does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?

does access2000 / 2003 allow more indexes per table ?

is now the time to move to msde or sql / server ?
if so,
I've seen reference to an 'migration' wizard, but I don't see in
access97, is this wizard a sql
server tool ?

and what other migration (access -sql) issues should I be aware of
?

I just have to wonder out loud, how does one get to the point of needing
32+ indexes on a table? Are you sure your stuff is normalized?

--
Smartin

[Rick Brandt]

le*********@nat pro.com wrote:

you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come

does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?

does access2000 / 2003 allow more indexes per table ?

is now the time to move to msde or sql / server ?
if so,
I've seen reference to an 'migration' wizard, but I don't see in
access97, is this wizard a sql
server tool ?

and what other migration (access -sql) issues should I be aware of
?

Since even having 32 *fields* is pretty unusual I fail to see why anyone would
ever hit 32 indexes.

Even if you change engines to one with a higher limit what will you use for an
interface? Access cannot link to tables with more than 32 indexes either.

I stongly suspect you have indexes you don't need. Are you using lookup fields?
Those add hidden indexes as do relationships. Not knowing about these might
have caused you to create non-hidden indexes that would be redundant copies of
the hidden ones. If so, then the non-hidden ones could be eliminated.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

"Rick Brandt" <ri*********@ho tmail.comwrote in
news:%B******** *******@newssvr 29.news.prodigy .net:

I stongly suspect you have indexes you don't need. Are you using
lookup fields? Those add hidden indexes as do relationships. Not
knowing about these might have caused you to create non-hidden
indexes that would be redundant copies of the hidden ones. If so,
then the non-hidden ones could be eliminated.

There's that and then there's denormalization , which I more strongly
suspect as the source of extra indexes.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[lesperancer]

the table itself has 9 non-hidden indexes that are legit for use in
WHERE clauses
the other 23 hidden indexes are as a result of foreign keys to other
tables in order to have relational integrity

if you have a table that stores inventory items (tblItem), and the
itemId is used in
quotes, sales orders, purchases orders, adjustments, invoices, etc it's
easy to create 23 hidden indexes

the fact that Access can't link to a sql table with +32 indexes is
interesting too
I'll have to look at my design again

thanks for the input
Rick Brandt wrote:

le*********@nat pro.com wrote:

you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come

does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?

does access2000 / 2003 allow more indexes per table ?

is now the time to move to msde or sql / server ?
if so,
I've seen reference to an 'migration' wizard, but I don't see in
access97, is this wizard a sql
server tool ?

and what other migration (access -sql) issues should I be aware of
?

Since even having 32 *fields* is pretty unusual I fail to see why anyone would
ever hit 32 indexes.

Even if you change engines to one with a higher limit what will you use for an
interface? Access cannot link to tables with more than 32 indexes either.

I stongly suspect you have indexes you don't need. Are you using lookup fields?
Those add hidden indexes as do relationships. Not knowing about these might
have caused you to create non-hidden indexes that would be redundant copies of
the hidden ones. If so, then the non-hidden ones could be eliminated.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lesperancer]

re..

Even if you change engines to one with a higher limit what will you use for an
interface? Access cannot link to tables with more than 32 indexes either.

I just created a table in sql server 7, and I put 33 indexes on the
same field
and I was able to link it in access97, and then added data to the
table.. so it looks like access will link a table with many indexes

Rick Brandt wrote:

le*********@nat pro.com wrote:

you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come

does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?

does access2000 / 2003 allow more indexes per table ?

is now the time to move to msde or sql / server ?
if so,
I've seen reference to an 'migration' wizard, but I don't see in
access97, is this wizard a sql
server tool ?

and what other migration (access -sql) issues should I be aware of
?

Since even having 32 *fields* is pretty unusual I fail to see why anyone would
ever hit 32 indexes.

Even if you change engines to one with a higher limit what will you use for an
interface? Access cannot link to tables with more than 32 indexes either.

I stongly suspect you have indexes you don't need. Are you using lookup fields?
Those add hidden indexes as do relationships. Not knowing about these might
have caused you to create non-hidden indexes that would be redundant copies of
the hidden ones. If so, then the non-hidden ones could be eliminated.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[rquintal]

le*********@nat pro.com wrote:

the table itself has 9 non-hidden indexes that are legit for use in
WHERE clauses
the other 23 hidden indexes are as a result of foreign keys to other
tables in order to have relational integrity

if you have a table that stores inventory items (tblItem), and the
itemId is used in
quotes, sales orders, purchases orders, adjustments, invoices, etc it's
easy to create 23 hidden indexes

Your structure is not properly normalized. That's why you are getting
so many indices.

If you have inventory that has Lot Number (PK), Item Number (FK) ,
Warehouse(FK), Location, qtyOnHand your other tables should have
references to the same PK, and only use the one index.

thanks for the input
Rick Brandt wrote:

le*********@nat pro.com wrote:

you start with a small application in access97, then you have more
modules and more...
and you reach the point where tables like 'item' and 'employee' reach
the limit and you
know there's more indexes required for RI to come
>
does creating a RI programatically instead of the relationship window
still consume one of the 32 indexes ?
>
does access2000 / 2003 allow more indexes per table ?
>
is now the time to move to msde or sql / server ?
if so,
I've seen reference to an 'migration' wizard, but I don't see in
access97, is this wizard a sql
server tool ?
>
and what other migration (access -sql) issues should I be aware of
?

Since even having 32 *fields* is pretty unusual I fail to see why anyone would
ever hit 32 indexes.

Even if you change engines to one with a higher limit what will you use for an
interface? Access cannot link to tables with more than 32 indexes either.

I stongly suspect you have indexes you don't need. Are you using lookup fields?
Those add hidden indexes as do relationships. Not knowing about these might
have caused you to create non-hidden indexes that would be redundant copies of
the hidden ones. If so, then the non-hidden ones could be eliminated.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Lyle Fairfield]

le*********@nat pro.com wrote:

I just created a table in sql server 7, and I put 33 indexes on the
same field and I was able to link it in access97, and then added data to the
table.. so it looks like access will link a table with many indexes

I just made a long distance telephone call to Texas; so it looks like
Bell Telephone will put through calls to States that practice Capital
Punishment.
Amazing ... eh?

[Larry Linson]

"Lyle Fairfield" <ly***********@ aim.comwrote

I just made a long distance telephone call to
Texas; so it looks like Bell Telephone will
put through calls to States that practice Capital
Punishment.
Amazing ... eh?

Ah, but the "burning question" is: how many indexes were involved in making
that call and in practicing capital punishment? You may have just
discovered a new technique for protesting capital punishment -- the "too
many indexes" argument.

Larry

[Rick Brandt]

le*********@nat pro.com wrote:

re..

>Even if you change engines to one with a higher limit what will you
use for an interface? Access cannot link to tables with more than
32 indexes either.

I just created a table in sql server 7, and I put 33 indexes on the
same field
and I was able to link it in access97, and then added data to the
table.. so it looks like access will link a table with many indexes

I can't simulate that easily as I don't have any existing tables on SQL
Server with more than 33 indexes. However; when creating links to the
tables on our IBM ISeries it is not unusual to encounter one with more than
32 indexes.

The ISeries has what are called Logical files built against the physical
tables to be used by RPG Programs. These are essentially views, but within
each one you can specify "keys" on fields to control order and to use for
lookups. Each of these keys creates an Index so the number can go pretty
high on "popular" tables.

I cannot create links directly to any of those tables with more then 32
indexes on them.. I get "Too many idexes error" when trying to create the
link.

Perhaps 33 indexes "on the same field" as you did is not a valid test.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com