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?
5  1440  Rabbit 12,516
Recognized Expert Moderator MVP
How many fields are we talking here?
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 12,516
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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 
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)> Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.
I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not...
|
by: Mike Wilson |
last post by:
Is there a way to open an OLE DB database from within Access?
I would like to use the Access GUI with its table and query
explorer to examine a database only available through an OLEDB
provider interface.
I have seen several references on how to open an OLE DB
database through VBA/ADO code but I wouldn't know how to go
from there to actually have the database available in the
Access GUI...
|
by: ronwer |
last post by:
Hello,
The title doesn't completely cover the question I have, but it's a bit more
complicated problem we have.
We are using a database, based on Acces, but developed by a third party
developer who sells it under his own name. So, functionally it's a very
specific database for a specific purpose.
Now we want to make certain changes in this database, but for reasons of
|
by: AnandaSim |
last post by:
I just had a google through this NG but have not seen mention of Erik
Rucker's blog entry and the new Jet:
http://blogs.msdn.com/access/archive/2005/10/05/477549.aspx
mentioned by Mike Gunderloy
http://www.larkware.com/dg4/TheDailyGrind726.html
Aside from the Sharepoint feature extension, amazing news.
|
by: lgbjr |
last post by:
Hello All,
I've been developing a VB.NET app that requires the use of a DB. Up to now,
I've been using Access. It's a bit slow, but everything works. I'm at a
point now where I need to decide if I should stay with Access or move the DB
to SQL. I'm trying to come up with a list of Pros/Cons for such a move. My
list is a bit lopsided, as I have very little experience with SQL and quite
a bit with Access.
PROS for moving to SQL:
| | |
by: DanielGifford |
last post by:
Hi
I'm making some basic cryptography software and I've run into a snag.
I'm trying to retrieve some sort of system number thats specific to
the system I'm working on.
It can't change over time.
Whether its a cpu id, memory id, system serial key, or anything else
|
by: Peter Olcott |
last post by:
So far the only way that I found to do this was by making a
single global instance of the container class and providing
access to the contained class, through this single global
instance. Are there any other no-overhead ways that a
contained class can access its container?
The obvious choice of passing (a pointer or a reference to
the container) to the contained class is not a no-overhead
solution, it requires both memory and time. I am...
|
by: howard.canaway |
last post by:
I have always wondered about the specification page in the Access Help
files. It reads
Microsoft Access database (.mdb) file size 2 gigabytes. However,
because your database can include linked tables in other files, its
total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set
to True) 1,000
Number of characters in an object name...
|
by: vippstar |
last post by:
n1256.pdf (C99 TC3), 5.2.4.1 Translation limits p1 says:
Does that mean that *any* program using an array of two or more
elements other than char is allowed to be rejected by an
implementation?
For example,
int main(void) { int array; return 0; }
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
