Tracking down causes of bloat

[DFS]

One of my systems grew exponentially - from 13mb to 43mb - after adding some
10 temp tables (with no data), a new form, and about a thousand lines of
code. The .mdb has mostly table links, lots of forms and queries, and a
good bit of code overall.

But 43mb is totally unreasonble. I can't figure out where the problem is
occurring. It was fine for a long time. I even created a new file and
imported the objects and it blew back up to the same size.

I do have a set of global variables (one each for the db, form, querydef,
tabledef, several recordsets, form, etc). I'm very careful to close the
recordsets after setting them. I don't set the form objects to Nothing,
though I frequently set references to them.

Is there a code or system profiler that could help pinpoint the cause?

[Gary Floam]

Access is simply cancerous. After adding new tables and forms, and especially
if you have to change them a few times to get them right, the .MDB files grows
like crazy. You simply have to compact the database Tools/ Database
Utilities/Compact and Repair to make the DB smaller. It will still grow more
than it should as you add and delete records, but it will be more manageable.
It would be a good idea to compact and repair every week or so.

Hope this helps.
"DFS" <nospam@dfs_.com> wrote in message
news:Sj*******************@bignews3.bellsouth.net. ..

One of my systems grew exponentially - from 13mb to 43mb - after adding some
10 temp tables (with no data), a new form, and about a thousand lines of
code. The .mdb has mostly table links, lots of forms and queries, and a
good bit of code overall.

But 43mb is totally unreasonble. I can't figure out where the problem is
occurring. It was fine for a long time. I even created a new file and
imported the objects and it blew back up to the same size.

I do have a set of global variables (one each for the db, form, querydef,
tabledef, several recordsets, form, etc). I'm very careful to close the
recordsets after setting them. I don't set the form objects to Nothing,
though I frequently set references to them.

Is there a code or system profiler that could help pinpoint the cause?

[Lyle Fairfield]

"Gary Floam" <fl***@comcast.net> wrote in
news:uK******************************@comcast.com:

Access is simply cancerous. After adding new tables and forms, and
especially if you have to change them a few times to get them right,
the .MDB files grows like crazy. You simply have to compact the
database Tools/ Database Utilities/Compact and Repair to make the DB
smaller. It will still grow more than it should as you add and delete
records, but it will be more manageable. It would be a good idea to
compact and repair every week or so.

A well-designed and programmed JET DB behaves exactly as one might expect.
Assumming a front-end/back-end configuration, the front end should never
need compacting. The back end will need compacting to the extent that
records are deleted.
<rant>
I cannot comment upon the reasons for bloat, never having experienced it. I
never use temporary tables, (not can I think of any justification for their
use), I almost never do recordset manipulation, I use the Access default
way for record navigation, I don't use images, I am really tight-a___ed
about normalization and relationships, I am frugal in my use of indexes,
and I restrict my code to as few lines as possible. (I can never understand
persons who post here about their 20000 or 30000 lines of code. I could
write the code to produce a nuclear bomb in 20000 lines, and have enough
left over to start space exploration. What could possibly go in 20000 lines
of code?)

Bottom line: Access/JET is efficient; when you see an inefficient
Access/JET application you are seeing one which is poorly designed and
coded.
</rant>

--
Lyle Fairfield

[DFS]

Gary Floam wrote:

Access is simply cancerous. After adding new tables and forms, and
especially if you have to change them a few times to get them right,
the .MDB files grows like crazy. You simply have to compact the
database Tools/ Database Utilities/Compact and Repair to make the DB
smaller. It will still grow more than it should as you add and
delete records, but it will be more manageable. It would be a good
idea to compact and repair every week or so.

Hope this helps.
Compact and Repair? I've been building Access systems for years - of course
I did that. Didn't help a thing.

Create new .mdb, import all objects? Same size.

What would help is if I exercised my brain and remembered, before posting,
that I created about 8 reports with embedded logos, .bmp, .gif, .jpg, etc.
I just removed them, and they're the entire reason for the bloat. The file
literally goes from 9mb to 42mb with those reports.

I may try the linking thing, but then when you open the reports you usually
get that "loading..." dialog image for a few seconds, which looks
unprofessional.

Thanks


"DFS" <nospam@dfs_.com> wrote in message
news:Sj*******************@bignews3.bellsouth.net. ..

One of my systems grew exponentially - from 13mb to 43mb - after
adding some 10 temp tables (with no data), a new form, and about a
thousand lines of code. The .mdb has mostly table links, lots of
forms and queries, and a good bit of code overall.

But 43mb is totally unreasonble. I can't figure out where the
problem is occurring. It was fine for a long time. I even created
a new file and imported the objects and it blew back up to the same
size.

