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\MSACCESS.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.Application
Private Sub SaveMDBObjectsAsText()
Dim r As Reference
DateTimeString = Format(Now(), "yyyymmddhhnn")
Path = CurrentProject.Path & "\AS_TEXT_" & DateTimeString & "\"
MkDir Path
Set App = New Access.Application
SaveDataAccessPagesAsText
SaveFormsAsText
SaveReportsAsText
SaveModulesAsText
SaveQueriesAsText
SaveMDBBase
LoadDataAccessPagesFromText
LoadFormsFromText
LoadReportsFromText
LoadModulesFromText
LoadQueriesFromText
On Error Resume Next
With App
With .CurrentProject
' disable special keys
' With .Properties
' .Add "AllowByPassKey", False
' .Item("AllowByPassKey").Value = False
' .Add "AllowSpecialKeys", False
' .Item("AllowSpecialKeys").Value = 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 acCmdCompileAndSaveAllModules
.CloseCurrentDatabase
' convert to AC2k
.ConvertAccessProject Path, Replace(Path, "XP.", "."),
acFileFormatAccess2000
' 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 SaveDataAccessPagesAsText()
Dim FileName As String
Dim Name As String
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject.AllDataAccessPages
Name = DataAccessPage.Name
FileName = Path & Name & ".txt"
SaveAsText acDataAccessPage, 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 SaveReportsAsText()
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 SaveModulesAsText()
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 SaveQueriesAsText()
Dim FileName As String
Dim Name As String
Dim GetQueryNames As ADODB.Recordset
Set GetQueryNames =
CurrentProject.Connection.OpenSchema(adSchemaViews )
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(CurrentProject.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.OpenCurrentDatabase FileName
End Sub

Private Sub LoadDataAccessPagesFromText()
Dim FileName As String
Dim Name As String
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject.AllDataAccessPages
Name = DataAccessPage.Name
FileName = Path & Name & ".txt"
App.LoadFromText acDataAccessPage, Name, FileName
Next DataAccessPage
End Sub

Private Sub LoadFormsFromText()
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.LoadFromText acForm, Name, FileName
Next Form
End Sub

Private Sub LoadReportsFromText()
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.LoadFromText acReport, Name, FileName
Next Report
End Sub

Private Sub LoadModulesFromText()
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.LoadFromText acModule, Name, FileName
Next Module
End Sub

Private Sub LoadQueriesFromText()
Dim FileName As String
Dim Name As String
Dim GetQueryNames As ADODB.Recordset
Set GetQueryNames =
CurrentProject.Connection.OpenSchema(adSchemaViews )
With GetQueryNames
Do While Not .EOF
Name = .Fields("TABLE_NAME")
FileName = Path & Name & ".txt"
App.LoadFromText 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!

[Lyle Fairfield]

BTW, is this Fun for the City of New York thing a list of RedHeads?

[jeremygetsmail]

Lyle,

I think you missed the "d" on the end of our name.

It's a list of DreadHeads. <G>

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