Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Microsoft Access 2007 table unusually large

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
PhilR714
Asked:
PhilR714
  • 4
  • 3
  • 2
  • +2
1 Solution
 
oleggoldCommented:
The math is probably problematic here since PDFs can be stored as image/lob in the database which takes huge amount of space
0
 
oleggoldCommented:
since it's " scanned PDF documents" they're probably images so it doesn't matter that they're in pdf format
0
 
gplanaCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Has you done a Compact & Repair on the database ?
0
 
PhilR714Author Commented:
All good suggestion which we will try.  A repair and compact has been done with no file size change.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
PhilR714Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
PhilR714Author Commented:
Thanks to all who posted.  Unfortunately the owner/user has not attempted to make any changes at this point.
0
 
PhilR714Author Commented:
No solution was tried by the owner/user.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now