Multi-User Access MDE File Growing and Growing.......

[Sonic]

I have an MDE file that is growing exponentially (from 3,900 KB to over
132,000 KB today). I am not saving data in this MDE, although I use a
mix of offline and SQL tables for Read Only querying. I also have many
forms and some reports and queries. Many people use this DB on a daily
basis and there is constantly someone in it, virtually every minute.

The DB tends to crash every couple of weeks. I am wondering if the
size of this could be the cause of the crashes or just the multi-user
environment aspect of this.

Is there any way that I can some how "compact" the DB without having
all of the users either logging out or being kicked out of the DB (by
us in the IS department)? We have to replace the DB with a compacted
MDE about every 2 weeks.

Thanks.

[PC Datasheet]

Check two things. First is the database split into a front end and a back
end with the backend on the server and a copy of the front end on each
station. It sounds like the database is one file and all the users are
running it over the network. This will give you the problems you describe.
Second, look at the programming in the database. Are records being
constantly added to temp tables and then later deleted. This is a sure cause
of bloating.

BTW, if you ever need outside help with your database applications, please
contact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com


"Sonic" <js*******@stat eindustrial.com > wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I have an MDE file that is growing exponentially (from 3,900 KB to over
132,000 KB today). I am not saving data in this MDE, although I use a
mix of offline and SQL tables for Read Only querying. I also have many
forms and some reports and queries. Many people use this DB on a daily
basis and there is constantly someone in it, virtually every minute.

The DB tends to crash every couple of weeks. I am wondering if the
size of this could be the cause of the crashes or just the multi-user
environment aspect of this.

Is there any way that I can some how "compact" the DB without having
all of the users either logging out or being kicked out of the DB (by
us in the IS department)? We have to replace the DB with a compacted
MDE about every 2 weeks.

Thanks.

[Di]

Unfortunately databases can only be compacted when a single user is in
it, well as far as I have seen anyway.

I have not yet seen multiple users as a problem but size will cause
slow downs and eventually people get tired of waiting and keep hitting
enter because they think it's not working, this can cause your crash or
it simply runs out of memory.

I found that the "size blow up" problem was exacerbated by cross
version users, ie 2000, XP and 2003 using the same database. If you
have any of that going on try making versions for these users, the
tables are just links so the data will all be dumped in the same spot.

I was also told to empty and close recordsets in my vb code after use.

If you have the original, code in an autocompact which checks for users
and compacts when the person who opens it is the only user.

D...

[Arno R]

BTW, you never seem to learn that advertising is 'not done' in this ng.
BTW, can't you just share your knowledge just like others do?

Arno R.
"PC Datasheet" <no****@nospam. spam> schreef in bericht
news:lA******** *********@newsr ead2.news.atl.e arthlink.net...

BTW, if you ever need outside help with your database applications, please
contact me at my email address below.

--
PC Datasheet

[Sonic]

The DB is not split, it is one file being run over the network. And as
far as records being added to a table and then later deleted, I don't
have anything being added or deleted in this DB. There are queries
being run, but nothing is being saved by these (outside of the normal
temp tables that Access uses when queries are being run). No make
tables queries are present in this DB.

[Sonic]

Everyone is running 2000 in our company, so the cross versions do not
apply in this case. We have checked for recordsets in all of the codes
and have emptied and closed them as well. I do have the original code
and will put in the autocompact as you suggested. I'll also have to
send out a message to instruct everyone to close out of the DB when
leaving for the day. Occasionally, users will keep this open
overnight. Thanks for the suggestions.

[Fred Zuckerman]

"Sonic" <js*******@stat eindustrial.com > wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I have an MDE file that is growing exponentially (from 3,900 KB to over
132,000 KB today). I am not saving data in this MDE, although I use a
mix of offline and SQL tables for Read Only querying. I also have many
forms and some reports and queries. Many people use this DB on a daily
basis and there is constantly someone in it, virtually every minute.

The DB tends to crash every couple of weeks. I am wondering if the
size of this could be the cause of the crashes or just the multi-user
environment aspect of this.

Is there any way that I can some how "compact" the DB without having
all of the users either logging out or being kicked out of the DB (by
us in the IS department)? We have to replace the DB with a compacted
MDE about every 2 weeks.

Thanks.

You could use the "Compact On Close" option (Tools > Options > General). It
is generally frowned upon in this group. But it only compacts for the last
person exitting the application. Which is just what you want. One problem
you may experience is that whoever is the last one out, may get tired of
waiting for the compacting to complete and they may choose end-task...
Fred Zuckerman

[J. Clay]

You should immediately work on getting this split and having each user with
his/her own front end on their workstation and the back end on the server.
This is considered a requirement by most if not all Access developers and
experts.

Jim

"Sonic" <js*******@stat eindustrial.com > wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

The DB is not split, it is one file being run over the network. And as
far as records being added to a table and then later deleted, I don't
have anything being added or deleted in this DB. There are queries
being run, but nothing is being saved by these (outside of the normal
temp tables that Access uses when queries are being run). No make
tables queries are present in this DB.

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[J. Clay]

You should immediately work on getting this split and having each user with
his/her own front end on their workstation and the back end on the server.
This is considered a requirement by most if not all Access developers and
experts.

Jim

"Sonic" <js*******@stat eindustrial.com > wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

The DB is not split, it is one file being run over the network. And as
far as records being added to a table and then later deleted, I don't
have anything being added or deleted in this DB. There are queries
being run, but nothing is being saved by these (outside of the normal
temp tables that Access uses when queries are being run). No make
tables queries are present in this DB.

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[PC Datasheet]

BTW, were the words in my response to Sonic too big for your little mind to
comprehend or are you just plain ignorant? Here is the first part of my
response ----

"Check two things. First is the database split into a front end and a back
end with the backend on the server and a copy of the front end on each
station. It sounds like the database is one file and all the users are
running it over the network. This will give you the problems you describe.
Second, look at the programming in the database. Are records being
constantly added to temp tables and then later deleted. This is a sure cause
of bloating."

In the scheme of things I contributed to the newsgroup. Your response
contributed absolutely zilch. In the future, if you don't have anything to
contribute, don't waste the newsgroup's time and keep your little mind and
your ignorance closed.

Steve
PC Datasheet

"Arno R" <ar************ ****@tiscali.nl > wrote in message
news:42******** **************@ dreader2.news.t iscali.nl...

BTW, you never seem to learn that advertising is 'not done' in this ng.
BTW, can't you just share your knowledge just like others do?

Arno R.
"PC Datasheet" <no****@nospam. spam> schreef in bericht
news:lA******** *********@newsr ead2.news.atl.e arthlink.net...

BTW, if you ever need outside help with your database applications, please contact me at my email address below.

--
PC Datasheet