Database doubles in size, when forms deleted even when compacted!

[Jozef]

Here's a strange one. I made a modification to a database recently, and
wound up getting an error message when trying to make an mde file. I'm
using Access XP/2002, and the error message I got was "Microsoft Access was
unable to create an mde database". After researching the error and the
solutions, I've run the gamut of solutions (or comply with the requirements)
when I came across the "maximum table ids" issue which can cause this error.

Although the database is quite large, I can't really see that being an issue
since I just made an mde out of the database a few days ago. The FE
database seems a little larger than usual (about double the size it normally
is from around 40mb to 96mb), but I didn't think too much of it. To test
it, I deleted about 1/2 the forms in the database, and compiled it. The
database doubled in size again to about 186mb. I figured o.k. when I
compact it, it will likely go back to a reasonable size, but it didn't.
EVEN COMPACTED with HALF the forms, the database is STILL 186mb!

I've rebooted, I'm using the latest service pack, it's running on a Windows
XP machine with the latest service pack. On top of all that, I still can't
create an MDE file!

Any ideas? I'm all out!

Thanks!

[Jozef]

I should also mention that I did create a new database and imported all
objects into it, compiled and compacted it, and it's STILL producing the
same problems, including it's immense size.

[Jozef]

Still not sure what happened, but here's how I got around it.

I converted the db from XP/2002 to a 2000db, then I converted it back to an
XP/2002 db. reduced the size from 186 to36mb.
"Jozef" <[e-mail]> wrote in message
news:LsNmf.251386$ir4.250710@edtnps90...

I should also mention that I did create a new database and imported all
objects into it, compiled and compacted it, and it's STILL producing the
same problems, including it's immense size.

[Larry Linson]

"Jozef" <[e-mail]> wrote

I should also mention that I did create a new database and imported all
objects into it, compiled and compacted it, and it's STILL producing the
same problems, including it's immense size.

For one thing, you should have split your database into a front-end (tables,
data, and relationships) and a back-end (queries, forms, reports, macros,
and modules). You link to the tables in the back-end from the front-end.
Only the front end need be compiled to MDE, but both the front and back ends
may have to be compacted.

There's a menu entry for the Splitting Wizard, or, you could simply make two
copies of your database, eliminate from each copy those things that you
don't need. Then find an appropriate folder in which to store the back end -
shared by all users.

Larry Linson
Microsoft Access MVP

[Wayne Gillespie]

On Sun, 11 Dec 2005 06:32:11 GMT, "Larry Linson" <bo*****@localhost.not> wrote:

"Jozef" <[e-mail]> wrote

I should also mention that I did create a new database and imported all
objects into it, compiled and compacted it, and it's STILL producing the
same problems, including it's immense size.
For one thing, you should have split your database into a front-end (tables,
data, and relationships) and a back-end (queries, forms, reports, macros,
and modules). You link to the tables in the back-end from the front-end.
Only the front end need be compiled to MDE, but both the front and back ends
may have to be compacted.

There's a menu entry for the Splitting Wizard, or, you could simply make two
copies of your database, eliminate from each copy those things that you
don't need. Then find an appropriate folder in which to store the back end -
shared by all users.

Larry Linson
Microsoft Access MVP

The FE database seems a little larger than usual

I think this would suggest that he has split the database Larry.

Wayne Gillespie
Gosford NSW Australia

[Jozef]

Yes, the database is split, and I was only referring to the FE or the Front
End.

"Wayne Gillespie" <be*****@NOhotmailSPAM.com.au> wrote in message
news:4r********************************@4ax.com...

On Sun, 11 Dec 2005 06:32:11 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:

"Jozef" <[e-mail]> wrote

>I should also mention that I did create a new database and imported all
> objects into it, compiled and compacted it, and it's STILL producing
> the
> same problems, including it's immense size.

For one thing, you should have split your database into a front-end
(tables,
data, and relationships) and a back-end (queries, forms, reports, macros,
and modules). You link to the tables in the back-end from the front-end.
Only the front end need be compiled to MDE, but both the front and back
ends
may have to be compacted.

There's a menu entry for the Splitting Wizard, or, you could simply make
two
copies of your database, eliminate from each copy those things that you
don't need. Then find an appropriate folder in which to store the back
end -
shared by all users.

Larry Linson
Microsoft Access MVP

The FE database seems a little larger than usual

I think this would suggest that he has split the database Larry.

Wayne Gillespie
Gosford NSW Australia

[Lyle Fairfield]

You couldn't imagine a 96 megabyte front end , Larry? Me neither.

I try to keep my front ends small enough to fit on a 3.5 floppy.

[BillCo]

I had a similar problem to this years ago when I was starting out. The
more I tried to reduce the size of the database by creating fresh front
ends and importing objects the worse it got. Compacting, decompiling
etc. didn't work. Then I realised that when I was importing the tables
to my fresh database, I was also importing the system tables. They were
being named MSysObjects_1, and_2 etc. The database grew exponentially
in size very quickly and I had a front end that should have been 25mb
up at 135mb.

If this was the same problem, I'm not sure how converting to a2k and
back could have sorted it for you - but it's just a thought.

[BillCo]

>I try to keep my front ends small enough to fit on a 3.5 floppy.

Lyle, that's interresting - I have a front end hovering around 20mbs
after compact...
But it is pretty big - 600 queries, 160 linked tables, 80 local tmp
tables (always emptied after use), 140 forms, 260 reports, 120 modules
and a handful of *ahem* macros.

It was double that before I took over it and cleared out the
non-essential crap.
If you could give any pointers on how to bounce this down to floppy
size I would be eternally in your debt!

[Lyle Fairfield]

I'll just content myself with admiring you for clearing out the
non-essential crap and shut up, Bill.

[BillCo]

Sorry, I've just re-read my post and it sounds like I'm being a smart
arse - but I'd genuinely like to try to strip down the size... but
short of a complete re-design I'm out of ideas. sadly i think that's
because there are no more tricks :(

[Lyle Fairfield]

Bill
I didn't think you were being a smart arse.

I did not want to get into a discussion of big front ends; this would
lead to a discussion of
- temp tables (I never use any);
- msde (where queries live in the backend) -> which would lead to a
discussion of ADPs and ODBC and maybe even ADO;
- Access as a suitable vehicle for a BIG application such as you
describe.

We've had many discussions here in CDMA about these things. Many
regulars don't agree with me about them. The discussions go on and on
and sometimes get nasty. I don't see any point in going there again.

I think you are a new poster to CDMA (I haven't noticed you before).
Your post seemed intelligent. We don't get so many of these anymore. I
wanted to encourage you to stay, not to go.

I know of only a few ways to keep the size of the FE in check:

a) no images on forms;
b) prevent temporary (JET created without asking) queries;
c) no temp tables; use ADO recordset save and open to text files if
necessary, but try to make it not necessary);
d) frequent savingastext all objects and reloadingastext to get rid of
the crud (during development);
e) having an efficient coding and object designing style;
f) don't use wizards.

I'm not saying that any of these is the right thing to do; I'm saying
they are the things I try to do.

[BillCo]

Thanks Lyle,

I'm afraid it looks like a complete re-design then for my inherited
bloatware... which is not going to happen this side of an upgrade to
..net vs sql server (a prospect which both terrifies me to my core and
is pretty exciting)

b) prevent temporary (JET created without asking) queries;

....interesting - I'll look into that