Front End Database File Size

Jerry Whittle wrote:
geo threw you a curve. He said that it's an MDE file. Therefore it can't be
decompiled.

Right. When I need to change the code, I simply compile a new MDE file and I
give this new MDE front end to the user. The file that is growing in size
during use is the MDE file.

All the information about decompiling the MDB file is great. In fact it
allowed me to resolve several issues that I had with other databases that I
am working on and it allowed me to reduce the size of the MDB file just like
the articles above suggest. Thank you very much for the decompile information.
It is extremely useful. It is just unrelated to my initial question.

The purpose of my database is to collect information and, after some minimal
processing, dump it into a back-end database. The front end is just a tiny
(460KB) compiled MDE file. All the data is stored in the back end.

The strange thing, to me, is that the size of the MDE file is growing during
use. It is not being re-compiled and has no local tables. Yet, it can more
than double in size after just a couple of uses.

This MDE front end has a lot of VBA code but all of it is compiled and
therefore static so that can not be growing. It has a single form that is the
user interface and similarly should not be growing in size. It has links to
eight tables in the back end - these are only links so they can not be
growing either. So I suppose that the culprits must be the queries. I have
six simple SELECT queries that do not really do anything but just display
information. Finally, I have one append query and one update query - both of
them targeting tables in the back end database. Would the append/update
queries be caching the data locally as they run maybe?

I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to
be applicable as well. These are UPDATE and INSERT statements that are
unavoidable.

If I am stuck with having to periodically re-compress the front end, is it
possible to initiate a "Compact and Repair Database" action from inside VBA
code? I can easily monitor the file size and detect whenever a compression is
needed from inside VBA. I am just not sure how to kick off the Compact and
Repair Database action. I suppose I could always do a SendKeys with the %TDC
keystrokes but that is like fixing furniture with duck-tape…

George
www.inspiredbrew.com

--
Message posted via http://www.accessmonster.com

thanks, Jerry -- I had to read the inital post again because
I missed those 3 little letters...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jerry Whittle wrote:
geo threw you a curve. He said that it's an MDE file. Therefore it can't be
decompiled.

Hi geo,

As Pat suggested in her discussion about decompiling, you
may do something that I do -- construct lots of SQL in code.
For grins, run find out what the db has for stored
queries... this may surprise you...

'~~~~~~~~~~~

Sub listQuerySQL()
Dim qdf As dao.QueryDef, db As dao.Database
Dim i As Integer

Set db = CurrentDb 'or whatever
i = 0
For Each qdf In db.QueryDefs
i = i + 1

If MsgBox(qdf.SQL, vbOKCancel, _
i & " " & qdf.Name) = vbCancel Then Exit Sub

'space in debug widnow is limited
' -- not all will show... look at counter (i)
'to see what you are missing

Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
Debug.Print qdf.SQL
Next qdf

Set qdf = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~

if you do have a lot of these temporary queries, you may
wish to set up a routine to delete them. They start with ~


I don't know how this will work for mde files... but here is
some code that works for mdb files that you can perhaps
adapt to compile your db

'~~~~~~~~~~~~~~~~~
'NEEDS REference To Microsoft DAO Library
'make sure DAO is as high up on the order as it will go

'------------------------------------------ CompactDB

Sub CompactDB(pDB As String)

On Error GoTo CompactDB_error

' make sure db is there
If Len(Dir(pDB)) = 0 Then
MsgBox pDB & " does not exist", , "Missing File"
Exit Sub
End If

Dim mTempDB As String

'always compact/repair on a local drive
mTempDB = "c:\tmp.mdb"

'erase temporary file
If Len(Dir(mTempDB)) 0 Then
DeleteFile mTempDB
End If

DBEngine.CompactDatabase pDB, mTempDB
' make sure compacted db is there
If Len(Dir(mTempDB)) 0 Then
' delete original
DeleteFile pDB
' rename compacted db back to original name
Name mTempDB As pDB
WaitForFile pDB
End If

CompactDB_exit:
Exit Sub

CompactDB_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
CompactDB"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo CompactDB_exit

End Sub

'------------------------------------------ WaitForFile

Sub WaitForFile(pFile As String)
On Error GoTo WaitForFile_error
SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..."
Do While Len(Dir(pFile)) = 0
' wait for file to be there
DoEvents
Loop

WaitForFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub

WaitForFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " WaitForFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo WaitForFile_exit

End Sub

'------------------------------------------ DeleteFile

Sub DeleteFile(pFile As String)
On Error GoTo DeleteFile_error
Kill pFile
SysCmd acSysCmdSetStatus, _
"Waiting for " & pFile & " to get erased ..."
Do While Len(Dir(pFile)) 0
DoEvents
Loop

DeleteFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub

DeleteFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " DeleteFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo DeleteFile_exit

