Table and Relationship limits in Access

  • Thread starter Thread starter Bob Baldwin
  • Start date Start date
B

Bob Baldwin

Everyone,

I think I have hit a limit that I did not think would exist. I have a
database table structure for a fairly major application where we store the
template data structure in Access and we have written a fairly complex
routine that upsizes the data structure to SQL for the application in
production. The application has approximately 480 tables and 7000 fields
with all the defaults/indexes/relationships. We have always threatened to
move our template to SQL but the Access database was a great method for us
to have the structure portable.

Anyway, here is my problem. I am trying to add another table to the
Relationships and I get a message "There isn't enough memory to perform this
operation. Close unneeded applications and try the operation again". I have
tried this now with multiple versions of Access and on multiple computers
with as many applications closed as possible with the same results. I don't
believe this is a problem with the number of relationships. I believe it is
a limitation of the Relationships screen. Does anyone know a way to
expand/remove the memory limitation or know what programs I can close to
make more memory available? I can create the relationships in code but it
won't be very exciting.

Bob
 
Hi Bob,

Have you tried disabling Name Auto Correct (assuming you are using Access
2000 or above)?

Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| From: "Bob Baldwin" <[email protected]>
| Subject: Table and Relationship limits in Access
| Date: Tue, 30 Mar 2004 15:28:46 -0600
| Lines: 25
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.tablesdbdesign
| NNTP-Posting-Host: 64.243.65.232
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08
.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:77815
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| Everyone,
|
| I think I have hit a limit that I did not think would exist. I have a
| database table structure for a fairly major application where we store the
| template data structure in Access and we have written a fairly complex
| routine that upsizes the data structure to SQL for the application in
| production. The application has approximately 480 tables and 7000 fields
| with all the defaults/indexes/relationships. We have always threatened to
| move our template to SQL but the Access database was a great method for us
| to have the structure portable.
|
| Anyway, here is my problem. I am trying to add another table to the
| Relationships and I get a message "There isn't enough memory to perform
this
| operation. Close unneeded applications and try the operation again". I
have
| tried this now with multiple versions of Access and on multiple computers
| with as many applications closed as possible with the same results. I
don't
| believe this is a problem with the number of relationships. I believe it
is
| a limitation of the Relationships screen. Does anyone know a way to
| expand/remove the memory limitation or know what programs I can close to
| make more memory available? I can create the relationships in code but it
| won't be very exciting.
|
| Bob
|
|
|
 
So, let me see if I've got this straight... It's when you're working with that
"Ralationships" window (the window with all the little lines between the
tables) that you get the error message ("There isn't enough memory to perform
his operation..."). And, you've got 480 tables in that window with
who-knows-how-many relationships. Wow!!!

If that's the case, I can see that that would use up a lot of memory, although
I'm surprised that it would cause an out-of-memory condition, but who knows
what and how Access is doing what it does in that window. Maybe each table and
each relationship that is shown in that window takes up a lot of memory... and
it can't be paged out for some reason or it's not coded very efficiently. BTW,
how much memory have you got on your PCs?

I'm obviously suspicious that each one of those tables (and the relationships)
being DISPLAYED in that window is using a lot of memory. So, you might try
deleting some of those tables. Deleting a table in that window does NOT delete
any of the relationships (or the parameters for Referential Integrity or other
stuff) that you have defined. If you 'delete' the tables from the window and
add them back later, all of the relationships and RI stuff will reappear just
like they were before. So, maybe if you do that, you'll make more memory
available so that you can define new relationships. If you do try that, I'd
also suggest that you exit the Relationships window, and come back, just in
case that needs to be done to recover memory. And, who knows, maybe you ought
to exit Access and come back.

Rick
 
Bob

Chalk it up to prurient interest, but nearly 500 tables seems like a lot!
Are you fairly confident that your data structure is well-normalized?
(...and how on earth did you normalize that many tables!?)

Curiously yours

Jeff Boyce
<Access MVP>
 
Back
Top