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."

http://support.microsoft.com/default.aspx?scid=kb;en-us;197953

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.

Splitting your mdb should help significantly.

Jet needs to do a lot of work to parse an SQL string and figure out how to obtain the requested recordset. It has to analayze the available indexes and determine if any of them are usable, it needs to look at the row counts and decide which table to process first, and so on. It takes time and workspace. And as we know, Access does not clean up its workspace until the db is compacted. That's why you see so much bloat in your databases. When you use querydefs, the process of binding the query happens once when the query is saved (and also again when the db is compacted) rather than EVERY time the query is executed.

This also applies to SQL strings used as recordsources in forms and reports and to domain functions. All of these should be referring to stored querydefs, usually with parameters. The only time you should be building SQL on the fly is when the actual SQL string needs to be variable. For example a TOP n query where n needs to vary must be built in code because n can't be a parameter, but a query with a where clause that is fixed except for the actual value being searched for should be a stored querydef.

Splitting your db won't solve your bloat problem since you'll be bloating the be by constantly adding and deleting large numbers of rows and the front end will still bloat due to your use of VBA to build SQL strings on the fly. However, if you change your approach to SQL, you can substantially reduce the bloat in the fe.

The db should be split regardless since it will make change management easier and reduce the potential for corruption in a shared environment.

Thank you both very much for your help. Let's see if I get this straight. I am indeed using SQL on the fly many times in the database so this is the primary cause of my bloat then?. This is an example of one of my functions that I run:

Function EditWizardCatsLayersAndCarFilesCombined()
'This code will correct the radio position for the WizardCatsLayersAndCarFilesCombined File

Dim dbs As DAO.Database
Dim recWizardCatsLayersAndCarFilesCombined As DAO.Recordset
Dim MTX As String
Dim FREQ As String
Dim recChannel As DAO.Recordset
Dim tdf As TableDef
Dim Count As Integer
Dim vchinv_recordcount As Integer
Dim qryTempQuery As DAO.QueryDef
Dim modifier As Integer
Dim DA_modifier As Integer
Dim DO_modifier As Integer
Dim SQL As String

Set dbs = CurrentDb
Set recWizardCatsLayersAndCarFilesCombined = dbs.OpenRecordset("SELECT * FROM WizardCatsLayersAndCarFilesCombined")

Do Until recWizardCatsLayersAndCarFilesCombined.EOF
If IsNull(recWizardCatsLayersAndCarFilesCombined![MTX]) Then GoTo 2
MTX = recWizardCatsLayersAndCarFilesCombined![MTX]
FREQ = recWizardCatsLayersAndCarFilesCombined![FREQ]
SQL = "SELECT [Field2] FROM VCHINV WHERE (([Field1] = '" & MTX & "') And ([Field3] = '" & FREQ & "'))"
Set recChannel = dbs.OpenRecordset(SQL)
If recChannel.EOF = True And recChannel.BOF = True Then GoTo 2
If IsNumeric(MTX) = True Or InStr(MTX, "X") Then
DA_modifier = 3: DO_modifier = 3
ElseIf InStr(MTX, "Y") Then
DA_modifier = -26: DO_modifier = -28
ElseIf InStr(MTX, "Z") Then
DA_modifier = -55: DO_modifier = -59
End If
If recWizardCatsLayersAndCarFilesCombined![Channel] = "1" Then GoTo 2
If recWizardCatsLayersAndCarFilesCombined![Channel] = "2" And recWizardCatsLayersAndCarFilesCombined![DAorDO] <> "DO" Then: GoTo 2
If recWizardCatsLayersAndCarFilesCombined![DAorDO] = "DO" Then
With recWizardCatsLayersAndCarFilesCombined
.Edit
![Channel] = recChannel.Fields(0) + DO_modifier
![Channel2] = recChannel.Fields(0) + DO_modifier
.Update
End With
GoTo 2
Else
With recWizardCatsLayersAndCarFilesCombined
.Edit
![Channel] = recChannel.Fields(0) + DA_modifier
![Channel2] = recChannel.Fields(0) + DA_modifier
.Update
End With
GoTo 2
End If
2 recWizardCatsLayersAndCarFilesCombined.MoveNext
Set recChannel = Nothing
Count = Count + 1
Loop

100

recWizardCatsLayersAndCarFilesCombined.Close
dbs.Close
Set recWizardCatsLayersAndCarFilesCombined = Nothing
Set recChannel = Nothing
Set dbs = Nothing

End Function

What sort of efficient recommendations are there for replacing "SQL on the fly" if this is what's causing my database bloat? Thanks.

Maybe this helps - Bruce Wayne?

Hello NJudson,
I am a newbie myself but from what I understand what you are doing is "importing several text files to perform some function" which cases bloating on your backend database because the data being imported has a range between 4000 to 12000 records. Why don't you use a copy of the database that is empty and import the data into it. Then run the various functions and when you get the result you want append the result to the backend table. As for the blank copy of the database use it to do the various functions. I think you can even automate the process if you write a script on paper and follow the steps from beginning to end then using the two table methods you save time and bloating issues.
Just a newbie's suggestion, if it is worth anything to you I'm happy it helps you. Good Luck!