Stubborn front-end bloat in Access 2002

[Steve Jorgensen]

I'm working on an application in Access 2002, and every time I'm done making
changes in the VBA project, the database is noticeably larger after
compacting, even when code changes are small or even if they involve removing
more code than I add.

In the past, I've usually been able to recover space in these situations by
decompiling the front-end, but in this case, after I decompile, compact,
recompile, and compact again, the database has bloated even larger. The
database is now over half again the size it was a few months ago, and the
added content over that time probably represents about a 2 or 3% increase.

Any ideas?

[Lyle Fairfield]

I have no ideas as to why, except that sometimes Access seems to save
ugly things (that it created all by itself) in its secret vault, and
its very difficult to find them, much less get rid of them.

Have you tried the old
SaveAsText
LoadFromText (into a new file)
for all objects
trick?

I can send to you or post code that should do it all for you with
minimum pain if you think you might like to go that way.

[Steve Jorgensen]

On 29 Nov 2005 04:52:28 -0800, "Lyle Fairfield" <ly***********@ aim.com> wrote:

I have no ideas as to why, except that sometimes Access seems to save
ugly things (that it created all by itself) in its secret vault, and
its very difficult to find them, much less get rid of them.

Have you tried the old
SaveAsText
LoadFromText (into a new file)
for all objects
trick?

I can send to you or post code that should do it all for you with
minimum pain if you think you might like to go that way.

I know how to do those things. Unfortunately, this database also uses
user/workgroup security, so permissions will also have to be rebuilt.

[Ted]

I would try to "decompile" your application, then recompiling it.
That seems to clear up a bunch of things.
Here is the command I use to start Access. Then I open the file I want
to work on.
Just modify the command for your version of Office and Access (change
the command to point to your MSACCESS.EXE file).
"C:\Program Files\Microsoft Office 2003\OFFICE11\M SACCESS.EXE"
/decompile
Ted
Steve Jorgensen wrote:

On 29 Nov 2005 04:52:28 -0800, "Lyle Fairfield" <ly***********@ aim.com> wrote:

I have no ideas as to why, except that sometimes Access seems to save
ugly things (that it created all by itself) in its secret vault, and
its very difficult to find them, much less get rid of them.

Have you tried the old
SaveAsText
LoadFromText (into a new file)
for all objects
trick?

I can send to you or post code that should do it all for you with
minimum pain if you think you might like to go that way.

I know how to do those things. Unfortunately, this database also uses
user/workgroup security, so permissions will also have to be rebuilt.

[altacct]

Lyle,

I'm actually about to do that to a database of mine, because it's been
giving intermitent corruption problems. If you don't mind, I'd love it
if you could post that code here.

Thanks much.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

[Lyle Fairfield]

Yikes ... it's with a great deal of trepidation that I post this but I
suppose the worst it can do is completely destroy your database,
permanently all data from your hard drive and burn down your house.
Maybe you should try it on some innocuous copy? And I don't know about
permissions which is an issue that Steve raises. This was written for
XP; it works in 2003. It won't work in 97. 2000 ... I dunno. There's
another virgin for ADPs.

Dim Path As String
Dim DateTimeString As String
Dim App As Access.Applicat ion
Private Sub SaveMDBObjectsA sText()
Dim r As Reference
DateTimeString = Format(Now(), "yyyymmddhh nn")
Path = CurrentProject. Path & "\AS_TEXT_" & DateTimeString & "\"
MkDir Path
Set App = New Access.Applicat ion
SaveDataAccessP agesAsText
SaveFormsAsText
SaveReportsAsTe xt
SaveModulesAsTe xt
SaveQueriesAsTe xt
SaveMDBBase
LoadDataAccessP agesFromText
LoadFormsFromTe xt
LoadReportsFrom Text
LoadModulesFrom Text
LoadQueriesFrom Text
On Error Resume Next
With App
With .CurrentProject
' disable special keys
' With .Properties
' .Add "AllowByPassKey ", False
' .Item("AllowByP assKey").Value = False
' .Add "AllowSpecialKe ys", False
' .Item("AllowSpe cialKeys").Valu e = False
' End With
Path = .FullName
End With

For Each r In .References
With r
If Not .BuiltIn Then
App.References. Remove r
End If
End With
Next r

For Each r In References
With r
If Not .BuiltIn Then
App.References. AddFromGuid r.Guid, r.Major, r.Minor
End If
End With
Next r

.RunCommand acCmdCompileAnd SaveAllModules
.CloseCurrentDa tabase
' convert to AC2k
.ConvertAccessP roject Path, Replace(Path, "XP.", "."),
acFileFormatAcc ess2000
' make mde
' .SysCmd 603, Path, Replace(Path, ".mdb", ".mde")
.Quit
End With
Set App = Nothing
MsgBox "All Done with Text Backup"
End Sub

