Front end file size grows when switching back end dbs

[Guest]

I have a front end application and 20 different company mdbs to serve as a
back end (they need to be kept separate for many reasons). The company
abbreviations (which serve as the folder names in which CompanyData.mdb is
stored; all company mdbs have the same database file name) is stored in a
Front End table called tblLocations. The code to change databases and relink
tables works quite well.

The problem is that the customer oftentimes needs to change databases
several times during the day, and with each change/relink, the front end
database grows quite large, from about 16Mb up to 40Mb and beyond with at
least 3-4 changes of the back end data file.

Is it simply the relinking of tables that would cause this, or is it more
likely that the code is dumping data into temporary files and not getting
cleaned out properly? The code is quite long, so I didn't want to post it
initially.

Thoughts/ideas appreciated.

WinXP & Office 03

 

[Guest]

Easy solution it to check Compact On Close in Tools, Options, General tab.

 

[Guest]

Sorry, I forgot to note that I've set up a message box to instruct the user
to Compact when the file size > x (since you can't programmatically Compact).

My question was more curiosity as to why the file would increase in size
simply by relinking tables to a new database.

 

[Guest]

I don't know the answer to your question. The mysteries of why databases
bloat is pretty etherical.

You can programmatically compact, but it takes some trickery. Seems like an
imposition to the users to have to do this periodically during the day. Is
is really necessary?

 

[Guest]

np.

Unfortunately, it is necessary as the performance decreases significantly
once the front end hits a certain level and beyond.

I thought you couldn't program a compact from within the database you are
trying to compact. Solutions are eagerly anticipated!!!!

 

[Marshall Barton]

You can not Compact the database while it is running, it
must close before the Compact can proceed. Even if you can
issue the DoCmd to compact the database, Access will close
it before compacting it. Access would reopen it but you
would be back to the beginning.

Personally, I am too paranoid to run a process that
rearranges every byte in the file in a routine, automatic
procedure. I think it's far safer to include Compact as a
part of the daily/weekly backup procedure where you can
quickly recover if anything goes wrong.

There are a ton of reasons why your front end might bloat,
the worst would be if you have code that creates/modifies or
just saves a form or report's design. Modifying querydefs
would also do it, but much less. Another major cause would
be if you used Make Table queries to provide quick access to
data in the frontend.

Other than those, bloat can also be caused by Access's
internal processes such as keeping track of large
recordsets. I have never seen relinking cause it, but I
don't do a lot of it so I wouldn't doubt it if it does.
Most of the internal Access file size increases are either
automatically reclaimed or the space is reused the next time
Access needs that kind of space. Does your bloat continue
to grow forever or does it stabilize at a some particular
size?

 

[Guest]

kind of what i have learned in reading through various groups and threads....

the front end grows endlessly, usually by a regular amount (within a range).
I've trained the client (and staff) to do the compact on a regular basis
just out of good maintenance, but it think it's more suzuki method than
cognitive enhancement. so much for educating the masses.

the only item that is open is the main switchboard, which consists of
command buttons to open other forms for specific modules.

I guess it's just something they will have to live with. thanks for the info.

You can not Compact the database while it is running, it
must close before the Compact can proceed. Even if you can
issue the DoCmd to compact the database, Access will close
it before compacting it. Access would reopen it but you
would be back to the beginning.

Personally, I am too paranoid to run a process that
rearranges every byte in the file in a routine, automatic
procedure. I think it's far safer to include Compact as a
part of the daily/weekly backup procedure where you can
quickly recover if anything goes wrong.

There are a ton of reasons why your front end might bloat,
the worst would be if you have code that creates/modifies or
just saves a form or report's design. Modifying querydefs
would also do it, but much less. Another major cause would
be if you used Make Table queries to provide quick access to
data in the frontend.

Other than those, bloat can also be caused by Access's
internal processes such as keeping track of large
recordsets. I have never seen relinking cause it, but I
don't do a lot of it so I wouldn't doubt it if it does.
Most of the internal Access file size increases are either
automatically reclaimed or the space is reused the next time
Access needs that kind of space. Does your bloat continue
to grow forever or does it stabilize at a some particular
size?
--
Marsh
MVP [MS Access]

Unfortunately, it is necessary as the performance decreases significantly
once the front end hits a certain level and beyond.

I thought you couldn't program a compact from within the database you are
trying to compact. Solutions are eagerly anticipated!!!!

 

[Guest]

I never said you can Compact the database that is currently open. There is a
trick to it. What you need is another small database that does nothing more
than Compact your main database, then reopen it. So here is the sequence:
Main DB shells to Compact DB
Main DB quits
Compact DB compacts Main DB
Compact opens Main
Compact quits

kind of what i have learned in reading through various groups and threads....

the front end grows endlessly, usually by a regular amount (within a range).
I've trained the client (and staff) to do the compact on a regular basis
just out of good maintenance, but it think it's more suzuki method than
cognitive enhancement. so much for educating the masses.

the only item that is open is the main switchboard, which consists of
command buttons to open other forms for specific modules.

I guess it's just something they will have to live with. thanks for the info.

You can not Compact the database while it is running, it
must close before the Compact can proceed. Even if you can
issue the DoCmd to compact the database, Access will close
it before compacting it. Access would reopen it but you
would be back to the beginning.

Personally, I am too paranoid to run a process that
rearranges every byte in the file in a routine, automatic
procedure. I think it's far safer to include Compact as a
part of the daily/weekly backup procedure where you can
quickly recover if anything goes wrong.

There are a ton of reasons why your front end might bloat,
the worst would be if you have code that creates/modifies or
just saves a form or report's design. Modifying querydefs
would also do it, but much less. Another major cause would
be if you used Make Table queries to provide quick access to
data in the frontend.

Other than those, bloat can also be caused by Access's
internal processes such as keeping track of large
recordsets. I have never seen relinking cause it, but I
don't do a lot of it so I wouldn't doubt it if it does.
Most of the internal Access file size increases are either
automatically reclaimed or the space is reused the next time
Access needs that kind of space. Does your bloat continue
to grow forever or does it stabilize at a some particular
size?
--
Marsh
MVP [MS Access]

Unfortunately, it is necessary as the performance decreases significantly
once the front end hits a certain level and beyond.

I thought you couldn't program a compact from within the database you are
trying to compact. Solutions are eagerly anticipated!!!!

:
I don't know the answer to your question. The mysteries of why databases
bloat is pretty etherical.

You can programmatically compact, but it takes some trickery. Seems like an
imposition to the users to have to do this periodically during the day. Is
is really necessary?

:
Sorry, I forgot to note that I've set up a message box to instruct the user
to Compact when the file size > x (since you can't programmatically Compact).

My question was more curiosity as to why the file would increase in size
simply by relinking tables to a new database.

:

Easy solution it to check Compact On Close in Tools, Options, General tab.

:

I have a front end application and 20 different company mdbs to serve as a
back end (they need to be kept separate for many reasons). The company
abbreviations (which serve as the folder names in which CompanyData.mdb is
stored; all company mdbs have the same database file name) is stored in a
Front End table called tblLocations. The code to change databases and relink
tables works quite well.

The problem is that the customer oftentimes needs to change databases
several times during the day, and with each change/relink, the front end
database grows quite large, from about 16Mb up to 40Mb and beyond with at
least 3-4 changes of the back end data file.

Is it simply the relinking of tables that would cause this, or is it more
likely that the code is dumping data into temporary files and not getting
cleaned out properly? The code is quite long, so I didn't want to post it
initially.

WinXP & Office 03