How can I keep my database from inflating? I believe it has to due with defining objects....

[joeclueless2]

Hi,

I have an on click event procedure that inflates the file size of the MS Access 2007 database. I think it has to do with writing to the open record set... This is confusing!?!

Can someone take a look at the code and see if there may be a way to keep the file size manageable?

THANK YOU!!!

Private Sub Command0_Click()

'Sets the start of the process
Dim Response As VbMsgBoxResult
Response = MsgBox("Click Okay to start Matriculation Check ", vbQuestion + vbYesNo)
If Response = vbNo Then Exit Sub

'Defines types for variables
Dim Grade As Integer
Dim StreetNum As Integer
Dim PreDir As String
Dim StreetName As String
Dim Suffix As String
Dim ZCode As String
Dim RecID As Long

'Drop TempM5 table and create new TempM5 (for 1-N version)
On Error Resume Next
CurrentDb.Execute " drop TABLE [TempM5]"
On Error GoTo 0
CurrentDb.Execute "CREATE TABLE TempM5 (POLYID Text, SCHOOL_ID Text, NAME Text, LO_GRD Integer, HI_GRD Integer, BY_APP Text, ChoiceCode1 Text, ResID Long, SchoolCnt Integer, PolyCnt Integer);"


'Open Database and define Recordset
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("NextSchoolProcessed")

Do Until rst.EOF

'Delete Temp Tables
On Error Resume Next
CurrentDb.Execute " drop TABLE [TempM1]"
CurrentDb.Execute " drop TABLE [TempM2]"
CurrentDb.Execute " drop TABLE [TempM3]"
CurrentDb.Execute " drop TABLE [TempM4]"
On Error GoTo 0

'Process records
'Collect Variables
Grade = rst![GradeLevelID  2014-15]
StreetNum = rst!StreetNumber
PreDir = rst!Direction
StreetName = rst!Street
Suffix = rst!Type2
ZCode = rst!Zip
RecID = rst!ID

'Check to see if fields are accessible
'MsgBox "Grade = " & Grade
'MsgBox "StreetNum = " & StreetNum
'MsgBox "PreDir = " & PreDir
'MsgBox "StreetName = " & StreetName
'MsgBox "Suffix = " & Suffix
'MsgBox "ZCode = " & ZCode

'Check House Number for odd or even side of street and use appropriate query to make TempM1 Table
If StreetNum Mod 2 Then
 'MsgBox "You've entered an odd number." & StreetNum
CurrentDb.Execute "SELECT [tblStreetRanges14-15].PolyID, [tblStreetRanges14-15].SIDE, [tblStreetRanges14-15].BEG_HSE, [tblStreetRanges14-15].END_HSE, [tblStreetRanges14-15].PREFIX, [tblStreetRanges14-15].NAME, [tblStreetRanges14-15].SUFFIX, [tblStreetRanges14-15].SUFFIX_DIR, [tblStreetRanges14-15].CITY_NAME, [tblStreetRanges14-15].ZIP, [tblStreetRanges14-15].SCH_YR, [tblStreetRanges14-15].LAUSD INTO TempM1 FROM [tblStreetRanges14-15] WHERE ((([tblStreetRanges14-15].SIDE)='O' Or ([tblStreetRanges14-15].SIDE)='B') AND (([tblStreetRanges14-15].BEG_HSE)<=" & StreetNum & ") AND (([tblStreetRanges14-15].END_HSE)>=" & StreetNum & ") AND (([tblStreetRanges14-15].NAME)='" & StreetName & "') AND (([tblStreetRanges14-15].SUFFIX)='" & Suffix & "') AND (([tblStreetRanges14-15].ZIP)=" & ZCode & ")) ORDER BY [tblStreetRanges14-15].PolyID,[tblStreetRanges14-15].NAME;"

Else
 'MsgBox "You've entered an even number." & StreetNum
CurrentDb.Execute "SELECT [tblStreetRanges14-15].PolyID, [tblStreetRanges14-15].SIDE, [tblStreetRanges14-15].BEG_HSE, [tblStreetRanges14-15].END_HSE, [tblStreetRanges14-15].PREFIX, [tblStreetRanges14-15].NAME, [tblStreetRanges14-15].SUFFIX, [tblStreetRanges14-15].SUFFIX_DIR, [tblStreetRanges14-15].CITY_NAME, [tblStreetRanges14-15].ZIP, [tblStreetRanges14-15].SCH_YR, [tblStreetRanges14-15].LAUSD INTO TempM1 FROM [tblStreetRanges14-15] WHERE ((([tblStreetRanges14-15].SIDE)='E' Or ([tblStreetRanges14-15].SIDE)='B') AND (([tblStreetRanges14-15].BEG_HSE)<=" & StreetNum & ") AND (([tblStreetRanges14-15].END_HSE)>=" & StreetNum & ") AND (([tblStreetRanges14-15].NAME)='" & StreetName & "') AND (([tblStreetRanges14-15].SUFFIX)='" & Suffix & "') AND (([tblStreetRanges14-15].ZIP)=" & ZCode & ")) ORDER BY [tblStreetRanges14-15].PolyID,[tblStreetRanges14-15].NAME;"
End If

