Go Back   Access World Forums > Microsoft Access Discussion > General

Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Prev Previous Post   Next Post Next
Old 05-02-2003, 08:37 AM   #1
Who farted?
NJudson's Avatar
Join Date: Feb 2002
Location: Indiana
Posts: 297
Thanks: 0
Thanked 0 Times in 0 Posts
Holy Bloated Database Batman!!!

I'm suffering from a terrible case of bloated database. I'm using access 2000 and the purpose of this db is to import several text files and perform a series of functions to modify some of the data on them. The tables in question are typically between 4000 to 12000 records. The database is located on a main server tied to a small network. Typically before I start the program I reboot my computer and compact the database which is approx 8mb after compacting. About 6-8 minutes into the program it will have run 5 of 7 functions but the database gets so bloated (about 100mb) that I usually have to stop it, compact it again then resume the last 2 functions. I've been searching on how to fix this and this is more or less what I've found:

1) double checked my code and made sure I've closed out all recordsets and set everything = nothing when I'm done

2) compact database regularly
-I seem to do this more than regularly

3) split database
-This is the next thing I will try

4) read an article on Microsoft that states:
"When you use Data Access Objects (DAO) to create objects in a database, the size of the database increases substantially during the operation."

"Use SQL Data Definition Language (DDL) statements rather than DAO to create or modify database objects."


I never heard of this DAO bloat thing until now. Over the last couple years I've written several databases for my job and I've always used DAO in everything with a little SQL here and there. I've always had bloating problems with most my databases but nothing as bad as the latest one I'm working on which has prompted me to research the problem more now.

My questions are:
Will splitting my database(s) be sufficient for easing the bloat?
If I split the database does it slow process down much?
Should I go back and re-write my code to try and change my DAO format to SQL?
Should I learn ADO and trying writing with that?
What would be better ADO or SQL?

Please forgive my ignorance or lack of knowledge on this. I know a little DAO so that's what I've used for some time now. I'm trying to learn more on how to write SQL and I know next to nothing about ADO. Thank you for taking the time to read my semi-book here. I appreciate any advice.

NJudson is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

All times are GMT -8. The time now is 03:37 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World