View Single Post
Old 05-02-2003, 10:49 AM   #4
NJudson
Who farted?
 
NJudson's Avatar
 
Join Date: Feb 2002
Location: Indiana
Posts: 297
Thanks: 0
Thanked 0 Times in 0 Posts
NJudson
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.

Last edited by NJudson; 05-02-2003 at 10:52 AM.
NJudson is offline   Reply With Quote