'Get Grade Appropriate Schools List by PolyID
CurrentDb.Execute "SELECT [tblSchoolsPerPoly14-15Cats].POLYID, [tblSchoolsPerPoly14-15Cats].SCHOOL_ID, [tblSchoolsPerPoly14-15Cats].NAME, [tblSchoolsPerPoly14-15Cats].LO_GRD, [tblSchoolsPerPoly14-15Cats].HI_GRD, [tblSchoolsPerPoly14-15Cats].BY_APP, [tblSchoolsPerPoly14-15Cats].ChoiceCode1 INTO TempM2 FROM TempM1 INNER JOIN [tblSchoolsPerPoly14-15Cats] ON TempM1.PolyID = [tblSchoolsPerPoly14-15Cats].POLYID WHERE ((([tblSchoolsPerPoly14-15Cats].LO_GRD)<=" & Grade & ") AND (([tblSchoolsPerPoly14-15Cats].HI_GRD)>=" & Grade & "));"

'Add fields to TempM2 for writing to proper RecID, PolyID Count, and School ID
CurrentDb.Execute "ALTER TABLE TempM2 ADD COLUMN ResID Long"
CurrentDb.Execute "ALTER TABLE TempM2 ADD COLUMN SchoolCnt Integer"
CurrentDb.Execute "ALTER TABLE TempM2 ADD COLUMN PolyCnt Integer"

'Get School ID Count into TempM3
CurrentDb.Execute "SELECT Count(TempM2.SCHOOL_ID) AS CountOfSCHOOL_ID1, TempM2.SCHOOL_ID INTO TempM3 FROM TempM2 GROUP BY TempM2.SCHOOL_ID;"

'Get PolyID Count into TempM4
CurrentDb.Execute "SELECT Count(TempM2.POLYID) AS CountOfPOLYID, TempM2.POLYID INTO TempM4 FROM TempM2 GROUP BY TempM2.POLYID;"

'Update TempM2 RecID
CurrentDb.Execute "UPDATE TempM2 SET TempM2.ResID = " & RecID & ";"

'Update TempM2 SchoolID Count
CurrentDb.Execute "UPDATE TempM2 INNER JOIN TempM3 ON TempM2.SCHOOL_ID = TempM3.SCHOOL_ID SET TempM2.SchoolCnt = [CountOfSCHOOL_ID1];"

'Update TempM2 PolyID Count
CurrentDb.Execute "UPDATE TempM2 INNER JOIN TempM4 ON TempM2.POLYID = TempM4.POLYID SET TempM2.PolyCnt = [CountOfPOLYID];"

'Update Student Record with grade appropriate School ID, school id count, poly id count on only the appropriate RecID
CurrentDb.Execute "UPDATE TempM2 INNER JOIN NextSchoolProcessed ON TempM2.ResID = NextSchoolProcessed.ID SET NextSchoolProcessed.RESLOCN = [TempM2].[SCHOOL_ID], NextSchoolProcessed.SchoolCnt =[TempM2].[SchoolCnt], NextSchoolProcessed.PIDCount = [TempM2].[PolyCnt];"
CurrentDb.Execute "INSERT INTO TempM5 ( POLYID, SCHOOL_ID, NAME, LO_GRD, HI_GRD, BY_APP, ChoiceCode1, ResID, SchoolCnt, PolyCnt )SELECT TempM2.POLYID, TempM2.SCHOOL_ID, TempM2.NAME, TempM2.LO_GRD, TempM2.HI_GRD, TempM2.BY_APP, TempM2.ChoiceCode1, TempM2.ResID, TempM2.SchoolCnt, TempM2.PolyCnt FROM TempM2;"




   rst.MoveNext
Loop

rst.Close
Set rst = Nothing

'Query MiSiS Results to MPD results for corroboration and update the AGREE Field to Yes or No
CurrentDb.Execute "UPDATE NextSchoolProcessed SET NextSchoolProcessed.Agree = 'Yes' WHERE (([RESLOCN]=[BoundarySchoolLocation]));"
CurrentDb.Execute "UPDATE NextSchoolProcessed SET NextSchoolProcessed.Agree = 'No' WHERE (([RESLOCN]<>[BoundarySchoolLocation]));"


'Display Finished Indicator
MsgBox "Done!", vbInformation


End Sub

 

[dhookom]

If you are creating and deleting temporary tables you might consider creating a temporary Access file, build your temporary tables in the temp file, and delete the entire file when done.

You could also compact/repair to reduce the file size.

Duane
Hook'D on Access
MS Access MVP