Compact and Repair Concerns! (1 Viewer)

[gunslingor]

Okay, so I've been developing this DB for 6 months now. It has slowly been growing and currently sits at 32MB in size (the front end only). I have been doing the occational compact and repairs, which usually drops the file size by about 5MB any time I do it. However, this morning I did a compact and repair and it dropped the file size down to 4MB from 32MB!!!!

Why did this happen, why did it happen now, what could I possibly have done differently? The DB seems to operate fine, but I'm scared to use this new version because of the massive reduction in size. There isn't anything in the front end other than forms, code, reports and macros. 4MB is what I would expect for the level of data in there, and its always been a concern that its been beteween 25 and 32 MB for many months now... but what could I have changed to result in this reduced file size?

Please help.

 

[JHB]

...It has slowly been growing and currently sits at 32MB in size (the front end only).

In which occasion did it grow, when you run it or?
Do you run/have queries in your Front End database?
Do you create temporary tables in you Front End database?

 

[gunslingor]

In which occasion did it grow, when you run it or?

-Really, I meant its been growing as I added code and develop it. But I am a computer engineer and for the amount of text and logical data in there, in the general sense, seems to me to be about 5MB or so worth of data and 32MB always seemed way to high.
-I use a lot of DAO recordsets in my code. For a while there I forgot to include the rec.close at the end but months ago I thought I went thru and corrected. Its possible I added the rec.close to one function that was missing it and just forgot prior to compacting yesterday... could this be the cause of the reduction? Also, what other possibilities are there, for future reference.

Do you run/have queries in your Front End database?

-Zero Access type queries, the kind that show up in the navigation pain like forms and reports. However, thru-out the DB, via VBA and via the control/form properties, there are tons of Queries being ran against the DB.

Do you create temporary tables in you Front End database?

None at all.

Note the front end only has linked tables + 1 single field single value table for version comparison and control. Note the backend, which only has tables and data, is 6.5MB and the front end is now 4.71MB. Again, everything SEEMS to be operating correctly, but I'm afraid to continue development in this version. I do have backups though, I just don't want to put in work that may be lost. Note that the last backup I took was 4 days ago and there the front end was 32MB.

Note: I'm doing textual comparisons against older versions and this presented something interesting I forgot about. Yesterday, before I compacted, something weird started happening. I would usually type "me.textbox_name.value" and auto complete would make it "Me.Textbox_Name.Value". However, yesterday, randomly and prior to compacting complete would do the following "Me.Textbox_Name.value". Note the lowercase "value". I did a check and now every single 'value' keyword is lowercase. This I highly suspect is the cause since it was the only bizzar behavior prior to compacting that was happening, but why and what is happenning?

I also removed a lot of code similar to this on three controls, the previous guy wrote this inefficiently:

Private Sub Fields1_AfterUpdate()
DoCmd.SetWarnings False

If Me.Fields1.Value = "Choose A Field" Then
    Me.FieldsCombo1.Visible = False
    Me.FieldsCombo2.Visible = False
    Me.FieldsCombo3.Visible = False
    Me.Fields2.Visible = False
    Me.Fields3.Visible = False
    Me.FieldsCombo1.Value = Null
    Me.FieldsCombo2.Value = Null
    Me.FieldsCombo3.Value = Null
    Me.Fields2.Value = "Choose A Field"
    Me.Fields3.Value = "Choose A Field"
    Me.UNIDs.RowSource = "SELECT [UNID] FROM CYBER_ASSETS ORDER BY UNID"
    Me.UNIDs.SetFocus
    Me.UNIDs.ListIndex = 0
    Exit Sub
End If

Me.FieldsCombo1.Visible = True
Me.FieldsCombo1.Value = Null

Select Case Me.Fields1.Value
    Case "Unique ID"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [UNID] FROM CYBER_ASSETS"
    
    Case "Company"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [COMPANY] FROM CYBER_ASSETS"
    
    Case "Department"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [DEPARTMENT] FROM CYBER_ASSETS"
    
    Case "Site"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [SITE] FROM CYBER_ASSETS"
    
    Case "Room"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [ROOM] FROM CYBER_ASSETS"
    
    Case "Device Type"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [DEVICE_TYPE] FROM CYBER_ASSETS"
    
    Case "Device Status"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [DEVICE_STATUS] FROM CYBER_ASSETS"
    
    Case "Identifier"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [IDENTIFIER] FROM CYBER_ASSETS"
    
    Case "Host Name"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [HOSTNAME] FROM CYBER_ASSETS"
    
    Case "Manufacturer"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [MFR] FROM CYBER_ASSETS"
    
    Case "Model"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [MODEL] FROM CYBER_ASSETS"
    
    Case "OS_Line"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [OS_Line] FROM CYBER_ASSETS"
    
    Case "ESP"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [ESP] FROM CYBER_ASSETS"
    
    Case "PSP"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [PSP] FROM CYBER_ASSETS"
    
    Case "Rack"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [RACK] FROM CYBER_ASSETS"
    
    Case "Row"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [Row] FROM CYBER_ASSETS"
    
    Case "Brief"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [BRIEF] FROM CYBER_ASSETS"
    
    Case "System Number"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [SYSTEM_NUM] FROM CYBER_ASSETS"
    
    Case "Q1"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [Q1] FROM CYBER_ASSETS"
    
    Case "Q2"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [Q2] FROM CYBER_ASSETS"
    
    Case "Q3"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [Q3] FROM CYBER_ASSETS"
    
    Case "Classification"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [CLASSIFICATION] FROM CYBER_ASSETS"
    
    Case "EACMS?"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [EACMS] FROM CYBER_ASSETS"
    
    Case "PACS?"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [PACS] FROM CYBER_ASSETS"
    
    Case "Within ESP?"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [WITHIN_ESP] FROM CYBER_ASSETS"
    
    Case "Access Point?"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [ACCESS_POINT] FROM CYBER_ASSETS"
    
    Case "Serial Number"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [SERIAL_NUMBER] FROM CYBER_ASSETS"
    
    Case "IP Address"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [IP_ADDRESS] FROM IP_ADDRESSES"
    
    Case "MAC Address"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [MAC_ADDRESS] FROM IP_ADDRESSES"
    
    Case "Domain Name"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [DOMAIN_NAME] FROM IP_ADDRESSES"
    
    Case "Ticket Number"
        Me.FieldsCombo1.RowSource = "SELECT DISTINCT [TICKET_NUM] FROM CHANGE_TABLE_AM"

End Select

End Sub

Private Sub FieldsCombo1_AfterUpdate()
DoCmd.SetWarnings False

Dim field1, field2, field3, sql, sqlTemp, table1, table2, table3 As String, tablePos As Integer

If Not IsNull(Me.FieldsCombo1.RowSource) Then
    field1 = Me.FieldsCombo1.RowSource
    field1 = Mid(field1, InStr(field1, "["), InStr(field1, "]") - InStr(field1, "[") + 1)
End If

If field1 = "[IP_ADDRESS]" Or field1 = "[MAC_ADDRESS]" Or field1 = "[DOMAIN_NAME]" Then
    table1 = "IP_ADDRESSES"
ElseIf field1 = "[TICKET_NUM]" Then
    table1 = "CHANGE_TABLE_AM"
Else
    table1 = "CYBER_ASSETS"
End If

If Not IsNull(Me.FieldsCombo2) Then
    field2 = Me.FieldsCombo2.RowSource
    field2 = Mid(field2, InStr(field2, "["), InStr(field2, "]") - InStr(field2, "[") + 1)
End If

If field2 = "[IP_ADDRESS]" Or field2 = "[MAC_ADDRESS]" Or field2 = "[DOMAIN_NAME]" Then
    table2 = "IP_ADDRESSES"
ElseIf field2 = "[TICKET_NUM]" Then
    table2 = "CHANGE_TABLE_AM"
Else
    table2 = "CYBER_ASSETS"
End If

If Not IsNull(Me.FieldsCombo3) Then
    field3 = Me.FieldsCombo3.RowSource
    field3 = Mid(field3, InStr(field3, "["), InStr(field3, "]") - InStr(field3, "[") + 1)
End If

If field3 = "[IP_ADDRESS]" Or field3 = "[MAC_ADDRESS]" Or field3 = "[DOMAIN_NAME]" Then
    table3 = "IP_ADDRESSES"
ElseIf field3 = "[TICKET_NUM]" Then
    table3 = "CHANGE_TABLE_AM"
Else
    table3 = "CYBER_ASSETS"
End If

