Solved

MS Access 2003 database grew to 2 gig overnight and crashed

Posted on 2013-01-03
9
200 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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
Learn how to PXE Boot both BIOS & UEFI machines with DHCP Policies and Custom Vendor Classes
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…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

807 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