size of a MS Access db and its components

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

size of a MS Access db and its components

Author

Topic

katarina07
Starting Member

31 Posts

Posted - 2005-12-07 : 09:27:01

Hi,

what is the best way to analyze the size of a MS Access database? I have a DB that is approx 500MB, but I dont know how to find out the sizes of the tables.

Moreover, if I delete some data, it doesnt seem to have an effect.

I tried it with a smaller database (approx 40MB) - I deleted all of the data in the tables and the size still was 40MB - is that possible?

thanks,
katarina

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-07 : 10:25:10

When data is deleted, the file size is not reduced to leave room for further changes.

You should periodically compact/repair your MDB file; once it is opened up, go to

Tools->Database Utilities->Compact and Repair

It may take a few minutes if the DB is really large; if not, it will run very quickly.

Let me how big the 500MB MDB file ends up after compacting and repairing. If there has been lots of activity in the MDB and/or if it has been around a while, it may be 10x bigger than it needs to be.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-07 : 20:06:15

Hi Katarina

when you do as Dr Cross Join has suggested, I've found on occasion with really large databases that the Access compact utility may not be able to cope (500MB is definitely the largest access database I've ever heard of).

The other option you have in this case is to create a new access database and import in all the objects from the old one. This will have a similar effect to the compact option. If you have to do this, just check your startup options are the same.

Post back if you need more help - and MAKE SURE YOU HAVE A BACKUP of the access database before you do anything.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

katarina07
Starting Member

31 Posts

Posted - 2005-12-08 : 04:28:04

Hi,

I have tried both ways:
1) compact and repair - db ended up at 196 MB
2) importing all the objects to a new db - 197 MB

I wanted to ask:
- what do you mean by startup options by importing? where can I check them?
- i still would like to know if there is some way to check the size of individual tables

thanx a lot
katarina

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-09 : 11:50:05

to check an individual table (if you really need to):

1) Create a new, blank MDB file
2) Make note of the size of that file
3) import the table into the blank file
4) Make note of the NEW size of that file
5) The difference between the old size and the new size is the approximate amount of space that table takes

Remmeber that indexes take up space as well, in addition to table contents.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-11 : 17:35:55

Hey Katarina

I'll bet the table data is NOT the bulk of your filesize issue - check for pictures on your forms and reports - I doubt you could actually have that much data in your tables.

From the debug window (ALT-F11 and then CTRL-G) you can issue

Debug.Print DCount("FieldName", "Tablename")

to see how many rows are in a table (substitute in your own table name and fieldname though.)

The way to check your startup options is to unhide the database window and then right-click on the title bar of the database window and choose "Startup ..."

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-11 : 19:16:37

quote:
Originally posted by rrb

Hey Katarina

I'll bet the table data is NOT the bulk of your filesize issue - check for pictures on your forms and reports - I doubt you could actually have that much data in your tables.

From the debug window (ALT-F11 and then CTRL-G) you can issue
Debug.Print DCount("FieldName", "Tablename")
to see how many rows are in a table (substitute in your own table name and fieldname though.)

The way to check your startup options is to unhide the database window and then right-click on the title bar of the database window and choose "Startup ..."

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

I've worked with some 500MB Access databases back in the day .... technically, they were even bigger because we split some tables out into seperate MDB file and linked them all together for certain reports.

Access did surprisingly well with databases this size; as long as you have proper indexes, keep things compacted + repaired, and have a fast network, it is amazing how well it does considering it is not a client/server app.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-11 : 19:25:34

True - there's a lot of bad press for Access, but I still like it...if you use it the way it is intended its a pretty good little tool...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources