MS Access 2003 database grew to 2 gig overnight and crashed

Posted on 2013-01-03
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 ?
Question by:CtexAndy
LVL 77

Assisted Solution

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

File >getExternalData>Import

Author Comment

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?
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.


Author Comment

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.
Integrate social media with email signatures

Is your company active on social media? Do you also use email signatures? Including social media icons in your email signature is a great way to get fans for free. Let all your email users know you’re on social media quickly and easily, in a single click.

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.


Author Comment

ID: 38739461
thanks for your assistance
LVL 61

Expert Comment

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).
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.
LVL 26

Accepted Solution

jerryb30 earned 250 total points
ID: 38740463

Featured Post

The problems with reply email signatures

Do you wish that you could place an email signature under a reply? Well, unfortunately, you can't. That great Exchange/Office 365 signature you've created will just appear at the bottom of an email chain. What a pain! Is there really no way to solve this? Well, there might be...

Question has a verified solution.

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

Our Group Policy work started with Small Business Server in 2000. Microsoft gave us an excellent OU and GPO model in subsequent SBS editions that utilized WMI filters, OU linking, and VBS scripts. These are some of experiences plus our spending a lo…
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

896 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now