Solved

Microsoft Access 2007 table unusually large

Posted on 2013-06-03
11
238 Views
Last Modified: 2013-06-25
I have a customer that has an Access backend database that has reached the 2GB limit.  The table has approx 80,000 records and has a field that embeds scanned PDF documents.  Is there any way to tell which field or fields is causing the table and database to be so large.  There are about 250 fields in the table but the math doesn't add up from what he tells me.  The bottom line is we are not sure why the database is so big and how can we figure out why?
0
Comment
Question by:PhilR714
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 39217539
The math is probably problematic here since PDFs can be stored as image/lob in the database which takes huge amount of space
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39217541
since it's " scanned PDF documents" they're probably images so it doesn't matter that they're in pdf format
0
 
LVL 15

Expert Comment

by:gplana
ID: 39217542
try to identify the fields with image datatype.If possible remove these fields or replace them by a text field and save the pdf file on the file system and put the path to this file on the text field. You should also change the programs that access to this field to get data from file instead that getting it from the field directly.

Also, an alternative should be migrate from Access to SQL-Server, which allows more data.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75
ID: 39217555
Has you done a Compact & Repair on the database ?
0
 

Author Comment

by:PhilR714
ID: 39217586
All good suggestion which we will try.  A repair and compact has been done with no file size change.
0
 
LVL 75
ID: 39217607
You might want to consider this or at least be aware of if you are dealing with images in an Access database ... and I can totally vouch for this program.  

http://www.ammara.com/dbpix/access.html

It does *all* the work for you. Examples show how to add a simple 'control' panel to Load, Save, Zoom In/Out, Size To Fit and much more.  AND ... virtually eliminates BLOAT associated with storing images in an Access MDB. I have 3 clients who sell commercial run-time products that use DBPix.

Note. I have no connection with DBPix ... except I have used it many times ...

mx
0
 
LVL 57
ID: 39218778
<<I have a customer that has an Access backend database that has reached the 2GB limit.  The table has approx 80,000 records and has a field that embeds scanned PDF documents.  Is there any way to tell which field or fields is causing the table and database to be so large. >>

  Not directly.  What you do is make a copy of the DB, then in table design, drop a field, do a compact and repair, and see the resulting size.

  As the others have said, the culprit is probably the PDF file.   *Especially* if by embedded you mean that you used Access to create an embedded object.   When you do this, Access puts its own OLE wrapper around an object which basically doubles it size.

  That's why the new attachement data type was created.

<<
 There are about 250 fields in the table but the math doesn't add up from what he tells me.  The bottom line is we are not sure why the database is so big and how can we figure out why?
>>

  250 fields in a table?  Doesn't sound like it's designed well unless this is some kind of temp or import table.

Jim.
0
 

Author Comment

by:PhilR714
ID: 39219670
MX
My customer likes the idea of DBPix.  I went on the site with the link you sent but I cannot tell if it will work with scanned documents in PDF format.  Have you ever worked with it with PDFs?

Phil
0
 
LVL 75
ID: 39219793
"Have you ever worked with it with PDFs?"
No.  But I have used the product several times, and 3 clients I have have incorporated it into their commercial applications.

mx
0
 

Accepted Solution

by:
PhilR714 earned 0 total points
ID: 39263329
Thanks to all who posted.  Unfortunately the owner/user has not attempted to make any changes at this point.
0
 

Author Closing Comment

by:PhilR714
ID: 39274174
No solution was tried by the owner/user.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 365 home questions 7 65
Exporting Access Tables as CSV 3 24
Turn off MS Access Default=0 for Numerics 6 27
My SQL as Backend for Access 3 18
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

822 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