Code bloats the database

[Abraxus]

I have a database with roughly a million rows.

I have to delete all records and re-import new ones on a fairly regular basis.

Then I have to update an empty field to a new value based on some other criteria.

I have code that looks like this to do it:

Code: Select all

Sub SetUp()
    Debug.Print Now()
    Dim rstPassed As DAO.Recordset
    Dim dbPassed As DAO.Database
    Set dbPassed = CurrentDb
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset("select * from [tblSpend] where BU is null", dbOpenDynaset)
    rst.MoveLast
    Debug.Print rst.RecordCount
    rst.MoveFirst
    While Not rst.EOF
        With rst
            .Edit
            !BU = GetNewBU(Nz(![Cost Ctr], ""), rstPassed, dbPassed, Nz(!Category, "x"))
            .Update
        End With
        rst.MoveNext
    Wend
    rst.Close
    Set rst = Nothing
    Set rstPassed = Nothing
    Set dbPassed = Nothing
    Set db = Nothing
    Debug.Print Now()
    MsgBox "Done!"
End Sub

and

Code: Select all

Function GetNewBU(strCC As String, rst As DAO.Recordset, db As DAO.Database, strCategoryIn As String) As String
    'Dim db As DAO.Database
    Set db = CurrentDb
    'Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset("SELECT [TheField] FROM CostCenter WHERE CostCenter = '" & strCC & "'", dbOpenForwardOnly)
    Dim strTemp As String
    If rst.EOF And rst.BOF Then
        strTemp = "OTHER"
    Else
        strTemp = rst![TheField]
    End If
    
    rst.Close
    
    If strTemp = "OTHER" Then
          Select Case strCategoryIn
          Case "00055", "00059"
              strTemp = "Network"
          Case "00031", "00033"
              strTemp = "Development"
          Case Else
              strTemp = "OTHER"
          End Select
    End If
    
    GetNewBU = strTemp
End Function

Problem is that this code bloats the database badly, and I often hit the 2gb limit. Last time it totally corrupted my DB.

Is there a way to NOT have it bloat?

Thanks!

[HansV]

Updating a lot of records will always cause database bloat; you might consider migrating the tables to SQL Server.

Your code could be made somewhat more efficient:

Code: Select all

Sub SetUp()
    Dim db As DAO.Database
    Dim strSQL As String
    Debug.Print Now()
    Set db = CurrentDb
    strSQL = "UPDATE tblSpend SET BU = GetNewBU(NZ([Cost Ctr], """"), " & _
        "NZ([Category], ""x"") WHERE BU IS Null"
    db.Execute strSQL, dbFailOnError
    Set db = Nothing
    Debug.Print Now()
    MsgBox "Done!"
End Sub

Function GetNewBU(strCC As String, strCategoryIn As String) As String
    Dim strTemp As String
    strTemp = Nz(DLookup("TheField", "CostCenter", "CostCenter = '" & strCC & "'"), "OTHER")
    If strTemp = "OTHER" Then
        Select Case strCategoryIn
            Case "00055", "00059"
                strTemp = "Network"
            Case "00031", "00033"
                strTemp = "Development"
        End Select
    End If
    GetNewBU = strTemp
End Function

But I don't think this will make much difference with respect to the bloat.

[Mark L]

First thing is to manually run the process in 2 steps. First do the import and note the size of the db, then do a compact/repair and note the size. Then do the update (and note db size), then do another compact/repair. This should give you a handle on where the bloat is occurring. I'm guessing the initial import causes most of the bloat, as adding alot of records generally causes the indexes to be really inefficient.

I don't know exactly how you import the records, but it would seem logical to try to incorporate updating that field at the same time.

And I'm assuming you do a compact/repair prior to importing?

[Abraxus]

I always do it in this order:

Flush the table
Compact
Import
Run the code until it gives me the error
Compact
Run the code until it errors...
Compact
run the code....

Until it no longer errors.

Sometimes I have very little data, so I don't get an error. Sometimes I have over a million rows, so I error several times.

Is there a way to stop the code BEFORE I get the error, but using code to do it?

[HansV]

I really recommend moving the data to SQL Server. You'll keep on running into the limits of Access otherwise.

[Mark L]

You might want to consider removing all indexes (even the primary key) from the table before the initial compact, then add them back after the import (if needed).

[Abraxus]

Unfortunately, I'm stuck with Access-only here at work...

[Wendell]

One thing you might consider is determining the record count you are dealing with, splitting it into halves or thirds, and then only process that many records with a counter, and do the compact after each pass. What is the database size when it has the approximate million records and has been compacted? If it's close to 1GB then you are really stretching Access MDBs.

But Hans has the right answer - SQL Server Express has a 4 GB upper limit on size, not 2 GB, and it is a free download. There is a small learning curve getting it setup, but it should manage your problem better.