You comment about having too many indexes means I need more tables is just wrong. I know all of these things and you people just literally don't believe that someone could need more indexes than Access has available and that is ridiculous. This is why I don't try to ask for help or help others on forums like this. You do realize that more tables means more joins which means more foreign keys which means more indexes right? Which is why I have so many. A properly normalized database by definition has more indexes. The database is for devices that we certify and they each have many, many attributes and measurements and many of them have repeating options so it gets it's own table and some only apply to about 1% of the devices and some share attributes with other types of devices so those tables get more indexes. We track about 30 options for each device, not to mention, the approximately 30 measurements that are taken that are unique numbers, also tracking the status of the certification effort, the points of contact and their organization and their function, which devices are built into other devices, we have parent devices as well so a certification can piggyback onto another certification which means self joins. NONE of which I should have to justify to you people in a COMPLETELY unrelated thread. Not one of you has actually commented on my code, which is much better than your EXPERT advice btw, but have latched onto that one offhand comment about upgrading to SQL so that you can show off all your knowledge about how great Access is despite the fact that has nothing to do with this topic. go high five each other about how awesome you are at making tiny databases why don't you.The people who tell you that SQL Server is the ONLY solution don't have a clue what they are talking about. Yes, Jet and ACE have limits and many applications eventually reach them. Technically 255 concurrent users but practically speaking, the limit is closer to 50 - 100 as long as the LAN is stable and you use optimistic locking. The database size is limited to 2Gig. You could get around that by having multiple BE's BUT to do that you have to give up RI because RI cannot be enforced except within a single physical BE. But most of the problems are caused by poor development technique. I develop all my applications with the idea that at some point they might outgrow ACE. As a result, when that time comes and it did recently for one of my apps, I can perform the conversion in a couple of hours at best or a whole day at worst depending on how many objects need to be tested. How is that possible? For starters, all the DAO is written using the arguments SQL Server needs so I don't have to modify any code. Plus the forms are built as described below.
I have a number of Access applications that work flawlessly and quickly with millions of records. Jet/ACE are extremely well optimized. The limit is 2G, not the number of rows. All the forms are bound to queries with selection criteria to minimize the number of rows that ever need to be returned. NO form is ever bound to a naked table or query with local filtering.
Jet/ACE are limited to 32 indexes per table. I've been working with Access since the early 90's and I don't think I've ever come even close to that except for a couple of Insurance applications. Insurance applications are the worst because they always have a gazillion "codes" that require lookups and therefore relationships.
I think if you have to split a table horizontally, you almost certainly have one or more repeating groups and those need to be turned into separate tables.
Sounds like you have too many indexes. That bloats the database and slows down processing due to having to manage the index updates. Keep in mind that Access automatically builds a hidden index for each FK so you don't need to or you'll end up with two indexes for the same field. If you never removed the defaults, Access automatically builds indexes for every field whose name ends in ID, CD and a couple of other strings. Clear that field and define your own indexes.
Last edited: