question about mde file

[gerard1979]

For the company I'm making a db.
A usual db is growing (in Mb) when you don't comprime and repair it often. Is the same true for a mde file?
When it is also growing, how to handle this?

Thnx in advance,
Using Access 2002

Gerard

 

[jes]

Hi Gerard,

My solution to this is to have two database files - the mde and the mdb, with the mdb holding the tables and the mde linking to those.

Then it's just a case of compacting the mdb as and when required.

I find it's useful to separate the program and data in any case as it makes it easier - and smaller - to distribute any changes to the program.

Hope that's some help.

Jes

 

[gerard1979]

Hi Jes,

maybe I don't explain myself clear enough.
I have a backend file (.mdb) and a fore-end file (.mde)
The problem is:
the backend file doesn't grow much. Only when new data comes in it.
the foreend file is shared by 5 a 10 users in a shared network path.
This file is .mde but grows up to 100 Mb in no time.
I can't understand how this is possible, and really appriciate advice,
thnx,
gerard

 

[jes]

Hi Gerard,

Well, you've got me stumped. The mde file should not grow if it has no tables attached - at least that's my understanding of the way it works. Does your program allow users to create their own queries/reports/forms through code as that might explain it?

The only other thing I could suggest is that not all the tables are held in the mdb or maybe you've not linked all the tables - I have made that mistake during testing myself, ie, used local tables at test time and forgotten to link back to the main database afterwards.

Can you let me know how this goes as I'd be interested to find out what the problem is.

The only other suggestion I've got is contacting TLBroadbent who is a bit of a whiz with access. He might have an answer.

Best of luck,

Jes

 

[petermeachem]

I would guess you are creating temp tables in the mde, in which case just compact it. Did you say 5 - 10 people are all accessing the same mde? Or do they have a copy locally linked to the mdb. If the first, and you are creating temp tables, something is going to get confused pretty soon. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[gerard1979]

I think we're coming to the source;
I linked all the tables from backend to frontend.
There is one table I use for mailings. It's a temporary table, the table never delete, but only the data is deleting every time it's used again.
After linking ALL the tables I make the mde - file.
At this moment it's growing from 3 mb till 6 mb in less than a week.
I can't understand the fact of growing...
Can the temporary table cause the evil?

tnx in advance,
gerard

 

[gerard1979]

One thing more
All the users are linked to the same mde file
every desktop has an link N:\Acquis\user.mde

so I have to fresh up only one link.

If this is the problem, how can I solve this pretty clean?

Gerard

 

[jes]

Hi Gerard,

My idea there would be to either:-

1) Delete the contents of the table after you've finished with it as opposed to before you start using it, so that way the info will never be hanging around.

2) Create and Delete the entire table as you require it. Some basic SQL code will do that for you. Alternatively, set up a housekeeping script that will nuke the table for you on demand.

3) Put the mailing table into the mdb. Unless there's a definite reason why it should be in the mde, put it with all the others.

Hope that helps,

Jes

 

[oharab]

As has been mentioned before, you have 2 problems here.
1: Your users are all using the same front end file
2: your users are adding and deleting data from within that file.

If you have more than 1 user on a database, the best thing you can do is have a back end for data on a central drive (as you already have) and seperate front ends on each users machine. If issuing updates to people is an issue, contact me again & I'll take you through some options this. This will cut down the risk of corruption dramatically.

If your users are creating lots of temporary tables, it might be worth considering creating a temporary database file at runtime with the temporary tables in it and linking to them as needed. When the user leaves the database the temp file is deleted.
This will mean there is no possibility of users deleting the temporary table that someone else has created and your database will be more stable and faster to use.

HTH

Ben

 

[gerard1979]

Thnx for the quick reactions both of you.
there is only one TempTable and this is in the mdb (backend file).
I know it's optional to create and delete the tables other way I do now, but I can't understand how this will increase the database dramatically.
I'm very interested in options to handle updates quick to the users.
Critically looking at ur answers the shared mde must be the only logical reason for corruption and increasing.

 

[jes]

Hi Gerard,

Well, you've exhausted all the options I can come out with so I'll have to agree and say that you'll have to distribute the mde to individual machines / accounts. I do think though that sort of defeats the object of an mde file.

Best of luck,

Jes

 

[Geekmomz]

Gerard,
If it is an option, updating the users' .mde files in their login scripts is an excellent option. Usually users can be trained to log off at the end of their day and on again at the start, and you can be certain that their .mde file is not currently in use if they are just now logging in.

Another option is to distribute a custom "shortcut" to the .mde file which actually copies it to the local machine before running it each time. The danger with this choice is that some users like to open more than one copy of the same database.

Good luck finding your best solution.
Geekmomz

 

[gerard1979]

Thnx all for the suggestions...

 

[oharab]

Having individual front ends is the only sensible thing to do. To make sure my users are using the latest version I have a version number in a table in the database. When the user opens the file it checks their version against a central version. When a later version is found the database quits and runs a short VBScript which copies the new file over the old and opens it.
The reason your data file is expanding is because as you delete data, the space it took up is not regained until you compact the file. If you create your temporary tables externally you eliminate this bloat.

Ben

 

[jes]

Hi Ben,

Compacting? Well, it's bloody obvious really isn't it? You could have mentioned that a while ago and saved my fingers some typing stress

Oh well then Gerard, you'd better start reading the threads on programatically compacting db's.

Regards,

Jes

 

[CVigil]

another quick thought, another option to add to your list (although the thread's last post is some months old now) ... You could use a recordset instead of a table. This keeps the info stored in memory instead of in the database file, and your file never grows.

Of course, the advisability of using this technique depends on the amount of data you're storing, the length of time that the data must exist (given that you've said this is a temp table), how many different objects (forms, queries, modules, etc.) in your database need to access the data, and the amount of memory available to the computers running your database at the time they're running it (accounting for other simultaneous uses of memory).

---

By the way, Gerard1979, a question about this mailing data that is deleted and recreated every time: Is the data unique to each user, but always mostly the same for each user? In other words, for your specific application, is it worth keeping the data for each user (and perhaps letting them modify their own data-set)? If the answer to these questions is "yes", then perehaps it would be good to keep A) a permanent table in the back end, each record associated with a user, OR B) a table or equivalent on each user's machine with their data. For instance, if the "table [you] use for mailings" is a list of contacts' addresses for the current user, and the list of contacts for a given user doesn't change (much) -- each user may have a distinct/unique list, but the lists themselves don't change (much) -- then why not store the data long-term and let them make changes only as needed?

On the other hand, if the data changes each session (or has the potential to), if the data has to be re-gathered or derived on the fly all the time, then you probably don't want to store it.

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot