MS Access Stops and Compact and Repair Fails

I'm having a frustrating problems with MS Access.  The programs stop in the middle of a relatively simple process.  When I try to compact and repair the database, the mySysCompactError table has records like:

-1907      You do not have the necessary permissions to use the 'MSysAccessStorage' object.  Have your system administrator or the person who created this object establish the appropriate permissions for you.            MSysAccessStorage

I'm using MS Access 2003, and Windows 7.

My database starts out at 112,068 KB.  I have a table with 180,000 records with a Full Name field.  I am spliting it into First Name, Last Name, etc.  If I only process a 2000 records, everything works fine.  If I try to process all the records, the program stops in the middle and the file size grows to 810,848 KB.  I can open the database, but when I try to work with the table, Access quits again.  If I try to Compact and Repair, the  process fails.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sounds like some form of corruption to me.

Try creating a new blank database and importing everything from the existing database.
Jeffrey CoachmanMIS LiasonCommented:
How are you doing this "Split"?

If you are using an inefficient loop, you may be running out of resources...
drjoeusaAuthor Commented:
The loop is pretty basic.  If we can improve it, that would be nice.  Here is the subroutine I use:
Private Sub btnProcessNames_Click()
Dim rs As Recordset
Dim thisName As String, LName As String, FName As String, Generation As String, MidName As String, Degree As String
Dim City As String, State As String, Zip As String, Zip4 As String, strSQL As String
Dim parts() As String, iPts As Integer, k As Integer, nRec As Long
strSQL = "SELECT ProviderID, ProvName, FName, LName, MidName, Generation, Degree, nRec " & _
"FROM tblProviderList " & _
"WHERE (((nRec) Is Null));"

Set rs = CurrentDb.OpenRecordset(strSQL)
nRec = 0
With rs
Do While Not .EOF
    nRec = nRec + 1
    thisName = Trim(.Fields("ProvName"))
    FName = ""
    LName = ""
    MidName = ""
    Generation = ""
    Degree = ""
    'First get rid of periods and multiple spaces
    thisName = Replace(thisName, ".", " ")
    Do While InStr(thisName, "  ") > 0
        thisName = Replace(thisName, "  ", " ")
    'Split the name into parts based on commas
    parts = Split(thisName, ",")
    iPts = UBound(parts)
    If iPts < 1 Then
        'Debug.Print .Fields("ProviderID"), thisName
        GoTo nextProv
    End If
    LName = Trim(parts(0))
    'Check for Middle Name/Initials
    FName = Trim(parts(1))
    'Check for a generation designation
    If InStr(FName, "Jr") > 0 Then
        FName = Replace(FName, "Jr", "")
        Generation = "Jr"
    ElseIf InStr(FName, "Sr") > 0 Then
        FName = Replace(FName, "Sr", "")
        Generation = "Sr"
    ElseIf InStr(FName, "III") > 0 Then
        FName = Replace(FName, "III", "")
        Generation = "III"
    ElseIf InStr(FName, "IV") > 0 Then
        FName = Replace(FName, "Sr", "")
        Generation = "IV"
    End If
    FName = Trim(FName)
    If InStr(FName, " ") Then
        k = InStr(FName, " ")
        MidName = Trim(Mid(FName, k))
        FName = Trim(Left(FName, k))
    End If
    k = 2
    Do While k <= iPts
        Degree = Degree & parts(k) & " "
        k = k + 1
    Degree = Trim(Degree)
    rs.Fields("LName") = LName
    rs.Fields("FName") = FName
    rs.Fields("MidName") = MidName
    rs.Fields("Generation") = Generation
    rs.Fields("Degree") = Degree
    If nRec Mod 1000 = 0 Then
        Debug.Print nRec
    End If
End With
MsgBox "Process Complete"
End Sub
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

drjoeusaAuthor Commented:
I have had problems in the pass with exceeding the number of locks allowed.  But that error never caused the program to crash.

