I have a database that I use in VB. As records update there is a lot of useless info in there because access doesn't reuse the same spots. So that information has to be removed. Before I would use compact & repair and access 2000 and it would correct this. It would correctly adjust the file size and remove all the wasted space or whatever happens in Access.
Compact and repair feature in 2007 and 2010 does not fix the file size. why?
JoelNiemela 0 Newbie Poster
rproffitt 2,662 "Nothing to see here." Moderator
Given the time since you asked this at http://dba.stackexchange.com/questions/110767/ms-access-2007-2010-compact-repair-not-working and other sites, it may be time to think over if Access is the right database for you.
What did that person say? "Insanity is repeating the same mistakes, expecting different results."
drjohn 56 Posting Pro in Training
"Compact and repair feature in 2007 and 2010 does not fix the file size."
Really??? I just made a very simple little access 2007 database, put some data into it, edited field contents with large copy and pastes, deleted records etc, with saves after every edit, and after each time I ran the compact and repair tool, the file size was reduced.
Now if your database file is large, you may not notice a change in size from say 15,000,490 to 15,000,050 and get the impression its not working as its size could be reported as 15,000kb each time But on my little 400kb db, changes up and down of 10, 20, 50, 100kb were easily spotted.
Also, if you are running the compact and repair every time you edit a single record, then the before edit and after edit and compact repair size can be very very similiar. So you wouldn't see much difference at all. Try running it after enough edits that you can see the file size has increased after a while.
I didn't bother testing it in my access 2013 copy however.
eustacetodd 0 Newbie Poster
To move to a new container, just build a new, blank database and import everything into that new container. If you're using 2010 to do this, note you can create databases using the older .mdb format if needed (and if you're supporting users who are running 2003 or prior, you'll need to keep the .mdb format).
To do this:
1) Make a backup of your database
2) In the File tab, select the New option on the leftside menu
3) On the rightside, click the folder icon just next to the box where you type the FileName.
4) In the dialog that shows, locate the path to store the new database, and enter the name
5) Select the "Microsoft Access Databases (2002-2003 format)(*.mdb)" option in the "Save as type" dropdown.
6) Click the OK button
This will create a new database, in the location that you specify, with the name you specify.
Now open that database in 2010, and do this:
1) Click the External Data ribbon group
2) Click the Access item
3) Use the Browse button to locate the database from which you want to import the database
4) Be SURE to select the first option (i.e. the "Import tables, blah blah" option)
5) Click OK
6) In the Import Objects dialog, select the tables you want to import. Be sure to review the settings in the Options button to see if you need any of those (chances are you do not).
7) Click OK, and Access will import the selected objects to your new database
Now, close out the database and make a copy of it. Reopen the new database, and Compact it.
eustacetodd 0 Newbie Poster
If you're still have troubles after that, then there is a good chance that you have troubles with the installation of Access/Office or Windows on your machine. Be sure that you've fully updated Office and Windows, and try again. Check out this also...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195314
eustacetodd 0 Newbie Poster
If you thus fail to solve the problem then try Access Recovery Toolbox utility. I hope that it may be able to solve the problem. http://www.oemailrecovery.com/access_recovery.html If you're still having troubles compacting, then you may need to hire a recovery service to take care of the issue (you may have unrecoverable corruption).
JoelNiemela 0 Newbie Poster
I am very grateful for your advice. Thank you very much! The issue has been resolved.)
Nevin_1 0 Newbie Poster
Kernel for Access, a wonderful third party utility to extract complete data from corrupt MDB and ACCDB files of MS Access database. The software enables users to retrieve complete Access data such as OLE data, original date format, memo data, table relationships, etc. accurately.
The software supports all MS Access versions i,e. 2013, 2010, 2007, 2003, XP, 2000, 97, 95. For more information about the software features and working, visit: http://www.repairaccessdatabase.com
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.