Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


MS Access Stops and Compact and Repair Fails

Posted on 2012-03-22
Medium Priority
Last Modified: 2012-06-27
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.
Question by:drjoeusa
  • 5
  • 5
  • 2
LVL 77

Expert Comment

ID: 37751219
Sounds like some form of corruption to me.

Try creating a new blank database and importing everything from the existing database.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37752357
How are you doing this "Split"?

If you are using an inefficient loop, you may be running out of resources...

Author Comment

ID: 37752502
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37752783
So try Peter's suggestion first and see if that clears up this issue...

Author Comment

ID: 37754119
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754370
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?
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.

LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 37756241
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.

Author Comment

ID: 37756560
This is an interesting idea.  I'll give it a shot over the weekend.  Thanks.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757287
Yes, give Petet's post a try first, ...I am curious as well...


Author Comment

ID: 37762192
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37763805

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question