End Sub

'~~~~~~~~~~~~~~`


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

geo via AccessMonster.com wrote:
Jerry Whittle wrote:

geo threw you a curve. He said that it's an MDE file. Therefore it can't be
decompiled.


Right. When I need to change the code, I simply compile a new MDE file and I
give this new MDE front end to the user. The file that is growing in size
during use is the MDE file.

All the information about decompiling the MDB file is great. In fact it
allowed me to resolve several issues that I had with other databases that I
am working on and it allowed me to reduce the size of the MDB file just like
the articles above suggest. Thank you very much for the decompile information.
It is extremely useful. It is just unrelated to my initial question.

The purpose of my database is to collect information and, after some minimal
processing, dump it into a back-end database. The front end is just a tiny
(460KB) compiled MDE file. All the data is stored in the back end.

The strange thing, to me, is that the size of the MDE file is growing during
use. It is not being re-compiled and has no local tables. Yet, it can more
than double in size after just a couple of uses.

This MDE front end has a lot of VBA code but all of it is compiled and
therefore static so that can not be growing. It has a single form that is the
user interface and similarly should not be growing in size. It has links to
eight tables in the back end - these are only links so they can not be
growing either. So I suppose that the culprits must be the queries. I have
six simple SELECT queries that do not really do anything but just display
information. Finally, I have one append query and one update query - both of
them targeting tables in the back end database. Would the append/update
queries be caching the data locally as they run maybe?

I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to
be applicable as well. These are UPDATE and INSERT statements that are
unavoidable.

If I am stuck with having to periodically re-compress the front end, is it
possible to initiate a "Compact and Repair Database" action from inside VBA
code? I can easily monitor the file size and detect whenever a compression is
needed from inside VBA. I am just not sure how to kick off the Compact and
Repair Database action. I suppose I could always do a SendKeys with the %TDC
keystrokes but that is like fixing furniture with duck-tape…

George
www.inspiredbrew.com

thanks, Jerry

yes, I didn't mention that, but did... compact that is,
after decomile. This particular db is set to compact on
close and I check file size after it closes.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jerry Whittle wrote:
Not too weird at all. When you decompiled the database, all the code got
translated into text. If you recompiled the text still stayed around. Did you
recompact the database after compiling? That's when the text is removed and
then your database should be smaller or the same size as when you started.

Great post. This has really helped me to reduce the size of my Dbs.
However, when I run the following code to delete the queries, it does not
delete them all in one pass and needs multiple passes to delete all the
unwanted queries. The database string I pass into the sub is external to the
database from which it is run.

Any help to solve this is much appreciated.

Sub delete_sys_query(pDB As String)
Dim qdf As DAO.QueryDef, db As DAO.Database
Dim i As Integer

Set db = DBEngine.Workspaces(0).OpenDatabase(pDB)
i = 0
For Each qdf In db.QueryDefs
If InStr(qdf.Name, "~") 0 Then
i = i + 1

'Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
db.QueryDefs.Delete qdf.Name

End If
Next qdf

Set qdf = Nothing
Set db = Nothing
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



"strive4peace" "strive4peace2006 at yaho" wrote:

Hi geo,

As Pat suggested in her discussion about decompiling, you
may do something that I do -- construct lots of SQL in code.
For grins, run find out what the db has for stored
queries... this may surprise you...

'~~~~~~~~~~~

Sub listQuerySQL()
Dim qdf As dao.QueryDef, db As dao.Database
Dim i As Integer

Set db = CurrentDb 'or whatever
i = 0
For Each qdf In db.QueryDefs
i = i + 1

If MsgBox(qdf.SQL, vbOKCancel, _
i & " " & qdf.Name) = vbCancel Then Exit Sub

'space in debug widnow is limited
' -- not all will show... look at counter (i)
'to see what you are missing

Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
Debug.Print qdf.SQL
Next qdf

Set qdf = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~

if you do have a lot of these temporary queries, you may
wish to set up a routine to delete them. They start with ~


I don't know how this will work for mde files... but here is
some code that works for mdb files that you can perhaps
adapt to compile your db

'~~~~~~~~~~~~~~~~~
'NEEDS REference To Microsoft DAO Library
'make sure DAO is as high up on the order as it will go

'------------------------------------------ CompactDB

Sub CompactDB(pDB As String)

On Error GoTo CompactDB_error

' make sure db is there
If Len(Dir(pDB)) = 0 Then
MsgBox pDB & " does not exist", , "Missing File"
Exit Sub
End If

Dim mTempDB As String

'always compact/repair on a local drive
mTempDB = "c:\tmp.mdb"

'erase temporary file
If Len(Dir(mTempDB)) 0 Then
DeleteFile mTempDB
End If