Jeffrey CoachmanMIS LiasonCommented:
So try Peter's suggestion first and see if that clears up this issue...
drjoeusaAuthor Commented:
Okay, I did this a couple of times and still had the problem.  For the most streamlined case, I created a new table in a new database with only the required name fields.  I then appended the names to this table.  I also imported the form to do the calculations.  So all I have in the database is the tblProviderList, and the form.  This gave me a database with about 17,500 KB.  I ran the program to split the names and the process completed without a problem.  But when I tried to run a query against the table or to compact the table, it again failed.  After running the process, the file had grown to > 714,000 KB.

I then started over with this new database and ran the program against 10,000 records at a time.  Between each set I compacted and queried to count the number of completed records.  This worked without a problem so I now have all records calculated and the database is about 21,500 KB.  But I shouldn't have to do it this way.

Why, for this excerise would the file grow from 17,500 KB to  714 ,000 KB.  Isn't there some better way to do this?  Also, the limit is supposedly 2GB and we're not even close to that.  I done a lot of work before where files over 1.5 GB worked without a problem.
Jeffrey CoachmanMIS LiasonCommented:
1. You are not specifying the type of recordset.
Dim rst as DAO.Recordset

2. You are not setting your recordset to nothing at the end
set rst=Nothing
end sub

3. You also have a loop inside of a loop, this may also contribute

There may be other small things as well that may be contributing to this as well...

Just remember that just the act of running a recordset on "thousands" of records uses a lot of resources...
Bloat will always occur.
This is why the Compact&Repair utility exists...

<The programs stop in the middle of a relatively simple process.>
I would not categorize what you are doing there as "Simple"...

Perhaps you could do some of this as a function in a query?
Perhaps you could do this as a series of SQL Update queries?
Perhaps fiddling with the MaxLocksPerFile setting might help?;en-us;815281&Product=acc2000
Perhaps you could just demand that the source data be "Normalized", so you don't have to go through any of this...

Without having full access to your environment, this is difficult to pin down.

This all being said,
If the result *now* is that you get error and you cannot proceed, then at some point you have to do something to protect your data...
So make sure you always make backups...

Let's see what other experts might post.

The fundamental problem here is the constantly expanding records.
Access will have initially stored the records as 'tightly' as possible, and of course there was not much data - lots of empty fields.
As soon as you add data to the first record Access has to rewrite the entire page of records to accommodate the new size of the first record , same for the second record and so on.  This is causing massive use of storage pages which leads to the bloat youre seeing and I guess eventually leads to Access deciding it can't expand any more (but that's just a guess).

You would get over the bloat and maybe the entire problem if you firstly create a table with all fields padded out to their maximum likely sizes (fill them with XXX..).  You would have to create a Make table query or its equivalent to do this, of course , you can't do it within the existing table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
drjoeusaAuthor Commented:
This is an interesting idea.  I'll give it a shot over the weekend.  Thanks.
Jeffrey CoachmanMIS LiasonCommented:
Yes, give Petet's post a try first, ...I am curious as well...

drjoeusaAuthor Commented:
I did a modification of Peter57r's solution.  I created an empty table with all the fields in a separate database file and linked it into my application.  rs is the input file recordset.  I use a second recordset, rsOut, set to the empty table.  Once I do the calculation, I add the total record to the new table like so:

with rs
    For Each thisField In .Fields
       If Not IsNull(thisField.Value) Then
            rsOut.Fields(thisField.Name) = thisField.Value
        End If
    Next thisField
    rsOut.Fields("LName") = LName
    rsOut.Fields("FName") = FName
    rsOut.Fields("MidName") = MidName
    rsOut.Fields("Generation") = Generation
    rsOut.Fields("Degree") = Degree
    rsOut.Fields("nRec") = nRec
end with

This worked without a problem.  The new database grew to 63,000 KB and when compacted dropped to 58,000 KB.  The application database grew about 20 KB.

P.S. I tried boag2000 suggestions to dim rs as DAO.recordset, set rs = Nothing, etc.  This did not have a material effect.

I will accept Peter57r's solution.  Thanks.
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.