Solved

MS Access 2003 database grew to 2 gig overnight and crashed

Posted on 2013-01-03
9
204 Views
Last Modified: 2013-11-02
I have an access database that suddenly grew to 2 gig and failed, as it only has 300 records plus sub tables in it it must have corrupted. I performed a compact and repair on it and revived it so it is now working again however its still 2 GIG in size and I am afraid it will crash again.

Does anyone know how to shink it ?
0
Comment
Question by:CtexAndy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 38739290
Create a new empty database.
Import eveything from the existing database.

File >getExternalData>Import
0
 

Author Comment

by:CtexAndy
ID: 38739316
I do not know how to create a new one of the same structure how can I empty an existing old copy of the database?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38739350
The method Peter57r suggests will bring in tables with the same structure (it may not bring in any relationships but I suspect there is a system table you can import with that). I susggect you try to determine what caused this or do you have a datatype that is causing this? Access 2010 will allow 4Gb databases, and if you use SQL Server express (free) you can get to 10Gb databases (SQL Server 2008r2 and above). Full SQL Server and you're into the Terrabyte range.


Kelvin
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 

Author Comment

by:CtexAndy
ID: 38739418
If I upgrade to 2010 will I need to do anything to the DB itself as in convert it or will it be directly read as is?

I have somebody writing an SQL version for me so only looking for a short term fix right now.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38739452
Open the mdb in Access 2010, then use the Home menu and choose Save & Publish and save database as an accdb. Access will do the rest and then use the newly created accdb instead of the mdb.


Kelvin
0
 

Author Comment

by:CtexAndy
ID: 38739461
thanks for your assistance
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38739464
Does your database have any code or macros creating temporary tables, importing data for reports etc...?

If so, I'd take a look at the data generated by such processes and see whether anything is amiss (such as a routine for calculating/formatting data for a report going into an infinite loop).
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38739814
How about attachments?

Are you storing other files (Word, Excel, jpg, ...) in the database? If so, you might want to consider storing those in a folder on your network, and simply storing the path to those files in your database.  2G gets used up pretty quickly if you are storing files in the data.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 250 total points
ID: 38740463
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

635 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