NGPSoft1
asked on
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
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
ASKER
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.
Try Advanced Access Repair at http://www.datanumen.com/aar/ . Maybe this third party tool can help you.
ASKER
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.
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.
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 ???
I do not understand this :
'via the fucntion at the bottom of this message'
How exactly are you trying to C&R the DB ???
or are you trying to use a function that is for *.mdb
database on a *.accdb database ?
database on a *.accdb database ?
you should also read here :
http://www.granite.ab.ca/access/bloatfe.htm
http://www.granite.ab.ca/access/bloatfe.htm
also make sure you have exclusive db access ....
ASKER
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.Appli cation")
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-TEM P.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
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.Appli
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-TEM
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
ASKER
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)
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.
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.
Can this help you (it's similar) ?:
https://www.experts-exchange.com/questions/22706278/How-automatically-doing-Compact-and-Repair-Database.html
https://www.experts-exchange.com/questions/22706278/How-automatically-doing-Compact-and-Repair-Database.html
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.