• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 852
  • Last Modified:

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
0
NGPSoft1
Asked:
NGPSoft1
  • 6
  • 5
  • 2
  • +1
1 Solution
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now