467,134 Members | 1,023 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

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

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.

Nov 13 '05 #1
  • viewed: 4843
Share:
14 Replies
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******@pcdatasheet.com
www.pcdatasheet.com


"Sonic" <js*******@stateindustrial.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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.

Nov 13 '05 #2
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...

Nov 13 '05 #3
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*****************@newsread2.news.atl.earthl ink.net...

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

--
PC Datasheet

Nov 13 '05 #4
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.

Nov 13 '05 #5
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.

Nov 13 '05 #6
"Sonic" <js*******@stateindustrial.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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

Nov 13 '05 #7
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*******@stateindustrial.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.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 =----
Nov 13 '05 #8
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*******@stateindustrial.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.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 =----
Nov 13 '05 #9
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.tiscal i.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*****************@newsread2.news.atl.earthl ink.net...

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

--
PC Datasheet


Nov 13 '05 #10
Well Steve, are we going to be offensive here ?

My response was really meant to be a contribution to this group, because this group can do
very well without your explicit advertising.
Your sig alone would be more than enough. (and you know that, because this has been told
you many times)

Well Steve, so I meant by the word *just* that it would be *just* enough when you share
your knowledge and *just* leave the advertising behind.
Maybe that's *just* too difficult to grasp for a mind like yours...

Who is 'playing' ignorant here in this ng ?
Asking questions while using other identities (often female) that's 'great minded' heh?

Well Steve, I just feel sorry for you...

Arno R.
"PC Datasheet" <no****@nospam.spam> schreef in bericht
news:_l*************@newsread3.news.atl.earthlink. net...
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.tiscal i.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*****************@newsread2.news.atl.earthl ink.net...
>
> BTW, if you ever need outside help with your database applications, please > contact me at my email address below.
>
> --
> PC Datasheet






Nov 13 '05 #11
Arno R wrote:
My response was really meant to be a contribution to this group, because this group can do
very well without your explicit advertising.


There's nothing wrong with the sig. His blatent solicitation was a bit
over the top, however.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #12
Sonic wrote:
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.


Sonic, you're missing the major point. You should not have multiple
instances of the same mde open at any one time. Make individual copies
for each user and have them operate it from their PC.

Here's what I do in such cases to prompt users to do their own
compacting when the mde/mdb gets a bit large.

I use a custom menu and create a menu or tool bar (depends on what you
want). When in edit menu mode I hold the control key down (make sure
you do this or you will regret it -see hereafter as to why) and click on
the Compact & Repair menu item (found in Tools->Database
Utilities->Compact & Repair) and drag it to my tool bar.

If you don't hold the control key down when doing this, your standard
menu "loses" the Compact & Repair menu item!

I then change the caption to "Optimization of <App Name> is STRONGLY
Recommended!", or something similar, and put an image there - my
favourite is the little bell from the Change button Image menu item when
right clicking a menu item in customize menu mode.

Next, I run a routine like the following on start up - usually on a
splash form's on open event, though I suppose you could put it in a
module and run it from an autoexec macro if you wanted:

Dim strPath as string 'location of front end
Dim lngSize as Long 'size of front end

'Check for overall file size
'If larger than recommended size, make compact menu item visible
'otherwise, make it invisible

strPath = Access.CurrentDb.NAME

lngSize = FileLen(strPath)

If lngSize > 5000000 Then

CommandBars("mnuMain").Controls.Item(6).Visible = True

Else

CommandBars("mnuMain").Controls.Item(6).Visible = False

end if

In the above example, the commandbars lines assume there are 5 other
menu items on my custom menu bar "mnuMain" before the compact menu item.
Adjust as per your own application.

With respect to the size the FE must be before the user is prompted,
what I usually do is compact the finished front end and then set the
lngsize "trigger" above to twice that of the compacted size.

The above is for a front end. Like your SQL back end, I usually write
my apps against non Jet (in my case, Oracle) back ends. Similar code
can be constructed for maintenance of Jet back ends, though of course
the menu on action function will be a procedure using the
CompactDatabase command....

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #13
"Sonic" <js*******@stateindustrial.com> wrote:
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.


HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)
http://support.microsoft.com/?kbid=210297
ACC: How to Detect User Idle Time or Inactivity (Q128814)
http://support.microsoft.com/?kbid=128814

However we found that the code which runs on the timer event must be disabled for the
programmers. Otherwise weird things start happening when you're editing code.

Also print preview would sometimes not allow the users to run a menu item to export
the report to Excel or others. So you had to right click on the Previewed report to
get some type of internal focus back on the report so they could then export it.
This was also helped by extending the timer to five minutes.

The downside to extending the timer to five minutes was if a person stays in the same
form and at the same control for considerable parts of the day, ie someone doing the
same inquiries, the routine didn't realize that they had actually done something.
I'll be putting in some logic sometime to reset this timer whenever they do something
in the program.

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
Nov 13 '05 #14
"Sonic" <js*******@stateindustrial.com> wrote:
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.


You really, really want to split the MDB into a front end containing the queries,
forms, reports, macros and modules with just the tables and relationships. The FE is
copied to each network users computer. The FE MDB is linked to the tables in the
back end MDB which resides on a server. You make updates to the FE MDB and
distribute them to the users, likely as an MDE.

See the "Splitting your app into a front end and back end Tips" page at
http://www.granite.ab.ca/access/splitapp/ for more info. See the Auto FE Updater
downloads page http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. It also supports Terminal Server/Citrix quite nicely.

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
Nov 13 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by OutsiderJustice | last post: by
37 posts views Thread by ajikoe@gmail.com | last post: by
17 posts views Thread by =?Utf-8?B?R2Vvcmdl?= | last post: by
reply views Thread by Sabri.Pllana@gmail.com | last post: by
14 posts views Thread by =?ISO-8859-1?Q?Tom=E1s_=D3_h=C9ilidhe?= | last post: by
4 posts views Thread by =?Utf-8?B?SGVucmlrIFNjaG1pZA==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.