Access 2019 Unable to Compact or back up DBs

[jadedalan]

Hi

Help. I've recently installed a standalone copy of access.  I have some experience of using earlier versions.  Whenever I try to either compact or back-up a db, an error message tells me that my file is in use.  I have eliminated the possibility of old lock files and have ensured the folders I'm using all have full permissions.  I've also tried 'clearing the air' via a blank db as advised elsewhere.  Whether I attempt to initiate this process from'Database Tools compact & Repair' or via 'Home/Info', the results are always 'Could not Use - file nnn already in use'.  I realise that I can set 'Compact on Close' but this is not an answer and is no help at all for Backing up.   Am I doing something obviously wrong?

[#2]

@jadedalan 

• How do you open it, just double-click?
  1. If yes, try press and hold Shift key, then double-click, do not release your finger until application opens. 
  2. press and hold Shift key again, use compact & repair
  3. use backup
• check if its Shared or Exclusive, make it shared 
  • File\options\client settings\advanced, see open default mode

I hope that helps.

[#3]

@jadedalan 
Is this a problem with only one accdb, or does it happen with any accdb you open?

Does any automation code run at startup?

If not, does this happen if you open the accdb and immediately try to run compact & repair before doing ANYTHING else?

Are you trying to compact & repair an accdb in a folder on your local computer, or in a network share?

[#4]

@George Hepworth 

Hi George

It happens with all dbs.  Ones carried forward from earlier version of access, new-ish ones and a couple I've created just to see if I can make compact and back up work.  I have db access set to 'shared'.  If I try and start a compact whilst the db is open via the 'Compact and repair etc' icon on the 'Database Tools' toolbar then I immediately get an error.  The same happens If I close the db and start a dialogue via 'file/info/open file location'.  As soon as I select a db - the same error - 'see below'  Everything I've said also applies to back ups. 

[#5]

also all my work is local

[#6]

@jadedalan 

That's puzzling indeed. I can think of nothing relevant that would be impeding the C&R.

I will send out for the Cavalry... 

.

[#7]

It's not beyond the bounds of possibility that I'm doing something dumb, but I hope not

[#8]

I've already mentioned this, but it may be worth repeating - I only bought this 'standalone' version (2019) of Access about a week ago and it hasn't worked from day one

[#9]

@jadedalan 

Perhaps you haven't registered the license for it yet?

Perhaps you should try a repair or re-install, since it's that new.

[#10]

@jadedalan 

I don't know if we covered the issue of Trusted Locations?

[#11]

I've seen this when the database was in a SharePoint library. Is that the case here? Even placing the file locally was not enough, I had to copy the file to another PC to unlock it to be able to work with it properly, then copy it back.

[#12]

@jadedalan 

I notice that the screen shot you provided starts with a D:\ drive, although the rest is blacked out. Is D:\ a second hard drive in your computer? Or maybe a mapped drive? Maybe another location such as a DVD or CD Drive? I don't imagine that's the case, but to be thorough, let's identify the location to be certain.

Does this problem persist if you copy an accdb to the C:\ drive as well?

[#13]

@George Hepworth 

Thanks for all your suggestions.  So - one at a time.

• I'm not using share point
• My d drive is a standard HDD which I use as a data drive.  I copied an access db to a folder on the C drive and made it a trusted location
• I appear to be licensed. I downloaded this from Microsoft.  The only option I can find is 'change your license', which when I proceed, asks me if I want a different one.  I presume from that, that I am.  I also recall vaguely that it was part of the initial activation process
• Repair behaves the same way as compact, understandably I guess

I'm left with the option to re-install, which I will when I get to grips with how to.  Via the downloaded install exe I guess.

I've tried, both with the DB open (in shared mode) and with another db open, via the file/info/enter path.  I'm presuming either of these should work (and neither do).  Is this a correct assumption or is there a preferred route?

Thanks Again

Alan

[#14]

@jadedalan 

It sounds like you've covered all bases. Thanks for the recap.

I am definitely grasping at straws, it seems. But one last thing to try. Have you started Access as an Administrator?

 

[#15]

@George Hepworth 

Good thought and one I hadn't had - unfortunately, nada.  I'll reinstall.  at least I can copy dbs via Windows explorer.  I can't lose the thought that's it's something to do with running a standalone version, but then again, I can't be the only one.

Alan

[#16]

Alan, I have occasionally had the "in use" issue attempting to compact and repair when one or more objects were open. Closing all open objects, then attempting to compact worked for me in those situations.

[#17]

@jadedalan 

Actually, the fact that this is the first time I've encountered an unexplained problem like this does suggest it's not related to having a stand-alone version of Access. Otherwise, we'd see more reports like it.

I think we covered this, but if you press and hold down the Enter Key while opening any accdb, and then IMMEDIATELY do the compact & repair before doing anything else, does that not work?

[#18]

Have you rebooted your machine?

Is this a Microsoft Store (App) version of Access?

Have you tried a full Online repair?

If you go the route of uninstalling and reinstalling, be sure to use Option 2 from https://support.microsoft.com/en-us/office/uninstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5d... to perform the uninstall.

[#19]

@jadedalan 

FWIW, something just came up in another forum that might have some bearing on this problem.

The issue arises when a file is copied under Windows " ... and the file is not open, Windows places a full lock on the file during the copy.

If the file is an Access database, and someone tries to open it in Access either directly or via a linked table during the time it’s being copied, Access behaves as if there were read only. Non-updateable errors get triggered.

That seems okay, but a side-effect is that the database seems to stay locked by Windows. So even after the Windows file copy is over, there’s still a lock that prevents Access from editing the data. Closing Access makes no difference and there’s no laccdb or ldb lock file.

We’ve overcome the file lock by rebooting the server hosting the database but that’s a pretty drastic remedy. We had a situation where the database was sitting on an external hard disk from the PC, and unplugging the USB cable and plugging it back in seemed to cause Windows to break the lock, and was much better than rebooting it."

Another very long shot, but it might be worth considering as well. Assuming you haven't solved the problem otherwise.

[#20]

Thanks for your suggestion. Mine is just an 'umble single user setup, so no users vying for contention, to throw off, just a reboot. Unfortunately.....