Excessive database size Numfric 30.09.2006 16:00:01 I keep information about my CD library on a fairly simple database on access. There is a CD table and a Source info table with names, addresses, hyperlinks for my most common suppliers. There are a couple of very simple reports, two or three simple queries and a data entry form for the CD info. I enter the supplier info directly into the table view. There are 560 records on the CD table and about 10 on the source table. When I export the table to a fixed format text file, it creates a file of about 500KB. The .mdb file itself was 20MB until I compressed it. It is now "only" 10MB. What in the world is taking up so much space?

Re: Excessive database size "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 30.09.2006 18:12:06 All of the information about -- what the data type of the fields in the table are -- what all the other properties of the fields in the table are -- what the indices of the table are -- the properties and structure of the form -- the SQL statements of the queries -- the format of the database file -- the metadata needed for the entire file to be usable in ACCESS -- allocated space for data not yet entered (think of as an empty shelf awaiting new records) and so on.... The text output that you get is just that -- the data in text format. This takes very little disk space. -- Ken Snell <MS ACCESS MVP> "Numfric" <Numfric[ at ]discussions.microsoft.com> wrote in message news:A2EDE024-3E58-4944-8536-8C61301337A5[ at ]microsoft.com... [Quoted Text] >I keep information about my CD library on a fairly simple database on >access. > There is a CD table and a Source info table with names, addresses, > hyperlinks for my most common suppliers. There are a couple of very > simple > reports, two or three simple queries and a data entry form for the CD > info. > I enter the supplier info directly into the table view. > There are 560 records on the CD table and about 10 on the source table. > When I export the table to a fixed format text file, it creates a file of > about 500KB. The .mdb file itself was 20MB until I compressed it. It is > now > "only" 10MB. What in the world is taking up so much space?

Re: Excessive database size "Granny Spitz via AccessMonster.com" <u26473[ at ]uwe> 30.09.2006 21:42:22 Numfric wrote: [Quoted Text] > When I export the table to a fixed format text file, it creates a file of > about 500KB. The .mdb file itself was 20MB until I compressed it. It is now > "only" 10MB. What in the world is taking up so much space? Do you have any graphics or OLE objects stored in these tables? Do you have embedded graphics in the forms or reports? These can take up considerable space. Did you used to have more columns in your tables, and then delete those columns? Even if you compact the DB , the data remains in the table. This can take up considerable space. If you're not storing any space hogs, you can run some tests. Create a new DB file. Import the objects from the original DB file and compact it. Is it about the same size as the original DB file, or is it much smaller? If it's much smaller, you had some bloat that you've now eliminated. If it's about the same size, delete the new DB file, and create another new one. It should be between 64 and 96 KB, depending on which Access version you're using. Record this file size, since it's your *base* overhead. Now import your fixed format text file for the CD table into this file, add the same indexes on the table as the original CD table, and compact it. How big is the file now? Subtract the base overhead, and you'll have the size the CD table should be. Record this. Delete this table and compact the file. Import the CD table from your original DB and compact it. How big is the file now? Subtract the base overhead. Is this table way bigger than the imported text file table? If so, you've found the bloat. If not, run through the rest of the objects. Keep comparing the size of the original when imported into the new DB with the size of the text file output imported into the new DB. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200609/1

Re: Excessive database size Numfric 01.10.2006 02:08:01 My oh yes! I followed your advice and tracked it down to a background picture I had decided to use on one of the forms a few years ago (it looked cool). This was a jpg I took on my digital camera, and it's actual size is about 700KB. Sure enough, as I copied the form into the copy of the CD database, it suddenly went up to 20MB again! I compacted the new db, and it again came down to 9.9MB. Now I went in and changed the picture from Embedded to Linked, and compacted the db again. It is now a healthy 345KB. What on earth is happening here? Does Access convert the jpg to a bitmap when a picture is embedded? "Granny Spitz via AccessMonster.com" wrote: [Quoted Text] > Numfric wrote: > > When I export the table to a fixed format text file, it creates a file of > > about 500KB. The .mdb file itself was 20MB until I compressed it. It is now > > "only" 10MB. What in the world is taking up so much space? > > Do you have any graphics or OLE objects stored in these tables? Do you have > embedded graphics in the forms or reports? These can take up considerable > space. Did you used to have more columns in your tables, and then delete > those columns? Even if you compact the DB , the data remains in the table. > This can take up considerable space. > > If you're not storing any space hogs, you can run some tests. Create a new > DB file. Import the objects from the original DB file and compact it. Is it > about the same size as the original DB file, or is it much smaller? If it's > much smaller, you had some bloat that you've now eliminated. > > If it's about the same size, delete the new DB file, and create another new > one. It should be between 64 and 96 KB, depending on which Access version > you're using. Record this file size, since it's your *base* overhead. Now > import your fixed format text file for the CD table into this file, add the > same indexes on the table as the original CD table, and compact it. How big > is the file now? Subtract the base overhead, and you'll have the size the CD > table should be. Record this. Delete this table and compact the file. > Import the CD table from your original DB and compact it. How big is the > file now? Subtract the base overhead. Is this table way bigger than the > imported text file table? > > If so, you've found the bloat. If not, run through the rest of the objects. > Keep comparing the size of the original when imported into the new DB with > the size of the text file output imported into the new DB. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/200609/1 > >

Re: Excessive database size Tom Wickerath 01.10.2006 03:28:01 [Quoted Text] > Does Access convert the jpg to a bitmap when a picture is embedded? Yes, this happens in Access 2003 and all prior versions. Access 2007 has finally solved this bloat problem: http://blogs.msdn.com/access/archive/2005/11/07/490113.aspx includes the quote: Modern image support - use png, gif, jpg, etc. without database bloat and maintain transparency. Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/expert_contributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Numfric" wrote: > My oh yes! I followed your advice and tracked it down to a background > picture I had decided to use on one of the forms a few years ago (it looked > cool). This was a jpg I took on my digital camera, and it's actual size is > about 700KB. Sure enough, as I copied the form into the copy of the CD > database, it suddenly went up to 20MB again! I compacted the new db, and it > again came down to 9.9MB. Now I went in and changed the picture from > Embedded to Linked, and compacted the db again. It is now a healthy 345KB. > What on earth is happening here? Does Access convert the jpg to a bitmap > when a picture is embedded?