Access Limits Making Things Impossible

[tduncan]

I've been trying to build a database with Access, and have been building query on top of query to get around Access' limits on the number of fields in a table or query. I've divided up some of my tables, but still need a total on my reports. I keep running into the wall with "Too many fields defined" or "Query is too complex."

Is there a way around the seemingly artificial limits Access puts on you? Or is there another database I should consider exporting to?

[Rabbit]

How many fields are we talking here?

[tduncan]

Rabbit,
I don't know the exact number, but it's a few hundred fields. Access doesn't tell me how many there are in any table or query. It only gives me grief after going over the limit.

[Rabbit]

The limit in Access is 255 fields. If you're hitting that then you're going to want to upgrade to an enterprise DBMS like SQL Server or Oracle, but those cost a pretty penny.

However, what kind of reports are you making where you need 255 fields of information?

[MMcCarthy]

I've been trying to build a database with Access, and have been building query on top of query to get around Access' limits on the number of fields in a table or query. I've divided up some of my tables, but still need a total on my reports. I keep running into the wall with "Too many fields defined" or "Query is too complex."

Is there a way around the seemingly artificial limits Access puts on you? Or is there another database I should consider exporting to?

Is your database normalised?

Have a look at this tutorial to see what I mean if you are not sure.

Database Normalisation and Table Structures

[missinglinq]

Are you by any chance creating all of these reports and queries and tables using the Access Wizards? The reason I ask is that the limits set by Access on the number of fields in these objects, 255, is very seldom reached in even the most complex databases executed in Access! Many experts claim that tables with more than 25-30 fields are invariably non-normalized. The Access Wizards, however, allow a much lower number of fields to be used. I've never actually seen any documentation on the specs for these Wizards, but the limits are much, much lower than the actual limits for the objects themselves. The Wizards are designed with the casual user in mind, people who want to run up a quick database to track memberships in a home owners association or people who are keeping track of their monthly bills. They are not meant to be used in a database where fields are going to number in the hundreds.

A general idea of what you're trying to accomplish with this app might be useful to us as we're trying to help you with it.

Welcome to TheScripts!

Linq ;0)>