Unable to compact/repair MDB in 64-bit Windows7

Greetings ... I'm trying to compact/repair a database on a 64-bit Windows7 machine with Office 2007 programatically via the fucntion at the bottom of this message.

The compact/repair function is called several times throughout the entire process to ensure the backend database does not bload beyond 2gigs (and become unusable).  It's successfully compacting/repairing the first few times it is called, but always seems to fail on the 3rd or 4th time it is called.  When it fails, it returns "Reserved Error 2950"

At this point, if I exit the app, and then open the backend database and try to manually compact/repair it, it gives the following error message: "Could not use '<file path>'; file already in use."  Closing it / trying it multiple times produces the same message.

There is no LDB file set on the backend, no other users on my machine, nobody else is in the database.  No clue why this is happening!

If anyone has any insight, it would be greatly aprpeciated.

Thanks!
Ian
LVL 4
NGPSoft1Asked:
Who is Participating?

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

x
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.

dqmqCommented:
>The compact/repair function is called several times throughout the entire process to ensure the backend database does not bload beyond 2gigs (and become unusable).

Thus, you are encroaching on the limits of Access.  It's a grey area where applications become unstable before failing outright.  Time to migrate to a more scalable database.  
0
NGPSoft1Author Commented:
The database size for this particular pass-through is very small, though .. .I don't get anywhere near the 2gb limit. SOMEtimes I do, but the amount of data for this particular pass-through is not much, so the maximum size I reach is about 15-20 megs tops.
0
chcwCommented:
Try Advanced Access Repair at http://www.datanumen.com/aar/ . Maybe this third party tool can help you.
0
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.

NGPSoft1Author Commented:
chcw ... I'm not sure if I want to resort to incorporating a 3rd party app quite yet, but I'll look into it as a last resort.

At this point I'm assuming this is to do with the 32 bit access app trying to run in the 64 bit environment and something not jiving about that, but that's about all the lead I have to go on.
0
senadCommented:
nothing to do with x64 since Access 2007 is x32 app.
I do not understand this :
'via the fucntion at the bottom of this message'
How exactly are you trying to C&R the DB ???
0
senadCommented:
or are you trying to use a function that is for *.mdb
database on a *.accdb database ?
0
senadCommented:
you should also read here :
http://www.granite.ab.ca/access/bloatfe.htm
0
senadCommented:
also make sure you have exclusive db access ....
0
NGPSoft1Author Commented:
Shoot, sorry senad ... forgot to paste the code snippet earlier:

I'm getting 2950 - Reserved Error, failing at line 10003

Public Sub CompactAndRepairSub()
    On Error GoTo errhandler

    Dim oAcc As Access.Application
    Dim strFilename, strTEMPFilename As String

10000:    Set oAcc = CreateObject("Access.Application")

10001: strFilename = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "NEW-DB-Tuneup-Backend.mdb"
10002: strTEMPFilename = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "NEW-DB-Tuneup-Backend-TEMP.mdb"

10003:    oAcc.CompactRepair strFilename, strTEMPFilename, False
10004:    Kill strFilename
10005:    Name strTEMPFilename As strFilename
    'MsgBox "Compact and Repair Successfully Completed.", vbInformation + vbOKOnly
10006:    oAcc.Quit acQuitSaveNone
10007:    Set oAcc = Nothing
    Exit Sub

errhandler:
    MsgBox Err.Number & " - " & Err.Description & " | Failed at line number: " & Erl, vbOKOnly
End Sub
0
NGPSoft1Author Commented:
Some additional info ... if I pause the subroutine when the error happens, then replace the backend database with an EXACT copy of the backend database that is no longer allowing the compact/repair, it all of the sudden works!  (for a while ... then it starts spitting out the 2950 error)
0
dqmqCommented:
If you get that working reliably, then I award you the prize for Access Expert of the year!  

You say our DB is small, but then you insist on re-orging it multiple times to make sure it does not bloat.  If periodic scheduled reorgs don't do the trick, then I'm afraid your tryin' to move the wagon by pushing on the rope.  
0
senadCommented:
0
NGPSoft1Author Commented:
Thanks for the link, but it looks like that thread is just about programatically performing the compact/repair, whereas my problem is that it is breaking specifically on my 64-bit Windows7 machine, and running perfectly on my WindowsXP and Windows Server 2008 machines.
0
NGPSoft1Author Commented:
I believe the solution to this problem was the Microsoft Indexing Service was turned on in the folder in which I was trying to perform the compact/repair.  Since disabling this, I have not had any issues.

This also solved another problem I was having between Windows 7 and Microsoft Excel 2007 where I'd get a sharing violation when attempting to trigger the SAVE feature in Excel via VBA in an Access Module.
0

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
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
Windows OS

From novice to tech pro — start learning today.