Solved

MS Access Stops and Compact and Repair Fails

Posted on 2012-03-22
12
1,084 Views
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.
0
Comment
Question by:drjoeusa
  • 5
  • 5
  • 2
12 Comments
 
LVL 77

Expert Comment

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

Try creating a new blank database and importing everything from the existing database.
0
 
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...
0
 

Author Comment

by:drjoeusa
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
.MoveFirst
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, "  ", " ")
    Loop
    '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
    Loop
    Degree = Trim(Degree)
   
    rs.Edit
    rs.Fields("LName") = LName
    rs.Fields("FName") = FName
    rs.Fields("MidName") = MidName
    rs.Fields("Generation") = Generation
    rs.Fields("Degree") = Degree
    rs.Update
    If nRec Mod 1000 = 0 Then
        DoEvents
        Debug.Print nRec
    End If
nextProv:
.MoveNext
Loop
.Close
End With
MsgBox "Process Complete"
End Sub
0
 

Author Comment

by:drjoeusa
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.

Thanks,
0
 
LVL 74

Expert Comment

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

Author Comment

by:drjoeusa
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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
rst.Close
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?
http://support.microsoft.com/default.aspx?scid=kb;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.

JeffCoachman
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 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.
0
 

Author Comment

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

Expert Comment

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

Jeff
0
 

Author Comment

by:drjoeusa
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
....
    rsOut.AddNew
    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
    rsOut.Update
....
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37763805
ok
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now