Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code to fill Column (2K)

    I have the following code that I created with help from the Lounge. It is used to propagate data down a column in a database.

    Function FillColumn(psTable As String, psField As String)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varSaveData

    On Error GoTo ErrHandler
    ' Set database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(psTable)
    If IsNull(rst.Fields(psField).Value) Then
    MsgBox "The first record is empty", vbCritical, "Column Fill"
    GoTo ExitHandler
    End If
    ' Loop through records
    Do While Not rst.EOF
    If IsNull(rst.Fields(psField).Value) Then
    ' Set data to last stored value
    rst.Edit
    rst.Fields(psField).Value = varSaveData
    rst.Update
    ElseIf Trim(rst.Fields(psField).Value) = Empty Then
    ' Set data to last stored value
    rst.Edit
    rst.Fields(psField).Value = varSaveData
    rst.Update
    Else
    ' Store current value
    varSaveData = rst.Fields(psField).Value
    End If
    rst.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Function

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    This code works well but I have discovered that when I run this my database seems to increase in size by a factor of 7 or more. Once I "compact and repair" it goes back to the original size. I realize that this increase is as a result of the internal temporary tables used by Access. I wondered if there was anything I could change to make this work without expanding the database even if it is only temporarily. My problem is my client has a database of 64 meg that seems to jump to over 1 gig. ANy suggestions?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Code to fill Column (2K)

    You don't give any indication as to the number of records in the psTable, but if it's a very large recordset, the bloat you experience is normal. You might want to put that table in it's own database and compact an repair it once you've run the update. I assume your database is split into a FE and a BE. As an aside, it appears you have some sort of relationship between records in that table, which is often an indication of a normalization problem.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to fill Column (2K)

    Thanks. I do realize that the table is not normalized. This is a problem with inheriting someone else's design. The record count is over 200k.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code to fill Column (2K)

    I strongly suggest you redesign it.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Code to fill Column (2K)

    I must agree with Charlotte - at this point you are skating on pretty thin ice, and sooner or later you will be either forced to do a redesign or other even more serious alternatives. Access will deal reasonably well with 200K record tables as long as they aren't horrendously large, but at some point with that kind of data if you are having to do an update to a significant portion of the records, the database is guaranteed to bloat rapidly. At some point you are likely to find that you cannot compact and repair the database because it has gotten too close to the upper limit. Moving it to SQL Server would be an alternative, but if your schema isn't well normalized, you may not see any benefit in doing so. Sorry to be the bearer(s) of bad news, but it's a real world view based on the school of hard knocks.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to fill Column (2K)

    Thanks for your input. I will discuss this with my client.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •