Access issue - compact and repair database doesn't work

[Jedytka]

Hi,

At work I have one access file which is use by me and my team workers. Ech of us saved copy of this access on our individual sharedrive and use it every day.
From yesterday I have issue with compact and repair option. When I click "compact and repair database" I get microsoft Windows error: "the application is not responding. The program may respond again if you wait. Do you want to end this process? "
I add that I am not familiar with access database. I got this file from our client and it worked for years. Nothing change recently, nobody changed any options etc.

I hope that someone will be able to help me.

Best regards

 

[moke123]

Is DB split? Does each user have a front end?
Is the file on your actual machine or are you accessing it over a Lan?

 

[gemma-the-husky]

You can't C&R while a database is in shared use. Is it a big database and on a server. It may be better to rename it, download it to your PC, C&R there, and then copy it back. That way other users can't interfere with your process.

 

[Jedytka]

I'm not sure that DB is split. Ech user have a front end.
The file is save in location: disk C\users\my user\app data\name of our data base
Ech of us saved this DB in the same way
Then we upload using DB 2 excel sheets. One shows data which we need and second file is upload to another database which is saved in our sharedrive.
We use virtual machine for work (VDI horizon)

 

[Jedytka]

You can't C&R while a database is in shared use. Is it a big database and on a server. It may be better to rename it, download it to your PC, C&R there, and then copy it back. That way other users can't interfere with your process.

It means that I can't use DB when someone else use it at the same time? If Yes propably I with my cooworker tried to use DB at the same time but right now everyone closed DB and I still cannot use it.

 

[moke123]

I'm not sure that DB is split.

You should have 2 files. The backend file should contain nothing but your tables. This backend is shared by your users who connect to it with their individual front ends. Users should not share front ends. The front ends contain all your forms, reports, code modules, etc.

 

[gemma-the-husky]

Maybe it's an issue with the locking file

Assume you have a split database.

When you close the database, so no users are active. Check the folder that holds the data.

You data file will be Datafile.accdb, or maybe Datafile.mdb
There may be a small file (less than16k) called Datafile.laccdb, or Datafile.ldb

This second file is a locking file, and stores details of active users.
If there is such a file try to delete it in windows. Not the large database, just the locking file.

Normally that file disappears when the last active user closes the database.
Sometimes though, when you have problems the database can terminate, or you may kill the task in task manager without killing the locking file.
If there are no active users you will be able to delete the locking file. If not the server things there is an active user.
If you can determine who has an active session to the database, you might be able to close the session correctly.

Once you delete that locking file you should be able to compact and repair.
If windows won't let you delete the file because it still says it is in use, then the only solution is to restart the computer.

Be careful not to delete your data. In fact, copy it first just in case.

 

[The_Doc_Man]

Your description of the database layout is confusing in that I'm having trouble trying to decide what is permanent (or persistent) and what is temporary.

Then we upload using DB 2 excel sheets. One shows data which we need and second file is upload to another database which is saved in our sharedrive.

Does every user upload the same 2 sheets? Or does each user get a different pair of sheets?

Does any user, in the course of doing whatever they do, have to look at or use data that someone else uploaded to that second database?

In the course of your daily work with this DB, does it ever occur that two of your users happen to touch the same data down to the row level of your input spreadsheets?

Those first three questions are looking for data overlap or data commonality because that commonality is usually the source of "not responding" in a simple database.

I'm trying to understand the data overlap between users. What I see SEEMS to be a "too many cooks" syndrome, but I can't be sure. I could see this as your DB being a "pseudo-catalyst" type of tool where it actually contains no persistent data, but that "upload to another database" comment makes me think that isn't accurate.

All of this plus the idea that you get a "not responding" message tells me that we are not getting the full picture here. That is going to be difficult for us to analyze well enough to help you. That error CAN occur in a stand-alone DB but it is rare, so to my way of thinking there HAS to be some type of overlap between users despite what appears to be efforts to keep users separate.

By the way, that virtual machine that you use... do you log in to a virtual Windows Professional operating system? Having data in the C:\users\username\... pathway suggests an attempt to isolate & separate user environments, which SHOULD insulate you from the normal causes of being unable to Compact & Repair. But if that isn't what you are using then we need to know more about your network setup because that will be crucial to finding what is blocking you from a C&R.

It is also possible that you have something going on in the background, such as but not limited to a timer-based function.

If you can get everyone out of the DB including yourself, then do as Dave (Gemma-the-Husky) suggested with the lock files, you should then be able to open the DB and immediately perform the C&R before you try anything else. How big is the file you are trying to C&R?