File Size Clarification For Access 2007

[Bill6868]

When Googling for Access 2007 files size limitations I am told that the file space is "2 gigabytes, minus the space needed for system objects". Not sure what they mean by system objects. I thought queries, forms, macros, reports were the system objects.

Would someone be able to explain a little more clearer about file size. I currently have a 2007 Access database that is 546 MB in size. It contains linked tables, and tables that are not linked, plus a whole lot of queries, reports, reports with imbedded queries, macros, forms.

Thank you.

 

[missinglinq]

System Objects are those things, in the background, that the Access Gnomes use to keep track of all of the things you mentioned.

Do this:

[ol 1]
[li]Right-Click on the top of the Objects Pane[/li]
[li]Click on Navigation Options[/li]
[li]Tick the Show System Objects box[/li]
[li]Click on OK[/li]
[/ol]
Now look at the Objects Pane; Everything that begins with MSys is a System Object.


Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[Bill6868]

Thanks missinglinq. From what you mention I was able to view the system objects in the Objects Pane, but it wasn't until I ran a report in the Database Documenter was I able to see the file sizes of these objects. Even that was confusing...it tells me the file size is, for example, 8 or 128 or 4 etc., but it doesn't tell me if this is KB or MB. If this is KB's then I have a long way to go before I use up 2 GB, something I may never reach anyway. Any idea if it is KB, MB?

Your advise is most appreciated.

 

[combo]

Don't bother about system objects, 2 GB is a limit of file size on the disk, it's what you can see in the explorer. Database is a part of the access file, so such description of limit. Don't know if you use compact&repair access tool, it recreates database file and shrinks the file, this process removes unused space that remains after working with the database.

combo

 

[missinglinq]

That 546 MBs you cite is included in the 2 GB limit, which puts you at approximately 25% of the limit. To be honest, very few serious Access databases, with proper design, reach anywhere near this limit.

One way to keep the size down would be to have the database split into two files, in a Front End/Back End configuration, which it should be, at any rate, if it's being used in a multi-user environment, a condition that the complexity you describe seems to suggest.

All data would go in the Back End, stored on a shared drive, with a copy of the Front End, holding everything else, on each user's PC.

If you still have a problem with the Back End file reaching the 2 GB limit, you probably need to be using something other than Access for the data, such SQL Server that doesn't have this limit. In fact, data exceeding 2 GBs would be the basis for arguing that you should be using something other than MS Access for your project.

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[Bill6868]

I'm the only one using the database so I'm not too concerned about splitting it at this time. It has a lot of .jpg photos which is adding to the size.

I have used the compact & repair procedure for years but in this IT environment I have been unable to do this. The process freezes up on me, stops and creates a back-up file. I have never had this problem before. I suspect something with the way IT has set up properties and options that is preventing me from a successful compact procedure.

Thanks combo and missinglinq.

 

[missinglinq]

It has a lot of .jpg photos which is adding to the size.

That being the case, the solution is probably going to be linking to the .jpg files, rather than actually storing them in Access.

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[combo]

You can still try to test the backup file, it's likely that acces has properly created a copy and has problems with deleting current database file. Is the database local or on a network drive? In the second case try to make local copy in a folder with full rights and compact it.

combo