Table Size

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron - 14 Apr 2006 00:50 GMT

Hi all,

How do I determine the size of the tables I'm using? I looked under
properties and it's not there. The book I just browsed said table is
limited to 1GB. How do I find out what size my tables are?

TIA
ron

Reply to this Message

Wayne Morgan - 14 Apr 2006 01:14 GMT

The table size for newer versions of Access is limited to 2GB less the space
needed for system objects. Where this is derived from is that an Access
database file is limited to 2GB. You can have a single table go up to the
size of the database file limit, less the size of the built-in items in the
file, for a total of 2GB for the file. Use caution as you approach this
limit. If something causes the file to bloat and go over this limit, I
suspect you'll have problems. Keep the file compacted to allow as much
operating room as possible.

Memo fields and OLE object fields have a size limit of 1GB.

For more information, search Help for "Access Specifications".

Signature

Wayne Morgan
MS Access MVP

> Hi all,
>
[quoted text clipped - 4 lines]
> TIA
> ron

Reply to this Message

Ron - 14 Apr 2006 15:52 GMT

So, then, there's no way to find out the size of a specific table?

Seems odd to me that they'd specify a files size limit for a table of 1GB
and then not provide some way to know how big a table is, exactly. Of
course, there are many things in life that seem odd to me.

Thanks anyway,
ron

> The table size for newer versions of Access is limited to 2GB less the
> space needed for system objects. Where this is derived from is that an
[quoted text clipped - 17 lines]
>> TIA
>> ron

Reply to this Message

Wayne Morgan - 14 Apr 2006 16:17 GMT

I think you miss understood. The size limit isn't on the table, it's on the
entire MDB file. You can check the size of the MDB file in Windows Explorer
or though DOS commands.

Signature

Wayne Morgan
MS Access MVP

> So, then, there's no way to find out the size of a specific table?
>
> Seems odd to me that they'd specify a files size limit for a table of 1GB
> and then not provide some way to know how big a table is, exactly. Of
> course, there are many things in life that seem odd to me.

Reply to this Message

Ron - 14 Apr 2006 17:38 GMT

Maybe Prague and Irwin, the authors of the Microsoft Access 2000 Bible (Gold
Edition) I have misunderstood something. They say on page 1408 under
Appendix A, "Microsoft Access 2000 Specifications" that the maximum file
size of a table is 1GB. Yes, the entire DB's size is limited to 2GB, but it
says, right there, in black and grey... table MAXIMUM is 1GB.

Again, just figured if there's a limit to table size, there outta be some
easy way to determine that size so someone can't accidentally go over that
limitation, even though the entire DB isn't over the 2GB size.

Could be Prague and Irwin have it wrong, who knows. Stranger things have
happened in "Bibles". ::big grin::

Thanks,
ron

>I think you miss understood. The size limit isn't on the table, it's on the
>entire MDB file. You can check the size of the MDB file in Windows Explorer
[quoted text clipped - 5 lines]
>> and then not provide some way to know how big a table is, exactly. Of
>> course, there are many things in life that seem odd to me.

Reply to this Message

Wayne Morgan - 15 Apr 2006 00:33 GMT

Copied and pasted from the 2003 help file,

Table size - 2 gigabyte minus the space needed for the system objects

I'm booting up my OS that has Access 2000 now,

And.... They are correct. The Access 2000 help file says the table size is
limited to 1GB while the file size is limited to 2GB.

I tried looking for a way to determine the table size and couldn't find one.
If this is accurate, it would also be a problem when opening a 2002/2003
file in Access 2000. This may be one of the things that requires the
2002/2003 file format, I don't know.

Signature

Wayne Morgan
MS Access MVP

> Maybe Prague and Irwin, the authors of the Microsoft Access 2000 Bible
> (Gold Edition) I have misunderstood something. They say on page 1408
[quoted text clipped - 21 lines]
>>> 1GB and then not provide some way to know how big a table is, exactly.
>>> Of course, there are many things in life that seem odd to me.

Reply to this Message

David W. Fenton - 15 Apr 2006 01:23 GMT

> Copied and pasted from the 2003 help file,
>
[quoted text clipped - 10 lines]
> opening a 2002/2003 file in Access 2000. This may be one of the
> things that requires the 2002/2003 file format, I don't know.

That actually makes absolutely no sense, as both A2K3 and A2K use
Jet 4.0. There can't be a difference on this regard within a single
version of Jet, so one of them is simply WRONG.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Ron - 15 Apr 2006 04:40 GMT

> Copied and pasted from the 2003 help file,
>
[quoted text clipped - 9 lines]
> 2002/2003 file in Access 2000. This may be one of the things that requires
> the 2002/2003 file format, I don't know.

<snip>

Hi Wayne.

Thanks for all you've tried to do. Just thought maybe there was some easy
workaround. I guess I could create a brand new db and import just my big
file(s) one at a time occasionally just to see how the size is changing. I
don't actually think I'll ever come close to the 1GB file limit. But hey!
Back in the early 80's I used to think 256k was more memory than anyone
would ever need.

Again, thanks Wayne...I really do appreciate your time.
ron

Reply to this Message

Wayne Morgan - 16 Apr 2006 04:37 GMT

I would have to agree with David, since both Access 2003 and 2000 use the
same version of Jet, it makes no sense that one would be limited to a 1GB
table while the other is limited to approximately a 2GB table. I created an
mdb file in Access 2000 and put in only one table. I then used code to add
records. I'm currently sitting at 1.35GB (compacted) and trust me, the code
isn't using 0.35 GB of disk space. So, I would suspect that the Access 2003
help file is the correct one. It may also be a change in one of the service
packs to Jet 4.0 (I don't know) since Access 2000 came out. However, one
thing I did notice, as I had mentioned in my first post, was that if you
start getting over 1GB in the file size, you have to watch the bloat caused
by system activity or you'll wind up with problems.

Signature

Wayne Morgan
MS Access MVP

> Hi Wayne.
>
[quoted text clipped - 7 lines]
> Again, thanks Wayne...I really do appreciate your time.
> ron

Reply to this Message