Database bloat, emptying and filling tables each day

[rdougherty]

I am not finding the place where I read this (on this forum 1.5 years ago), but I was under the impression that one way to avoid database bloat after emptying and refilling the same table, such as at the beginning of each daily iteration of a job, was to avoid using:

docmd.openquery ("qry_name")

and instead use:

d.execute ("qry_name")

However, this does not always appear to be consistent, if it works at all (at avoiding bloat, that is).

Is this understanding incorrect, incomplete, or completely inaccurate? *Note, each day sees the database go from 750mb to 1.5gb and I would prefer it not to do this. I understand that splitting the database is a possibility, but I would prefer not to do this if there is another way.



Thanks

[moke123]

first thing you should do is split the database. Should you hit the 2 gig mark you will probably loose everything. You should also make constant backups at least until you figure out whats causing the db to double in size.

I dont think using the docmd method or db.execute method should make any difference.

I think we need a better description of what you have and what your doing.
Is this a temporary table? Does the size come down when you compact and repair?

One method is to use a temp database instead of a temp table.

[rdougherty]

I am gathering fresh datasets from a sales application, which is very unfriendly to complex reporting, and appending it anew each day to a table which has several updates etc. run against it to process the data. The tables get emptied out each time, so in that sense it is a temp table. But the structure is kept each time.

I am essentially using imports and linked tables (to spreadsheets) to first gather the records for combining into the master table. Once appended there, processes are run against it, as stated above.

Yes, the filesize does come down to 750mb each time I do so. I would simply prefer not to need to do this for every iteration of the job.

If I split the DB and have my tbl_Master (where everything is dumped for p rocessing) in a BE DB, will that prevent the BE from bloat, or just the FE? The BE could very quickly go over 2gb in a couple days, otherwise. What happens at 2gb anyway? I know that recent access version have higher limits, but what are they? (#records & filesizes)

Thanks,

[Micron]

Agreed that you should split this db, for several reasons.
1) the size of the Back End (data only) will come down relative to the db as a whole.
2) development on the forms/reports/queries side will not affect the be, especially if that development introduces corruption.
3) development on the fe can take place without locking down the be from concurrent use
4) it is the only safe way to share the tables in a multi-user environment
5) it is easier to back up the be on a frequent/continual basis
There are others, but if that doesn't convince you, probably nothing will.

If the data is entirely changed, then wiping the old and appending the new data is really your only choice with respect to having localized data (I presume there are no tables that you can link to instead). However, if you perform this process without using Transactions, you run the risk of deleting all and having the updates/appends fail, resulting in no data. Consider Updating what's there and appending what's new rather than what you're doing now.

[isladogs]

Agree with both moke123 and micron.
Splitting is an absolute necessity.
A temp database or 'side end' is very useful for processing the new data.

Using update for existing and append for new records is generally better than replacing all
BUT it will still cause bloat and will take longer to run.

Also be aware that repeatedly making and replacing tables will at some point cause instability and corruption may follow.

To correct a point in the first post no Access database can be larger than 2GB. New versions have the same limit. 64 bit has the same size limit as 32 bit.
Hit the limit and your file will be unusable and you may not be able to rescue the data from another source.
However SQL SERVER BE files can be much bigger. Even the free Express version can go to 10GB and paid versions far more than that.

[Micron]

Aside from design development, there are many reasons why an Access db bloats; from OLE storage to DAO objects that are not 'destroyed'. I used to think the latter one simply had an effect on pc memory allocation/release, but that is apparently not the case. I have to take the word of others on this as I religiously close rs objects in addition to destroying those and other DAO type objects. As an oft repeated cause, "Temporary tables" no longer seems to mean a 'never replaced, always present' table for storing temporary data that's undergoing processing (which is no more deleterious than any other table). It now seems to mean a table that exists one minute but is gone in the next minute. Then again, the longer you live, the more words seem to get twisted to mean something else. Till is my pet peeve lately. It IS NOT short for "until", OK?

While I never meant to suggest my method prevents bloat, I do believe constantly affecting the size of a table (10000 records one minute, 0 the next, 20000 the next, etc.) is more of a problem than the operations themselves. Albert Kallal (been around as long as Allen Browne and probably just as knowledgeable) states that record level locking (in Options>Client Settings, not at the form level) is a major cause for bloat. So the best advice I can give is Google it and make a list of the reasons you find, Then see if you're doing any of those 'bad' things.

[isladogs]

Albert Kallal (been around as long as Allen Browne and probably just as knowledgeable) states that record level locking (in Options>Client Settings, not at the form level) is a major cause for bloat.

I'd definitely agree regarding Albert's knowledge level - but wasn't aware about record level locking in client settings was an issue in that respect - though I use No Locks there myself
Do you have a reference where I can read more about this?

[Micron]

in this thread http://www.utteraccess.com/forum/ind...wtopic=2031686

[isladogs]

Many thanks. Just read his comments & it makes perfect sense. Glad I haven't ever used it

[rdougherty]

thanks to all of you. I will look into splitting the db so that everything functional is in the FE, while the biggest data table(s) sits in the BE. There is also one transitional table I use prior to posting the records to the Master table, which is no longer needed due to efficiencies I've applied. Removing that big table will help with size, of course.

A couple points about this DB:
It is not a user input DB at all.
Multiple users never use it simultaneously
It is a functional way to prepare reporting ONLY
A "shell" db with all functions, but no data, can easily be kept as a backup. No need to back up data itself, per se.

Also, I will look for and disable the record locking as suggested, and see if that makes any difference. EDIT: nvm, "Client SEttings/Advanced/Default record locking" appears to be off by default. That is not a contributing factor, I suppose.

Thanks again,