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.