By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Can't compact Access 200 db

P: n/a

The compacting routine just hangs after about 20 seconds. Any
suggestions?
--
Robert Fish
------------------------------------------------------------------------
Robert Fish's Profile: http://www.dbtalk.net/m390
View this thread: http://www.dbtalk.net/t307374

May 17 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I encounter this problem quite often when the database grows more
complex. My solution is:
1. Convert the database to a pevious version (from the main menu)
2. Convert it back to 2000
Most of the time this solves the problem in my case.

If this does not work, a somewhat more complex solution could be
exporting all forms, reports, etc into text files. Then create a new
database and load them again. To export the objects you can use the
code below. Safe it in a module and run 'saveTextVersion' in the direct
window. I have not yet automated the loading process, but you need the
following syntax:

LoadFromText acObjectType, objName, objTextFile

I need to do this sometimes when there are severe problems. In this
case you may want to check if all references you need for your VBA code
and exotic tools are loaded into the new database.

BTW does al your code compile without errors (in VBA editor, run
compile menu)
Mike
===================CODE===============
Sub saveTextVersion()
'export the entire database into a subdirectory of the current database

subdirName = Format(Now(), "yyyymmddhhMM")
curdirName = Mid(CurrentDb.name, 1, InStr(1, CurrentDb.name,
Dir(CurrentDb.name)) - 1)
newdirName = curdirName & subdirName
MkDir newdirName
MkDir newdirName & "\forms"
MkDir newdirName & "\reports"
MkDir newdirName & "\macros"
MkDir newdirName & "\modules"
MkDir newdirName & "\queries"

Debug.Print newdirName
With Application.CurrentProject.AllForms
For i = 0 To .Count - 1
objName = .Item(i).name
Debug.Print "Exporting forms: " & objName
SaveAsText acForm, objName, newdirName & "\forms\" & objName
Next
End With
With Application.CurrentProject.AllReports
For i = 0 To .Count - 1
objName = .Item(i).name
Debug.Print "Exporting reports: " & objName
SaveAsText acReport, objName, newdirName & "\reports\" &
objName
Next
End With
With Application.CurrentProject.AllMacros
For i = 0 To .Count - 1
objName = .Item(i).name
Debug.Print "Exporting macros: " & objName
SaveAsText acMacro, objName, newdirName & "\macros\" & objName
Next
End With
With Application.CurrentProject.AllModules
For i = 0 To .Count - 1
objName = .Item(i).name
Debug.Print "Exporting modules: " & objName
SaveAsText acModule, objName, newdirName & "\modules\" &
objName
Next
End With
With Application.CurrentDb.QueryDefs
For i = 0 To .Count - 1
objName = .Item(i).name
Debug.Print "Exporting queries: " & objName
SaveAsText acQuery, objName, newdirName & "\queries\" & objName
Next
End With

End Sub

May 17 '06 #2

P: n/a
I used to get this a lot. In my case it was always caused by a corrupt
form. This is what I did - create a new empty database and then import
all the objects from the corrupt one. If you have a corrupt object then
the process will stop when it reaches it, and you can then see which
one is causing the problem. Then you have to re-create that object or
import it from a known good copy.

By the way, I used to get this corruption when I worked on a database
that was held on a file server, and I opened it from a workstation via
a shared drive. I solved the problem by copying the database to the
workstation first and working on it like that.

Good luck
Dave

May 17 '06 #3

P: n/a
"Dave G @ K2" <da**@k2computers.co.uk> wrote in
news:11**********************@j55g2000cwa.googlegr oups.com:
I used to get this a lot. In my case it was always caused by a
corrupt form. This is what I did - create a new empty database and
then import all the objects from the corrupt one. If you have a
corrupt object then the process will stop when it reaches it, and
you can then see which one is causing the problem. Then you have
to re-create that object or import it from a known good copy.

By the way, I used to get this corruption when I worked on a
database that was held on a file server, and I opened it from a
workstation via a shared drive. I solved the problem by copying
the database to the workstation first and working on it like that.


I hardly ever encounter any kind of corruption. I am pretty sure
it's because:

1. I have COMPILE ON DEMAND turned off.

2. I decompile on a regular basis, so corruption never has a chance
to build up.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 17 '06 #4

P: n/a
David

I hadn't been doing this - mainly because I didn't know about it. So
I've just tried it and the effect on file size was quite interesting.
In round numbers - my compacted FE starts at 28 Mb. After decompile it
goes up to 33. With a compile and compact it goes down to 25. So I've
lost 3 Mb somewhere - that's got to be a good thing !!

Thanks for the tip

Dave

May 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.