473,569 Members | 2,762 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
10 2681
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
Sep 16 '06 #2
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
Sep 17 '06 #3
"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/
Sep 17 '06 #4
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
Sep 18 '06 #5
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
Sep 18 '06 #6

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
Sep 18 '06 #7
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?

Sep 18 '06 #8
"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
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
Sep 18 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
7956
by: U N Me | last post by:
I have a continuous form that lists records from a query. One of the columns is an amount field, In the footer of the form I place a text box that displays the total (sum) of the amount. The data in the form is linked from a one-to-many table relationship. In this instance, I really can't sum the value from the one's table since if there...
7
2794
by: JMCN | last post by:
hello i'm trying to parse out some data from the below format. first of all, i import the text file into access97 table and the try to parse out.i have a code written but when i run it, it does not go to the next "FM TX#" string and there could be a maximum of 1000+. The code only parses out the first "FM TX#" string set and then exits.what...
1
2629
by: Mario Crevits | last post by:
My name is Mario Crevits, I'm from Belgium (Roeselare) and I'm working with Access97 for several years now. We are in an Access97-2000 migration project. I'm writing a wizard for the end-users to automatically find and convert their Access97 databases on a specific drive. I want to convert the databases through VBA : 1. create an empty...
8
2080
by: swilson513 | last post by:
In Access97 I was able to have an advance filter on a form that had a Like statement so when you applied the filter EACH time it would asked for the criteria. In 2000 the same filter doesn't asked for the criteria after the first time you apply it. Is there a beter way of doing this? TIA
6
6535
by: Steven Wolf | last post by:
Hi guys, can a hashtable be faster than a sql server?? i made my own O/R mapping with an "entity cache" (caching all mapped objects). So, when i get some data from my sql server, i map that data to an entity-object and i leave a copy of it in the entity cache. the entity cache is a simple hashtable, which stores as key: the ID of the...
5
1500
by: Shiraz | last post by:
Is there some manual on how to make indexes? for example: table is create mytable ( a varchar, b varchar, c int, d int
2
3367
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd table today, it is linked as a text(255) field, ditto for the first table if I link it today if I link the 2nd table using access2003 (and the same...
3
1949
by: Ramchandar | last post by:
Hi, I am creating reports using VBA code. I have the same query in a querydef residing both in Access97 and Access2003. The result of this querydef is then moved to a table in Access97 and Access2003 respectively. The table in Access97 returns 874 rowcount, table in Access 2003 returns 1050 rowcount. In both the case the querydef is retrieving...
3
6008
by: Larry Leonard | last post by:
Running MSDE 2000 SP2 on Windows XP SP3. I have a T-SQL script that is relatively simple (adding constraints, inserting rows, etc.) and short (maybe 300 lines, heavily commented). It's an upgrade script I wrote weeks ago, and has worked fine everywhere. After the patch from last Wednesday. "Security Update for SQL Server 2000 Service...
0
7921
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8118
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7666
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7964
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5504
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1208
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
936
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.