Solved

MS Access 2003 database grew to 2 gig overnight and crashed

Posted on 2013-01-03
9
199 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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