I do have a set of global variables (one each for the db, form,
querydef, tabledef, several recordsets, form, etc). I'm very
careful to close the recordsets after setting them. I don't set the
form objects to Nothing, though I frequently set references to them.

Is there a code or system profiler that could help pinpoint the
cause?

[DFS]

Lyle Fairfield wrote:

<rant>
I cannot comment upon the reasons for bloat, never having experienced
it.
Then you've never embedded hefty .bmp and .jpg files in forms or reports. I
finally remembered/figured out why my .mdb is so large.

I never use temporary tables, (not can I think of any
justification for their use),
When I say temp, I mean they're temporarily used, then emptied. The
tabledef object stays in the .mdb. As for justification for their use -
that depends on your system, the structures of the tables within, the flow
of data and the structure of source systems that feed yours, etc.

I almost never do recordset manipulation,
You're missing out. DAO is a glorious thing.

I use the Access default way for record navigation
Yes. I'm with you on that. Occasionally, custom first/previous/next/last
buttons are useful, but the built-in navigation is almost always
satisfactory.

I don't use images,
Judicious is best, or you end up making dumb posts like I did here.
I am really tight-a___ed about normalization and relationships,
Always. And when designing databases, too.
I am frugal in my use of indexes
I usually index all fields upon which queries and joins and searches are
made.
and I restrict my
code to as few lines as possible.
That I am in the middle on. I write as few lines of code as necessary the
first time or two through, and stop there. I'm not going to spend an
eternity looking for ways to save 10% or 20% of line count.

(I can never understand persons who
post here about their 20000 or 30000 lines of code. I could write the
code to produce a nuclear bomb in 20000 lines, and have enough left
over to start space exploration. What could possibly go in 20000
lines of code?)
A powerful, functional, feature-rich Access application. ie, here's a
routine I wrote to download a file via ftp, import the file, and post it
against some tables. It utilizes recordset manipulation, as many lines as
necessary, some error checking, etc.
================================================== ====
Public Sub getTPS(jobType As String)

'THIS ROUTINE IMPORTS THE TPS DATA AND UPDATES THE DB
'jobType INDICATES NIGHTLY, OR ON-DEMAND BY ADMIN

On Error GoTo errTPS

DoCmd.Hourglass True