Private Sub SaveDataAccessP agesAsText()
Dim FileName As String
Dim Name As String
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject. AllDataAccessPa ges
Name = DataAccessPage. Name
FileName = Path & Name & ".txt"
SaveAsText acDataAccessPag e, Name, FileName
Next DataAccessPage
End Sub

Private Sub SaveFormsAsText ()
Dim FileName As String
Dim Name As String
Dim Form As AccessObject
For Each Form In CurrentProject. AllForms
Name = Form.Name
FileName = Path & Name & ".txt"
SaveAsText acForm, Name, FileName
Next Form
End Sub

Private Sub SaveReportsAsTe xt()
Dim FileName As String
Dim Name As String
Dim Report As AccessObject
For Each Report In CurrentProject. AllReports
Name = Report.Name
FileName = Path & Name & ".txt"
SaveAsText acReport, Name, FileName
Next Report
End Sub

Private Sub SaveModulesAsTe xt()
Dim FileName As String
Dim Name As String
Dim Module As AccessObject
For Each Module In CurrentProject. AllModules
Name = Module.Name
FileName = Path & Name & ".txt"
SaveAsText acModule, Name, FileName
Next Module
End Sub

Private Sub SaveQueriesAsTe xt()
Dim FileName As String
Dim Name As String
Dim GetQueryNames As ADODB.Recordset
Set GetQueryNames =
CurrentProject. Connection.Open Schema(adSchema Views)
With GetQueryNames
Do While Not .EOF
Name = .Fields("TABLE_ NAME")
FileName = Path & Name & ".txt"
SaveAsText acQuery, Name, FileName
.MoveNext
Loop
End With
End Sub

Private Sub SaveMDBBase()
Dim FileName As String
Dim Name As String
Name = Replace(Current Project.Name, CurrentProject. Path, "")
' name it with XP sffix to enable later conversion to 2K
Name = Replace(Name, ".", "XP.")
FileName = Path & Name
SaveAsText 6, "", FileName
App.OpenCurrent Database FileName
End Sub

Private Sub LoadDataAccessP agesFromText()
Dim FileName As String
Dim Name As String
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject. AllDataAccessPa ges
Name = DataAccessPage. Name
FileName = Path & Name & ".txt"
App.LoadFromTex t acDataAccessPag e, Name, FileName
Next DataAccessPage
End Sub

Private Sub LoadFormsFromTe xt()
Dim FileName As String
Dim Name As String
Dim Form As AccessObject
For Each Form In CurrentProject. AllForms
Name = Form.Name
FileName = Path & Name & ".txt"
App.LoadFromTex t acForm, Name, FileName
Next Form
End Sub

Private Sub LoadReportsFrom Text()
Dim FileName As String
Dim Name As String
Dim Report As AccessObject
For Each Report In CurrentProject. AllReports
Name = Report.Name
FileName = Path & Name & ".txt"
App.LoadFromTex t acReport, Name, FileName
Next Report
End Sub

Private Sub LoadModulesFrom Text()
Dim FileName As String
Dim Name As String
Dim Module As AccessObject
For Each Module In CurrentProject. AllModules
Name = Module.Name
FileName = Path & Name & ".txt"
App.LoadFromTex t acModule, Name, FileName
Next Module
End Sub

Private Sub LoadQueriesFrom Text()
Dim FileName As String
Dim Name As String
Dim GetQueryNames As ADODB.Recordset
Set GetQueryNames =
CurrentProject. Connection.Open Schema(adSchema Views)
With GetQueryNames
Do While Not .EOF
Name = .Fields("TABLE_ NAME")
FileName = Path & Name & ".txt"
App.LoadFromTex t acQuery, Name, FileName
.MoveNext
Loop
End With
End Sub

[altacct]

Backups? We don't need no stinkin' backups!

Thanks, Lyle. I promise to use with caution, and to report back here
only if I have exceedingly good news to report on how things went.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

[Steve Jorgensen]

Just an update...

The client opened the database in Access 2003, ran repair & compact, and it
shrunk right down to 1/3 of its previous size. Worst case, if it resumes
bloating again, I'll just have the client compact it for me every few months
or upgrade my Access to 2003.

On Tue, 29 Nov 2005 01:35:12 -0800, Steve Jorgensen <no****@nospam. nospam>
wrote:

I'm working on an application in Access 2002, and every time I'm done making
changes in the VBA project, the database is noticeably larger after
compacting, even when code changes are small or even if they involve removing
more code than I add.

In the past, I've usually been able to recover space in these situations by
decompiling the front-end, but in this case, after I decompile, compact,
recompile, and compact again, the database has bloated even larger. The
database is now over half again the size it was a few months ago, and the
added content over that time probably represents about a 2 or 3% increase.

Any ideas?

[jeremygetsmail]

Lyle,

Looks like it worked. There were a few tweaks I made for my purposes,
but this was definitely helpful.

Hopefully the testing we'll do over the next couple of weeks won't turn
up any more instances of what was an intermittent PITA.

Thanks so much.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

[Lyle Fairfield]

Fabulous!