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
?

Sep 16 '06 #1

Smartin

le*********@natpro.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

Sep 16 '06 #2

Rick Brandt

le*********@natpro.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

Sep 17 '06 #3

David W. Fenton

"Rick Brandt" <ri*********@hotmail.comwrote in
news:%B***************@newssvr29.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/

Sep 17 '06 #4

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*********@natpro.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

Sep 18 '06 #5

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*********@natpro.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

Sep 18 '06 #6

rquintal

le*********@natpro.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*********@natpro.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

Sep 18 '06 #7

Lyle Fairfield

le*********@natpro.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?

Sep 18 '06 #8

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

Sep 18 '06 #9

Rick Brandt

le*********@natpro.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

Sep 18 '06 #10

David W. Fenton

le*********@natpro.com wrote in
news:11**********************@m73g2000cwd.googlegr oups.com:

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

Do any of the non-hidden indexes duplicate the hidden indexes?

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

Sep 18 '06 #11