Solved

What's the maximum size?

Posted on 2004-09-07
18
10,727 Views
Last Modified: 2011-08-18
I can't seem to find the maximum recommended size for an Access database or an Access table.  The database I'm building now will have a few smallish table, and one table with a million records and 51 fields.  Will this bring Access to its knees?
0
Comment
Question by:DanR
  • 5
  • 3
  • 3
  • +5
18 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 11999615
The maximum size of an Access database is something like 1 Gig.  

Recommended size will vary by user, but I've noticed a drop in anything above 50Mb.  In addition, you'll have to repair-compact A LOT so you don't wake up one morning and find your access app is now 400Mb.

If you are editing a table with a million records and 51 fields, I'd recommend exploring something larger such as SQL Server, Oracle, etc.

Hope this helps.
-Jim
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 50 total points
ID: 11999758
it is actually 2 gig.

specification

Attribute                                                 Maximum
Microsoft Access database (.mdb) file size  2 gigabytes minus the space needed for system objects.
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     64
Number of characters in a password           14
Number of characters in a user name or group name     20
Number of concurrent users                                    255

if you want to know more
Access window click Help

search for Specification
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11999989
1GB is the maximum size for Jet 3 (A95/97) databases. 2GB is the maximum for Jet 4, but that is also the maximum for a single *table*. You can get around the 2GB *file* size by containing large tables in their own separate databases if you wish.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11999999
BTW - 1 million records won't be a problem, depending on your table design. I'm running one with something in the region of 5-8 million records.
0
 
LVL 4

Expert Comment

by:divt
ID: 12000052

I used to have a 100MB with 4-5 users, and the performance was very bad.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12000104
Depends on your design. A well designed, well indexed design will run far better than a badly designed or poorly indexed database.
0
 
LVL 4

Expert Comment

by:divt
ID: 12000149

I agree.  Number of activities on the database (add/delete/update/search) also affect performance.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12000962
I have a db with 4-5M  (about 300Mb compacted) records in one of the tbl's, a search like select aFewFields FROM myTable where oneField like 'aaaaa*' takes about 3seconds to populate another form...

once i go -> like '*aaa*' then its slows down (cant use the indexes any more....) maybe 30sec's   - in Access97
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12001016
I seem to recall that Instr is faster than Like, but I'd have to run some tests to double check that.

Why no indexes?
0
 
LVL 34

Expert Comment

by:flavo
ID: 12001038
No, there are indexes, but dont they "bocome basically usless) when ou use Like '*' & sSearch & '*' ???

ie, like with a * at the front??


Ill look into this inStr too... (when i get time from my 'real' job)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12001050
Up to a point yes, but still useful for other searches and joins. I thought you meant you had to remove them from the design :)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12001101
>> I thought you meant you had to remove them from the design :)
I may not know what's going on, but Im not that bad....

:-)
0
 

Expert Comment

by:StickyDragon
ID: 12444955
Curious about happens to a database that goes over 2 GB. I have a database at work which is 1GB and growing fairly quickly. Is it going to corrupt the database if it reaches 2GB or will it just no longer add new data to it?
0
 
LVL 1

Expert Comment

by:kkamm
ID: 12463379
if your 1 GB database is made of multiple tables, split them across multiple MDBs and link them to an MDE client.

This will delay your inevitable transition to SQL a little bit, but if you have that much data, SQL is in your very immediate future.
0
 

Expert Comment

by:StickyDragon
ID: 12463501
I have about 5-6 tables that are linked into other databases and about 15-20 that are local tables. I made a copy of the DB and deleted all the linked tables to see how much space they take up, and then deleted a few tables to see what the results would be and the database size did NOT change. Is there something I need to do to refresh it and get the proper size of the DB so I can gauge how much space I can free up by removing tables?
0
 
LVL 1

Expert Comment

by:kkamm
ID: 12464076
Compact and repair it.

That is located under the tools > Database utilities.

Chances are, your database may be smaller than you think, if you've never done that before.
0
 
LVL 3

Author Comment

by:DanR
ID: 12464692
stickydragon, I'll tell you what my experience has been.  I have one table that is about 1 GB, and it takes about 2 mins. just to open that table.  Run a query?  Time for a coffee break!  Of course, I'm running the thing on my poor laptop with 512 MB of RAM, so it's not too surprising.
0
 

Expert Comment

by:StickyDragon
ID: 12464947
Turns out that after deleting 1 table the DB is now 69MB, so I will just relocate that table into a seperate database so it dosen't corrupt the rest of the data.

Thanks.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access Form, Datasheet view, Toggle \ show Totals Row via VBA 8 40
Search for text in a .txt file 14 43
Create macro from runcode 30 24
DCount Type Mismatch 2 21
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question