Corrupt Front End

[Tony Toews [MVP]]

BTW you haven't answered the question about why your FE is over 400 Mb
in size.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[JimS]

The front end at 400MB compacts down to 30 MB or so, then slowly bloats back
up.

The back end (maybe 50 tables) compacts back down to maybe 200MB, then more
quickly bloats back up.

The back end probably does that because of the tables I recreate daily. I
delete all the records, then insert from a long-running query. There are two
such tables. After reading this post, I'm moving them to a separate db so
they can be more manageable (after I get back into town....)

I also delete and recreate several "import" tables from SAP and derivative
systems. Those tables can be 150,000 rows, quite wide (I import every column,
because my client is constantly changing the data she needs from those
columns....)

On the front end, I'm not sure what causes the bloating. I do have a couple
forms with graphics. I created them before learning there are steps that can
make that less cumbersome. Not worth fixing at the moment.

There are no tables (only links to maybe 50 tables). I have maybe a hundred
queries, a hundred forms, half a dozen modules, 9 macros, and a dozen
reports. The system does a lot of exporting of queries to excel, several
pivot table forms, and nothing much else. It's used by 20 or so users
typically no more than 5 at a time.

I imagine the best way to do the "separate db" for those flat tables would
be to drop the table in the separate db, copy the structure of the model
table from the backend, then insert the 157,000 records into it. That sound
right? Any resources on doing that?

 

[Tony Toews [MVP]]

Phew, thanks for asking Tony. 470Mb front and 1.1Gb back.

I was afraid to ask as someone might mistake me for asking some size thing on
a human female. Size in front and size at at the back, you get the drift.

<chuckle> <shudder> Now that's something that could get you into
deep trouble.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

The front end at 400MB compacts down to 30 MB or so, then slowly bloats back
up.

The back end (maybe 50 tables) compacts back down to maybe 200MB, then more
quickly bloats back up.

The back end probably does that because of the tables I recreate daily. I
delete all the records, then insert from a long-running query. There are two
such tables. After reading this post, I'm moving them to a separate db so
they can be more manageable (after I get back into town....)
Excellent.

I also delete and recreate several "import" tables from SAP and derivative
systems. Those tables can be 150,000 rows, quite wide (I import every column,
because my client is constantly changing the data she needs from those
columns....)

When it comes to importing data I always import all the available
data. Then use only what I need. My thinking being that you'll never
know when in the future you'll want the other columns.

On the front end, I'm not sure what causes the bloating. I do have a couple
forms with graphics. I created them before learning there are steps that can
make that less cumbersome. Not worth fixing at the moment.

Fair enough. Each graphic can take up to 1 Mb or more. Easily. You
can get an idea how large they might take by converting the graphic to
bmp format.

If I'm building a custom system then I create a form and a report with
just a logo. Then I reference those as subforms and subreports as
required. In my Granite Fleet Manager I reference the graphic file on
the hard drive each time it's required.

There are no tables (only links to maybe 50 tables). I have maybe a hundred
queries, a hundred forms, half a dozen modules, 9 macros, and a dozen
reports.

That sounds more like 3 or 5 Mb at most. Not 30 Mb. But the graphics
might account for the difference.

I imagine the best way to do the "separate db" for those flat tables would
be to drop the table in the separate db, copy the structure of the model
table from the backend, then insert the 157,000 records into it. That sound
right?
Yes.

Any resources on doing that?

Import the table into the separate MDB. Delete from main BE. Link
to that table from the FE. You're done.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Import the table into the separate MDB. Delete from main BE.
Link to that table from the FE. You're done.

If you use the same MDB, why relink? Just leave the original link
intact. This is certainly how I use my temp MDBs.

 

[Tony Toews [MVP]]

If you use the same MDB, why relink? Just leave the original link
intact. This is certainly how I use my temp MDBs.

I was describing the initial operation in creating the separately MDB.
These steps only need to be done once. Not repeatedly.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I was describing the initial operation in creating the separately
MDB. These steps only need to be done once. Not repeatedly.

But I thought you created your temp database on the fly, instead of
creating them once and letting them persist?

 

[Tony Toews [MVP]]

But I thought you created your temp database on the fly, instead of
creating them once and letting them persist?

Yes, that's generally how I use my temp database. But in this posters
situation it sounded like his temp data stayed around until the next
time it was downloaded.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/