none
Access 2010 Compact & Repair drops indexes RRS feed

  • Question

  • Access 2010 Compact & Repair drops indexes in descending order. The database is ~700MB and the table affected has 190 columns, 228,000 records and 13 indexes and runs on Windows 7 with 4GB of memory. The same file compacts without incident using Office 2007 on a Window XP machine with 2GB of memory. Strangely it runs OK on a VM running Office 2010 and on my home desktop running Office 2010 on Windows 8.

    Can anyone tell me why this is happening after a year of daily compacting? The file grows by about 500KB a day. I am seriously considering downgrading my main office desktop to Office 2007.

    Saturday, August 4, 2012 6:35 PM

All replies

  • Speculating I would say the Access installation on that machine is messed up. I would first repair Office. Also ensure the latest service pack is installed, and that you run Microsoft Updates to exhaustion.

    If that does not help, uninstall and reinstall Office.


    -Tom. Microsoft Access MVP

    Saturday, August 4, 2012 11:41 PM
  • Thanks Tom, I have done all those things, repaired Office twice, reinstalled Office after wiping the folder.  It looks to me like the Microsoft Updates caused the problem because VM has not be updated and is working fine.  My home machine has Office 2010 running Windows 8 beta and compacts without wiping out the indexes too.
    Thursday, August 9, 2012 11:31 AM
  • Hello GeorgEverett,

    I created a database with some indexes defined as descending indexes and attempted to perform the compact and repair on a recent build of Access 2010, but it did not remove the indexes.

    I would be interested in the following:

    1. What is the build number of Access from both machines (14.0.xxxx.xxxx)? This should be listed within Access by going to File > Help
    2. Is the index defined for a single field?
    3. What is the datatype of the field(s) for this index?
    4. If you create a new blank database on the problematic machine and import one of your tables that contains the descending index, does the index still exist after the import? Also, if you compact and repair the new database file, is the index removed?

    Regards,

    Dennis

    Tuesday, August 14, 2012 4:47 PM
    Answerer
  • Hi Dennis-

    1. Microsoft Access 2010 (14.0.6024.1000) SP1 MSO (14.0.611.5000)

    Part of Microsoft Office Professional Plus 2010

    2. There are 14 indexes defined.

    ID / Key field / Text/ No Duplicates

    AccountName / Text / Duplicates OK

    Billing_Control__c / Text / Duplicates OK

    Billing_Frequency__c / Text / Duplicates OK

    CloseDate / DateTime / Duplicates OK

    Contract__c / Text / Duplicates OK

    Finance_Close_Date / DateTime / Duplicates OK

    Invoice_Type_New__c / Text / Duplicates OK

    Master_BCN__c / Text / Duplicates OK

    Qualified_to_Bill__c / YesNo / Duplicates OK

    Workspace_ID__c / Text / Duplicates OK

    3. The indexes are all ascending.  But the last two or three (Master_BCN__c, Qualified_to_Bill__c and Workspace_ID__c) indexes are dropped when compacting.  Doesn't matter what ordinal position the fields are placed in either.  They are dropped in descending alphabetical order.

    When the table is imported into a new database shell, 222,485 records out of 231,153 are created, a message box appears saying "Resources have been exhausted" and no indexes are created.

    Perhaps it is a memory issue.  So I tried increasing the Virtual Memory space.  I tried deleting a few processes (there about 100 total).  Still no luck.

    Thanks for your interest,

    George

    Tuesday, August 14, 2012 6:34 PM
  • Hi George,

    Thanks for the updates.

    These may seem like odd tests, but I'm curious if changing the processor affinity for the msaccess.exe process changes either behavior.

    Steps:

    1. Launch your database which contains all indexes

    2. Launch task manager

    3. Right-click on msaccess.exe from the process tab and choose Set Affinity

    4. Uncheck the All option and select a single processor

    5. Try to perform the compact and repair and note if the indexes are removed

    Go back through the steps to set the processor affinity after launching a new blank database. Then try to import your original database into the new file again. Do you still get the "System Resources Exceeded" error?

    Have you noticed any difference in time it takes for the compact and repair to complete between your machines? How long does the compact and repair take on the affected PC?

    Regards,

    Dennis

    Wednesday, August 15, 2012 3:35 PM
    Answerer
  • Hi Dennis-

    Setting the Affinity to one processor solved the mystery.  The database compacted in about 2 minutes using one processor and the indexes were intact.  Importing the big table took about 2 minutes, no errors were encountered and the indexes were imported as well

    Setting the Affinity back to 4 processors reintroduced the problems, but Compact and Repair is faster.

    How do I configure Access to use only one processor?  Do I have to go to the Task Manager each time and uncheck processors 01, 02 and 03?

    Thanks for the help!

    George

    Wednesday, August 15, 2012 4:17 PM
  • Hi George,

    You could set the affinity from a VBA code snippet and call the SetAffinity function from an autoexec macro.

    Add this sample code into a new module:

    Private Declare Function GetProcessAffinityMask Lib "kernel32.dll" _
    (ByVal hProcess As Long, ByRef dwProcessAffinityMask As Long, _
    ByRef dwSystemAffinityMask As Long) As Boolean

    Private Declare Function SetProcessAffinityMask Lib "kernel32.dll" _
    (ByVal hProcess As Long, ByVal dwProcessAffinityMask As Long) As Boolean

    Private Declare Function GetCurrentProcess Lib "kernel32.dll" () As Long

    Public Function SetAffinity()

    Dim hRet As Long
    Dim dwProcMask As Long
    Dim dwSysMask As Long

    hRet = GetProcessAffinityMask(GetCurrentProcess(), dwProcMask, dwSysMask)

    'Set affinity to the first processor
    hRet = SetProcessAffinityMask(GetCurrentProcess(), &H1)

    If hRet <> 0 Then

    MsgBox "Process Affinity successfully set for this application."

    Else
    MsgBox ("Error : " & Err.LastDllError)
    End If

    End Function

    Thursday, August 16, 2012 4:11 PM
    Answerer
  • Hi Dennis-

    Thanks for the code.

    I Googled "set affinity" yesterday and found a utility "CPU-Control" that seems to be working.  I just hope that is it is not malware.  Have you had any experience with it?  I can be found at "http://www.koma-code.de/index.php?option=com_content&task=view&id=88&Itemid=93"

    Best wishes,

    George

    Thursday, August 16, 2012 5:28 PM
  • Hi George,

    Next time you should try to Bing it instead. (wink wink) www.bing.com

    Sorry, I don't have experience with the utility mentioned.

    However, I also found a method allowing you to set the affinity from a shortcut.

    C:\Windows\System32\cmd.exe /C START /affinity 0xa msaccess

    Regards,

    Dennis

    Friday, August 17, 2012 12:57 PM
    Answerer
  • Could you explain why the affinity impacts the Compact and Repair? 

    We have a 120MB backend mdb shared on our network used by 5-10 users. (Office 2010 on Win7)

    We compact and repair about every two months, or sooner if there is corruption.

    Last time we lost ALL of our indexes on the main parent table. Will setting the affinity to one CPU prevent this issue?

    Jean-Marc

    Monday, August 22, 2016 6:18 PM