If field1 = "" Then
    If field2 = "" Then
        If field3 = "" Then
            Me.UNIDs.RowSource = "SELECT [UNID] FROM CYBER_ASSETS ORDER BY UNID"
            Exit Sub
        Else
            sql = "SELECT [UNID] FROM " & table3 & " WHERE " & field3 & "='" & Me.FieldsCombo3.Value & "'"
        End If
    Else
        If field3 = "" Then
            sql = "SELECT [UNID] FROM " & table2 & " WHERE " & field2 & "='" & Me.FieldsCombo2.Value & "'"
        Else
            sql = "SELECT [UNID] FROM " & table2 & ", " & table3 & ", WHERE " & table2 & ".[UNID]=" & table3 & ".[UNID] AND " _
                  & table2 & "." & field2 & "='" & Me.FieldsCombo2.Value & "' AND " & table3 & "." & field3 & "='" & Me.FieldsCombo3.Value & "'"
        End If
    End If
Else
    If field2 = "" Then
        If field3 = "" Then
            sql = "SELECT [UNID] FROM " & table1 & " WHERE " & field1 & "='" & Me.FieldsCombo1.Value & "'"
        Else
            sql = "SELECT [UNID] FROM " & table1 & ", " & table3 & ", WHERE " & table1 & ".[UNID]=" & table3 & ".[UNID] AND " _
                  & table1 & "." & field1 & "='" & Me.FieldsCombo1.Value & "' AND " & table3 & "." & field3 & "='" & Me.FieldsCombo3.Value & "'"
        End If
    Else
        If field3 = "" Then
            sql = "SELECT [UNID] FROM " & table1 & ", " & table2 & ", WHERE " & table1 & ".[UNID]=" & table2 & ".[UNID] AND " _
                  & table1 & "." & field1 & "='" & Me.FieldsCombo1.Value & "' AND " & table2 & "." & field2 & "='" & Me.FieldsCombo2.Value & "'"
        Else
            sql = "SELECT [UNID] FROM " & table1 & ", " & table2 & ", " & table3 & ", WHERE " & table1 & ".[UNID]=" & table2 & ".[UNID] AND " _
                  & table2 & ".[UNID]=" & table3 & ".[UNID] AND " & table1 & "." & field1 & "='" & Me.FieldsCombo1.Value & "' AND " _
                  & table2 & "." & field2 & "='" & Me.FieldsCombo2.Value & "' AND " & table3 & "." & field3 & "='" & Me.FieldsCombo3.Value & "'"
        End If
    End If
End If

tablePos = InStr(sql, "CYBER_ASSETS,")
If tablePos <> 0 Then
    If InStr(tablePos + 1, sql, "CYBER_ASSETS,") <> 0 Then
        sqlTemp = Left(sql, tablePos - 1)
        sql = Replace(sql, " CYBER_ASSETS,", "", tablePos)
        sql = sqlTemp & sql
    End If
End If

tablePos = InStr(sql, "IP_ADDRESSES,")
If tablePos <> 0 Then
    If InStr(tablePos + 1, sql, "IP_ADDRESSES,") <> 0 Then
        sqlTemp = Left(sql, tablePos - 1)
        sql = Replace(sql, " IP_ADDRESSES,", "", tablePos)
        sql = sqlTemp & sql
    End If
End If

tablePos = InStr(sql, "CHANGE_TABLE_AM,")
If tablePos <> 0 Then
    If InStr(tablePos + 1, sql, "CHANGE_TABLE_AM,") <> 0 Then
        sqlTemp = Left(sql, tablePos - 1)
        sql = Replace(sql, " CHANGE_TABLE_AM,", "", tablePos)
        sql = sqlTemp & sql
    End If
End If

sql = Replace(sql, ", WHERE", " WHERE")

Me.UNIDs.RowSource = sql

If Me.UNIDs.ListCount <> 0 Then
    Me.UNIDs.Selected(0) = True
    Me.UNIDs = Me.UNIDs.ItemData(0)
    Call UNIDs_Click
Else
    'Hide page
End If

Me.Fields2.Visible = True

End Sub

and replace it with this:

Private Sub Fields1_name_AfterUpdate()
    Me.Fields1_val.value = "Choose a value"
    Me.Fields1_val.RowSource = "SELECT DISTINCT " & Me.Fields1_name.value & " FROM CYBER_ASSETS"
    Me.UNIDs.RowSource = Filter_On(Me.Fields1_name, Me.Fields1_val, Me.Fields2_name, Me.Fields2_val, Me.Fields3_name, Me.Fields3_val)
    Me.Filter_val.value = Me.UNIDs.RowSource
    Me.UNIDs.Selected(0) = True
    Me.UNIDs = Me.UNIDs.ItemData(0)
    Call UNIDs_Click
