No one but me can compact/repair (1 Viewer)

[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

 

[Pat Hartman]

The compact process creates a new empty database, imports all original database objects, deletes the original database, and renames the new database to the old name. If you are seeing database1, database2, db1, or db2 files that means that Access was not able to delete the original file and therefore could not complete the compact process. This is a permissions issue. The users need permission to both create AND delete files in the Access database directory.

PS - the front ends should not be located on network drives, they should be located on each individual's C: drive for better performance.

Compact on close is a really bad idea for the back end. That file is opened and closed constantly throughout the day. It is better to schedule a backup daily, weekly, or monthly depending on how much updating happens in the database. Delete activity is most critical since Access cannot recover the used space until the database is compacted. Update activity can also cause records to be moved from one physical spot to another if the record size grows to large to fit into its current sector and that also leaves waste space. Add activity causes the database to grow but there is no waste space created so compacting isn't strictly necessary. Compacting does force the database engine to recalculate record counts and reorganize the data so it is useful even if you never delete any records.

I know that some people recommend against compact on close for the FE but it really depends on the internal workings of your application. If you use dynamic SQL (SQL strings in code rather than saved querydefs) or make-table queries, you create a lot of bloat and the database needs to be compacted regularly to keep that under control. The recommendation against compact on close was due to a bug that existed in Access which caused the database1 file to be deleted when certain errors occured. That would leave you with NO database at all. People found this distressing. I can't imagine why. Of course in a production environment, this isn't relevant since all you need to do is download a new copy of the FE from the server so I would leave Compact on close if you need it to manage bloat.

 

[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????

 

[Pat Hartman]

If you can do it when logged in from their computers and they can't, it's a permissions issue! It can't be anything else. I'd worry about the new account not being able to compact also so you need to get this cleared up while that account is still active.

The fact that the freshly compacted database is named .mdb is probably just sloppy coding not differentiating between an .mdb and an .accdb at that point in the process. The .mdb can't support some .accdb features so I think it is just a naming error.

Sorry about your hiatus. Maybe you'll find a better job in the interim.

 

[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.