No one but me can compact/repair

[DataMinerHome]

I have an Access 2010 database with about 30 frontends all linking to the same backend. The frontends and backend are both located on my company's internal network. I've set all the frontends to compact on close, but if anyone but me is logged in, they all get the "file permissions" error.

Yet, they are able to go outside of Access and creat a new file in the directory.

And even Access can create new files in the directory, because occasionally those "database1", Database2" etc. files show up, containing only an MsysCompactError table. And here's something strange: Even though this is an Access 2010 database, they show up as "Database1.mdb" instead of .accdb, and all of these files seem to be corrupt: When I try to open them I get multiple errors like "parent ID name is not an index in this table", and the msyscompacterror table is full of :
"You do not have the necessary permissions to use the '[whatever]' object. Have your system administrator or the person who created this object establish the appropriate permissions for you."

I've also tried compacting from the command line, no help.

I've been trying to find a solution to this for the better part of a year.

Any and all ideas much appreciated!

 

[Jeanette Cunningham]

Compact on close on the front ends is not a good idea for a multi user database. Especially in A2010 it can cause corruption - there have been posts where this has happened.

Assuming that you want to compact the data in the data file - the back end - the compact on close call in the front ends won't do that.

The data in the back end can only be compacted when all the users are out of the database. You can write code to compact the back end.

 

[Jeanette Cunningham]

Here are links to code to compact.
1.Compact the front end
http://www.rogersaccesslibrary.com/forum/topic377.html

2.Compact the back end
http://www.rogersaccesslibrary.com/forum/topic378.html

 

[PNGBill]

Consider reviewing why you need to Compact.
We used to do this regularly as the databse became Bloated.

After reviewing our database and making changes Bloating has ceased.

Temp Tables were a big problem.

 

[DataMinerHome]

Thanks for all of your replies. To give you some idea of where I'm coming from: I have created and managed Access DB's nearly full time for many years, since the days of Access 2.0. It's only since I've started using Access 2010 that I've encountered this problem.

Pat, I do not use compact on close for the backend. As you suggest, I schedule compacts regularly, weekly in most cases. I have successfully used frontends on network drives for many years -- this makes it MUCH easier to automatically distribute new revisions, which I must do frequently for about a dozen different db's. Otherwise, this would be almost impossible.

I understand about Access needing create and delete permissions to compact successfully. IT assures me, and as far as I can tell they are right, that all the users have both create and delete permissions. I can go to their PC's, when they are logged in, and create or delete files. But I still can't compact unless it's ME that's logged in.

I have generally found it good practice to use compact on close to manage bloat. The current fe's under discussion have not been a big issue, but I am about to take a forced 3-6 month hiatus (a whole 'nother story, bureaucratic nightmare-wise) and would rather not trust that things will not expand to fill the known universe before my return.

So, I am still at a loss as to why my user's can't compact. During my forced hiatus, my computer account (the only one that can successfully compact these db's) will be closed and I will have to get a new account when I come back, so I am fearful that then even *I* won't be able to compact.

And then there's the added mystery of why the "Database1", "Database2" etc, get created in .mdb format instead of .accdb, leading me to believe my db's are in some twilight world between versions of Access.

I've also gone down the "trusted locations" path, and all the user's PC's are set up with the frontend and backend in trusted locations.

Any other ideas????

 

[kaiwei]

Consider reviewing why you need to Compact.
We used to do this regularly as the databse became Bloated.

 

[DataMinerHome]

Pat, yes, that's what I keep telling our IT department, and they keep telling me that all the permissions are there. Besides, if it's a permissions issue, why can the others, when logged in, still create and delete files?

 

[PNGBill]

. Besides, if it's a permissions issue, why can the others, when logged in, still create and delete files?

Can they do this in the exact same folder as your BE is ? Have you tested this ?
Permissions can change. You login, do a Compact. Log out. Get a user to login and do a compact, if failed, get them to create and delete a file in the same folder as the BE. if they fail then either, they do not have permission and you do, or something caused the permission to change.
You then log in and if you can compact and create and delete a file, then there was no change of permission. They do not have the same permissions as you.