Compacting DB problem: DB gets huge!

[schredder]

Hi all

Designed a DB on my laptop. The DB compacts itself on exiting. Worked fine on my laptop, but now since the DB lies on a server (it's a frontend/backend solution) i get the message on exiting "Table 'TempMSysAccessObjects' already exists" and the DB doesn't compact.
The result is that the DB, each time it's been in use, gets bigger and bigger in size. Does anybody knows anything about this problem? Any help greatly appreciated.
Chris

 

[seaport]

"Compact on close" is a good feature for a small Access database. For a big one (more than 5M), sometimes Access cannot complete the compact job and the error message just confuses users.
My suggestion is not to use this feature. Since your system is a frontend/backend solution, you can put a front-end database on the server, and set up a batch program to overwrite the local front-end db with the front-end db every morning (assuming that there is no data saved in the front-end db). Since users get a brand-new front-end db every day, there is no need to compact.

Hope this can help you.

Seaport

 

[schredder]

Thanks for the hint, seaport. Since my db is about 15 megs in size i think i have to go for the solution u suggested. But, and now the but,....how do I set up such a batch program? sry, but my knowledge is limited to access and some vb.
Chris

 

[JeremyNYC]

Chris,

There's an example of a batch file on my website, in the developers' section.

But I would first look at something else: if you can, get rid of the temp tables all together. I've only had to use them twice in eight years of access development. You might want to start a different thread in the tables section to see what you can do about that. If you do, make sure to include a lot of info about how you're going about it now, and any thoughts you've had about other ways to approach it.

If you can't get rid of them, create a third database to hold your temp tables. This temp database will get blown away and recreated (from a copy) every time you need to recreate the table. That way, no bloating. It's a trusted solution to what you're facing.

And, once you do one of those two things, you won't have to compact the front end at all. It will grow a bit once people start using it, but that growth should level off quite quickly, at a reasonable level. This will reduce your exposure to corruption, as well, because it's just too easy for a user to get bored waiting for the compact to happen and give the three finger salute or kick out the power cord.

I don't have any good batch urls, but I should find some, as people keep asking once they read that page...gotta get on that.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[schredder]

Thanks Jeremy. Since i dont have any experience at all with batch files and so i think i will some time to get familiar/read into this. So if u find/know any got urls on that topic please let us know.
Chris

 

[seaport]

A batch file is a DOS bat file. You can create it with notepad, write the code like "copy I:\frontend.mdb c:\production" and save as it as "EveryDayUpdate.bat".

Then you can put this batch file to the startup menu of every user's computer. This batch file will run every time a user logs on a computer.

seaport

 

[bostonfrog]

Seaport, I'd like to run a similar batch file like the one you listed above in Access but just not at Startup. Maybe just run it when people exit the database. What line of code could I use in VBA that would execute this batch file? Thanks.
-- Michel

 

[seaport]

Michel,

I donot think there is any event for exiting Access database. So you cannot make the batch file run when a user exits database.

Another way to run the batch file is to set up a scheduled task, making the file run every morning, say 5am. "Schduled tasks" is part of "system tools" in Windows 2000. You can find it in "Start Menu".

Seaport

 

[jhowley58]

Hi,

I've got an msAccess2000 tables-only database(60+ tables) on a w2000 server. This is accessed by up to 10 client desktops and/or laptops using a fairly big msAccess2000 'system' database containing loads of VB, queries, Forms (120), a few local temporary tables which are deleted after use, and several local permanent tables for monitoring purposes.

The server database expands very slowly. At present it stands at 7mb - so that is no problem at all. However, the client systems grow like Topsy!. The base size is 19mb and it grows over a day to maybe 100mb in some cases. I've selected the 'compact on close' option but it takes too long on the kind of harware being used.

I use ADO and .mdb database files.

I split off the local permanent tables into a separate linked tables database but the admin hassle became a nightmare.

My question is twofold:

1.What operations cause such a build up in a Access database? Is it solely the deletion and creation of temporary tables or what?

2. When a client database is going through the compaction process does it perform any activity on the linked server database?

Many Thanks,

JohnH

 

[AvGuy]

http://support.microsoft.com/?kbid=289562

 

[JeremyNYC]

A couple of points...one is that Access is not a client/server database. It's just words, true, but there are some (not me) who will scoff at you for such usage. The back end in access does no processing of the data, it all happens in the front end. In a true client server database, the processing of the data is done, to the extent possible, in the back end.

Not an issue here, just thought I'd mention it.

Next, temp tables do indeed sort of kill you on the expanding database issue. The best solution is to keep your temporary tables in a temporary database. You can build a new one from scratch each time or you can have a template for that database that you make a copy of each time. You link to that one in addition to linking to the main back end. You can keep that database on the user's PC, as it will be faster that way.

And no, compacting the front end doesn't do anything at all to the back end.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jhowley58]

Jeremy,

I never said that Access was a Clent/server database. I do, however, have client desktops accessing a server. As for my being scoffed at(not by you): fools do not suffer me gladly.

I am very well aware that I am using Access in a fileserver configuration. I have tried your suggestion about using a local linked database for the temporary tables. If you had read my post properly you would have seen that as well.

Cheers,

JohnH

 

[jhowley58]

HI AvGuy

Thanks for the link. I believe that article refers only to DAO coding. I translated the systems to ADO some time back.

Funny thing that. I kept getting failures when I went to close recordsets under ADO. Maybe I should investigate along those lines more.

JOhnH

 

[jhowley58]

AvGuy,

At one point I do open an adodb recordset to obtain the number of records. I was able to close that successfully and it has decreased the 'bloating' to some extent. So thanks for that.

I guess I must go back to using a third and local database for the temporary tables I use.

JOhnH.

 

[JeremyNYC]

John,

Some people do, indeed, cause themselves much suffering.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.