If jobType = "admin" Then
v = SysCmd(acSysCmdInitMeter, "retrieving TPS data", DCount("EVN_ID",
"TX_EVN_TPS", "EVN_ID IS NOT NULL") + 3)
j = 1
v = SysCmd(acSysCmdUpdateMeter, j)
End If
'RECORD JOB START TIME IN HISTORY TABLE
Dim jobStart As Date
jobStart = Now()
If jobType = "nightly" Then
db.Execute ("INSERT INTO TS_JOB_HIST (JOB_NM, JOB_STRT, JOB_RSLT, USR_NM)
VALUES ('TPS',#" & Now() & "#,'started', 'nightly');")
ElseIf jobType = "admin" Then
db.Execute ("INSERT INTO TS_JOB_HIST (JOB_NM, JOB_STRT, JOB_RSLT, USR_NM)
VALUES ('TPS',#" & Now() & "#,'started', '" & Forms.F_MAIN.USR_LOGIN &
"');")
End If
'FILE PATHS AND NAMES
Dim TPSFileFTP As String, TPSPath As String, TPSFileName As String
TPSPath = "D:\data\TPS\"
TPSFileName = "Master_" & Format(Date, "YYYYMMDD") & ".xls"
TPSFileFTP = """Master " & Format(Date, "MM-DD-YYYY") & ".xls"" " & TPSPath
& TPSFileName
'UPDATE FTP COMMANDS IN LOCAL TEXT FILE
Open "D:\app\misc\downloadTPS.txt" For Output As #1
Print #1, "open ftp.site.com"
Print #1, "user"
Print #1, "password"
Print #1, "binary"
Print #1, "get " & TPSFileFTP
Print #1, "bye"
Close #1

If jobType = "admin" Then
j = j + 1
v = SysCmd(acSysCmdUpdateMeter, j)
End If

'RUN THE FTP GET PROCESS
Dim sExe As String
sExe = Environ$("COMSPEC")
sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
sExe = sExe & "ftp -s:D:\app\misc\downloadTPS.txt"
Shell sExe

'PAUSE - SHOULD TAKE ABOUT 10 SECONDS TO GET FILE ~ 2.3MB IN SIZE MID-APRIL
Call utlWait(20)

If jobType = "admin" Then
j = j + 1
v = SysCmd(acSysCmdUpdateMeter, j)
End If
'CHECK THAT THE FILE EXISTS AND IS >0 BYTES (DON'T SEE AN FTP COMMAND TO GET
THE REMOTE FILE SIZE FOR COMPARISON)
If FileLen(TPSPath & TPSFileName) = 0 Then

cMsg = "Failed: TPS extract file didn't download correctly. Shows size
0."
cSQL = "UPDATE TS_JOB_HIST SET JOB_END = #" & Now() & "#, JOB_RSLT = """
& cMsg & """ "
cSQL = cSQL & "WHERE JOB_NM = 'TPS' AND JOB_STRT = #" & jobStart & "#;"
db.Execute (cSQL)

If jobType = "admin" Then
MsgBox "The TPS extract file didn't download correctly. It shows size
0.", , sysTitle
v = SysCmd(acSysCmdRemoveMeter)
End If

DoCmd.Hourglass False
Exit Sub
End If
'DELETE LOCAL WORK TABLE
Call DeleteATable("TPSMaster")
db.TableDefs.Refresh
Call utlWait(1)

'IMPORT SPREADSHEET FILE TO LOCAL WORK TABLE
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "TPSMaster", TPSPath &
TPSFileName, True
'ADD ID COLUMN AND VALUE TO WORK TABLE
db.Execute ("ALTER TABLE TPSMaster ADD RestID Long;")
cSQL = "UPDATE RestGen R INNER JOIN TPSMaster E "
cSQL = cSQL & "ON R.RestCd = E.RestCd "
cSQL = cSQL & "SET E.RestID = R.RestID "
cSQL = cSQL & "WHERE Trim(R.MnemCd) <> '' "
cSQL = cSQL & "AND R.LocationCd = 'C';"
db.Execute (cSQL)
If jobType = "admin" Then
j = j + 1
v = SysCmd(acSysCmdUpdateMeter, j)
End If
'UPDATE ITEMS ACCORDING TO DATA IN CROSS-REF TABLE
'OPEN PROJECTS ONLY,
'ONLY NOT-COMPLETED EVENTS WITH NO START DATE
Set rs = db.OpenRecordset("SELECT TPS_EVENT, EVN_ID FROM TX_EVN_TPS WHERE
EVN_ID IS NOT NULL;")
If rs.RecordCount = 0 Then
cMsg = "failure: no Items cross-referenced with TPS data"
cSQL = "UPDATE TS_JOB_HIST SET JOB_END = #" & Now() & "#, JOB_RSLT = """
& cMsg & """ "
cSQL = cSQL & "WHERE JOB_NM = 'TPS' AND JOB_STRT = #" & jobStart & "#;"
db.Execute (cSQL)
rs.Close

If jobType = "admin" Then
MsgBox "Error: no Items cross-referenced with TPS data", , sysTitle
End If
GoTo exitTPS
End If

Set rs2 = db.OpenRecordset("SELECT TOP 1 * FROM TPSMaster;", ,
dbOpenSnapshot)

If rs.RecordCount > 0 Then
i = DLookup("STATUS_ID", "VL_STATUS", "STATUS_DESC = 'complete'")
Do Until rs.EOF

'ONLY UPDATE IF ITEM IN X-REF TABLE STILL FOUND IN NIGHTLY EXTRACT
'DO THIS CHECK IN CASE EXTRACT PROGRAM CHANGES AND IBM DOESN'T
NOTIFY US
For k = 0 To rs2.Fields.Count - 1
If rs("TPS_EVENT") = rs2(k).Name Then

cMsg = Left("TPS: " & rs("TPS_EVENT"),25)

cSQL = "UPDATE (TT_PROJ_CRITPATH CP INNER JOIN TT_PROJ P ON
CP.PROJ_ID = P.PROJ_ID) "
cSQL = cSQL & "INNER JOIN TPSMaster E ON E.RestID = P.RestID "
cSQL = cSQL & "SET CP.STATUS_ID = " & i & ", "
cSQL = cSQL & "CP.ACT_STRT_DT = E.[" & rs("TPS_EVENT") & "], "
cSQL = cSQL & "CP.ACT_END_DT = E.[" & rs("TPS_EVENT") & "], "
cSQL = cSQL & "CP.HTL_CMPL_BY = """ & cMsg & """ "
cSQL = cSQL & "WHERE P.PROJ_STATUS = 'Open' "
cSQL = cSQL & "AND CP.EVN_ID = " & rs("EVN_ID") & " "
cSQL = cSQL & "AND CP.STATUS_ID <> " & i & " "
cSQL = cSQL & "AND CP.ACT_STRT_DT IS NULL "
cSQL = cSQL & "AND E.[" & rs("TPS_EVENT") & "] IS NOT NULL "
db.Execute (cSQL)

Exit For
End If
Next k

If jobType = "admin" Then
j = j + 1
v = SysCmd(acSysCmdUpdateMeter, j)
End If

rs.MoveNext
Loop
End If
rs.Close
rs2.Close
'RECORD END TIME IN JOB HIST TABLE
cMsg = "success: processed TPS file for " & Format(Date, "M/D/YYYY")
cSQL = "UPDATE TS_JOB_HIST SET JOB_END = #" & Now() & "#, JOB_RSLT = """ &
cMsg & """ "
cSQL = cSQL & "WHERE JOB_NM = 'TPS' AND JOB_STRT = #" & jobStart & "#;"
db.Execute (cSQL)

'FEEDBACK
If jobType = "admin" Then
MsgBox "TPS job finished successfully", , sysTitle
End If

exitTPS:
If jobType = "admin" Then
v = SysCmd(acSysCmdRemoveMeter)
End If
DoCmd.Hourglass False
Exit Sub

errTPS:
If jobType = "nightly" Then
'RECORD ERROR IN JOB HISTORY TABLE
cMsg = Left("Failed: error " & Err.Number & ": " & Err.Description,255)
cSQL = "UPDATE TS_JOB_HIST SET JOB_END = #" & Now() & "#, JOB_RSLT = """
& cMsg & """ "
cSQL = cSQL & "WHERE JOB_NM = 'TPS' AND JOB_STRT = #" & jobStart & "#;"
db.Execute (cSQL)
ElseIf jobType = "admin" Then
MsgBox "Error " & Err.Number & " when importing TPS data: " &
Err.Description, , sysTitle
End If
Resume exitTPS

================================================== ====

Bottom line: Access/JET is efficient; when you see an inefficient
Access/JET application you are seeing one which is poorly designed and
coded.
This sounds a little like David Fenton, who once insisted that any
corruption in Access or Jet is due to external factors.

In many ways, Access/Jet is horribly *inefficient.* It's absurdly slow and
memory hungry when trying to delete records. It's a terrible client-server
db engine. Just using an Access .mdb/.mde system with a lot of querying and
code causes enormous file bloat (no matter how "efficiently" it's written).

</rant>

Superman, can you spare us some of your hubris? You have way too much.

[Lyle Fairfield]

DFS wrote:

In many ways, Access/Jet is horribly *inefficient.* It's absurdly slow and
memory hungry when trying to delete records.
I've never experienced this. I replaced the primary key on ID and
Product ID, with just ID and added a million records to Order Details
on Northwind. Then I deleted about 51000 records based on UnitPrice>14
and Discount=0.2. The delete took 12 seconds on my less than cutting
edge machine (a three year old 1.5 centrino with 256 memory). I didn't
think this was so slow for such a task?
It's a terrible client-server db engine. It's not so good at frying eggs or mowing the lawn either and it
definitely sucks at playing croquet.
Just using an Access .mdb/.mde system with a lot of querying and
code causes enormous file bloat (no matter how "efficiently" it's written). As I understand it Access/JET compiles a query first time it runs, so
files grows by size of compilation which is really quite small. After
that file growth due to that query stops. I tested this assumption
years ago and as I recall found it to be true.
Superman, can you spare us some of your hubris? You have way too much.

When one reads something here which one thinks is very wrong what
should one do? When one does not challenge the statements and
assumptions which are made, one contributes to their becoming accepted
positions. And one squirms when one reads that Access 97 is generally
accepted as being superior to later versions or other such nonsense.
Access 97 was the software of preference of the Polish cavalry in 1939.
I suppose it takes arrogance to think that one should speak up; if
that's the case it would seem that I have it.
Tant pis!
My opinion is that if others can write pure unadulterated bullshit that
reflects nothing save their own peculiar situation, incompetence or
laziness then I can write responses with my own conflicting opinions;
everyone can read, assess and accept whatever he or she feels is better.

[PBsoft]

> Compact and Repair? I've been building Access systems for years - of

course I did that. Didn't help a thing.
It doesn't completely avoid bloating, but is a little step to the correct
direction :-)
Create new .mdb, import all objects? Same size.
Another step in the correct direction.
What would help is if I exercised my brain and remembered, before
posting, that I created about 8 reports with embedded logos, .bmp,
.gif, .jpg, etc. I just removed them, and they're the entire reason
for the bloat. The file literally goes from 9mb to 42mb with those
reports.
I can confirm: these ar the very things that lead MDBs to bloat.

Anyway, I noticed that also commandbar manipulation (by VBA) takes rapidly
to bloat.
I may try the linking thing, but then when you open the reports you
usually get that "loading..." dialog image for a few seconds, which
looks unprofessional.

Right. I add that this is not a little step to the correct direction :-(

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype: pbsoftsolution