End Sub

Private Sub Fields2_val_AfterUpdate()
    Me.UNIDs.RowSource = Filter_On(Me.Fields1_name, Me.Fields1_val, Me.Fields2_name, Me.Fields2_val, Me.Fields3_name, Me.Fields3_val)
    Me.Filter_val.value = Me.UNIDs.RowSource
    Me.UNIDs.Selected(0) = True
    Me.UNIDs = Me.UNIDs.ItemData(0)
    Call UNIDs_Click
End Sub

This code essentially filters the records on a form.

 

[gunslingor]

And the crowd goes silent!

 

[Pat Hartman]

Did you delete any objects? Perhaps one of them was corrupt and once it was gone, the compact was able to finally clean up the space it was using.

Depending on the version of Access you are using, running SQL from VBA can cause substantial bloat. If you have ever worked with SQL in a compiled language, you may recognize the terms static and dynamic. Static queries are "compiled" and their execution plans are saved and reused at runtime. Dynamic SQL is "compiled" each time it runs. Querydefs are static. They are complied and their execution plans saved for future use. SQL in VBA is dynamic and must be compiled each time it is executed. Newer versions of Access are more efficient about recapturing workspace used to calculate execution plans so they don't blow up the way older versions do.

 

[JHB]

And the crowd goes silent!

Yes - the weather here is to good to sit in front of an computer, 32 °C.

Its possible I added the rec.close to one function that was missing it and just forgot prior to compacting yesterday... could this be the cause of the reduction?

I don't think it could cause it, but it may be easy to control, comment out the line and run the function again.

Again, everything SEEMS to be operating correctly, but I'm afraid to continue development in this version. I do have backups though, I just don't want to put in work that may be lost. Note that the last backup I took was 4 days ago and there the front end was 32MB.

You could create a brand new database and import all from the old database into it.
I think the size will be near the size you have now, about 5MB.

You don't need the value part in "Me.Textbox_Name.value" the "Me.Textbox_Name" do it.

Pat Hartman points on some good explanations.

 

[gunslingor]

Thanks for all the input guys, I guess I'll just proceed being happy the bloat is gone and assume I fixed some unknown bloating issue. Not much else I can do.

You don't need the value part in "Me.Textbox_Name.value" the "Me.Textbox_Name" do it.

True, but I think I've had one or two issues in the past and found it safest to include the value. I can't remember precisely, but I think if a control name is the same as the control source, that adding the value distinguishes if your referring to the value of a control or to one of many fields in the form's control source... probably not right, but something like that.

Regarding the lowercase "value" problem from auto complete, I think I know the cause but not sure how to fix. I think I've had this issue before with other keywords. I think what happened was that I was writing a function which contained "Dim value as string". Immediately after writing that line, I realized that "value" was a keyword and I renamed it, but it was too late and it already interacted with the keyword variable in that odd access way. I'll start another post to discuss.

 

[RainLover]

DoCmd.SetWarnings False

This should either be left out altogether or added back in at the end of the code.

DoCmd.SetWarnings True

I would get rid of it because it hides any current error.

Also you could do a decompile then recompile to see if that makes a difference which I feel sure it will.

 

[gunslingor]

Not sure why your giving me advice on the warning commands Rainlover.

Regarding the "value" auto-complete problem previously discussed, I solved it. De-compile/Re-compile fixed it and now all my Values are capitalized again.

Thanks guys.

 

[RainLover]

Not sure why your giving me advice on the warning commands Rainlover.

Regarding the "value" auto-complete problem previously discussed, I solved it. De-compile/Re-compile fixed it and now all my Values are capitalized again.

Thanks guys.

I am not sure if you think I am interfering with my reference to Set Warnings or not.

If you would like a better explanation I would be happy to do so.

Just as a sample you are turning Warnings off but never turn them back on again.

I will wait for your reply as to what you want to happen.

 

[gunslingor]

No worries buddy... its just totally out of context... I think the code your looking at, as I believe I stated, was removed and replaced with the tiny code snippet referenced later. Within the context of the conversation, That ultra large chunk of inefficient code with the warnings turned off was removed and replaced and I was wondering if that could have been the cause of the size reduction. i.e. that problem was corrected already, you just went straight to the code without reading the text =). No worries.:banghead:

 

[ions]

Did you delete a form with a lot of embedded graphics?