Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

618 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