DBEngine.CompactDatabase pDB, mTempDB
' make sure compacted db is there
If Len(Dir(mTempDB)) 0 Then
' delete original
DeleteFile pDB
' rename compacted db back to original name
Name mTempDB As pDB
WaitForFile pDB
End If

CompactDB_exit:
Exit Sub

CompactDB_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
CompactDB"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo CompactDB_exit

End Sub

'------------------------------------------ WaitForFile

Sub WaitForFile(pFile As String)
On Error GoTo WaitForFile_error
SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..."
Do While Len(Dir(pFile)) = 0
' wait for file to be there
DoEvents
Loop

WaitForFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub

WaitForFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " WaitForFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo WaitForFile_exit

End Sub

'------------------------------------------ DeleteFile

Sub DeleteFile(pFile As String)
On Error GoTo DeleteFile_error
Kill pFile
SysCmd acSysCmdSetStatus, _
"Waiting for " & pFile & " to get erased ..."
Do While Len(Dir(pFile)) 0
DoEvents
Loop

DeleteFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub

DeleteFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " DeleteFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo DeleteFile_exit

End Sub

'~~~~~~~~~~~~~~`


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

geo via AccessMonster.com wrote:
Jerry Whittle wrote:

geo threw you a curve. He said that it's an MDE file. Therefore it can't be
decompiled.


Right. When I need to change the code, I simply compile a new MDE file and I
give this new MDE front end to the user. The file that is growing in size
during use is the MDE file.

All the information about decompiling the MDB file is great. In fact it
allowed me to resolve several issues that I had with other databases that I
am working on and it allowed me to reduce the size of the MDB file just like
the articles above suggest. Thank you very much for the decompile information.
It is extremely useful. It is just unrelated to my initial question.

The purpose of my database is to collect information and, after some minimal
processing, dump it into a back-end database. The front end is just a tiny
(460KB) compiled MDE file. All the data is stored in the back end.

The strange thing, to me, is that the size of the MDE file is growing during
use. It is not being re-compiled and has no local tables. Yet, it can more
than double in size after just a couple of uses.

This MDE front end has a lot of VBA code but all of it is compiled and
therefore static so that can not be growing. It has a single form that is the
user interface and similarly should not be growing in size. It has links to
eight tables in the back end - these are only links so they can not be
growing either. So I suppose that the culprits must be the queries. I have
six simple SELECT queries that do not really do anything but just display
information. Finally, I have one append query and one update query - both of
them targeting tables in the back end database. Would the append/update
queries be caching the data locally as they run maybe?

I also do have embedded SQL in the VBA so Pat Hartman's suggestion seems to
be applicable as well. These are UPDATE and INSERT statements that are
unavoidable.

If I am stuck with having to periodically re-compress the front end, is it
possible to initiate a "Compact and Repair Database" action from inside VBA
code? I can easily monitor the file size and detect whenever a compression is
needed from inside VBA. I am just not sure how to kick off the Compact and
Repair Database action. I suppose I could always do a SendKeys with the %TDC
keystrokes but that is like fixing furniture with duck-tape…

George
www.inspiredbrew.com

Rather than:

For Each qdf In db.QueryDefs
If InStr(qdf.Name, "~") 0 Then
i = i + 1

'Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
db.QueryDefs.Delete qdf.Name

End If
Next qdf

which works from the front of the collection to the end, you need to start
at the end of the collection, and work forward:

For intLoop = (db.QueryDefs.Count - 1) To 0 Step -1
If InStr(db.QueryDefs(intLoop).Name, "~") 0 Then
i = i + 1

'Debug.Print "--- " & i & " -- " & db.QueryDefs(intLoop).Name &
" ---"
db.QueryDefs.Delete db.QueryDefs(intLoop).Name

End If
Next intLoop

What happens when you work from the front to the back is once you delete a
specific query, the point automatically moves to the next query. However,
you then have your "Next qdf", which then moves to the query after that so
that you actually skip every other query.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"ianc" wrote in message
...
Great post. This has really helped me to reduce the size of my Dbs.
However, when I run the following code to delete the queries, it does not
delete them all in one pass and needs multiple passes to delete all the
unwanted queries. The database string I pass into the sub is external to
the
database from which it is run.

Any help to solve this is much appreciated.

Sub delete_sys_query(pDB As String)
Dim qdf As DAO.QueryDef, db As DAO.Database
Dim i As Integer

Set db = DBEngine.Workspaces(0).OpenDatabase(pDB)
i = 0
For Each qdf In db.QueryDefs
If InStr(qdf.Name, "~") 0 Then
i = i + 1

'Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
db.QueryDefs.Delete qdf.Name

End If
Next qdf

Set qdf = Nothing
Set db = Nothing
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



"strive4peace" "strive4peace2006 at yaho" wrote:

Hi geo,

As Pat suggested in her discussion about decompiling, you
may do something that I do -- construct lots of SQL in code.
For grins, run find out what the db has for stored
queries... this may surprise you...

'~~~~~~~~~~~

Sub listQuerySQL()
Dim qdf As dao.QueryDef, db As dao.Database
Dim i As Integer

Set db = CurrentDb 'or whatever
i = 0
For Each qdf In db.QueryDefs
i = i + 1

If MsgBox(qdf.SQL, vbOKCancel, _
i & " " & qdf.Name) = vbCancel Then Exit Sub

'space in debug widnow is limited
' -- not all will show... look at counter (i)
'to see what you are missing

Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
Debug.Print qdf.SQL
Next qdf

Set qdf = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~

if you do have a lot of these temporary queries, you may
wish to set up a routine to delete them. They start with ~


I don't know how this will work for mde files... but here is
some code that works for mdb files that you can perhaps
adapt to compile your db

'~~~~~~~~~~~~~~~~~
'NEEDS REference To Microsoft DAO Library
'make sure DAO is as high up on the order as it will go

'------------------------------------------ CompactDB

Sub CompactDB(pDB As String)

On Error GoTo CompactDB_error

' make sure db is there
If Len(Dir(pDB)) = 0 Then
MsgBox pDB & " does not exist", , "Missing File"
Exit Sub
End If

Dim mTempDB As String

'always compact/repair on a local drive
mTempDB = "c:\tmp.mdb"

'erase temporary file
If Len(Dir(mTempDB)) 0 Then
DeleteFile mTempDB
End If

DBEngine.CompactDatabase pDB, mTempDB
' make sure compacted db is there
If Len(Dir(mTempDB)) 0 Then
' delete original
DeleteFile pDB
' rename compacted db back to original name
Name mTempDB As pDB
WaitForFile pDB
End If

CompactDB_exit:
Exit Sub

CompactDB_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
CompactDB"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo CompactDB_exit

End Sub

'------------------------------------------ WaitForFile

Sub WaitForFile(pFile As String)
On Error GoTo WaitForFile_error
SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..."
Do While Len(Dir(pFile)) = 0
' wait for file to be there
DoEvents
Loop

WaitForFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub

WaitForFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " WaitForFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo WaitForFile_exit

End Sub

'------------------------------------------ DeleteFile

Sub DeleteFile(pFile As String)
On Error GoTo DeleteFile_error
Kill pFile
SysCmd acSysCmdSetStatus, _
"Waiting for " & pFile & " to get erased ..."
Do While Len(Dir(pFile)) 0
DoEvents
Loop

DeleteFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub

DeleteFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " DeleteFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume

GoTo DeleteFile_exit

End Sub

'~~~~~~~~~~~~~~`


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

geo via AccessMonster.com wrote:
Jerry Whittle wrote:

geo threw you a curve. He said that it's an MDE file. Therefore it
can't be
decompiled.


Right. When I need to change the code, I simply compile a new MDE file
and I
give this new MDE front end to the user. The file that is growing in
size
during use is the MDE file.

All the information about decompiling the MDB file is great. In fact it
allowed me to resolve several issues that I had with other databases
that I
am working on and it allowed me to reduce the size of the MDB file just
like
the articles above suggest. Thank you very much for the decompile
information.
It is extremely useful. It is just unrelated to my initial question.

The purpose of my database is to collect information and, after some
minimal
processing, dump it into a back-end database. The front end is just a
tiny
(460KB) compiled MDE file. All the data is stored in the back end.

The strange thing, to me, is that the size of the MDE file is growing
during
use. It is not being re-compiled and has no local tables. Yet, it can
more
than double in size after just a couple of uses.

This MDE front end has a lot of VBA code but all of it is compiled and
therefore static so that can not be growing. It has a single form that
is the
user interface and similarly should not be growing in size. It has
links to
eight tables in the back end - these are only links so they can not be
growing either. So I suppose that the culprits must be the queries. I
have
six simple SELECT queries that do not really do anything but just
display
information. Finally, I have one append query and one update query -
both of
them targeting tables in the back end database. Would the append/update
queries be caching the data locally as they run maybe?

I also do have embedded SQL in the VBA so Pat Hartman's suggestion
seems to
be applicable as well. These are UPDATE and INSERT statements that are
unavoidable.

If I am stuck with having to periodically re-compress the front end, is
it
possible to initiate a "Compact and Repair Database" action from inside
VBA
code? I can easily monitor the file size and detect whenever a
compression is
needed from inside VBA. I am just not sure how to kick off the Compact
and
Repair Database action. I suppose I could always do a SendKeys with the
%TDC
keystrokes but that is like fixing furniture with duck-tape.

George
www.inspiredbrew.com