MSysCompactError

[Lhoj]

Hi, everyone. I've been working on an Access DB on a net share from a shortcut on my PC for the last few months. now I've accessed the net location of the DB and there are about fifteen generic db files: database1 (or whatever de default name for a db in english is), database2, database3 and so forth, with dates up to today (last time I've opened the db I've been working on).



Those dbs have a single table named MSysCompactError, with the same error on every record (instead of posting a SS in spanish I'll do my best translating the error description):
Error code: -1907 ErrorDescription: You don't have the required permissions to use the object 'XXXXXXX'. The system administrator or the person who created the object must establish the adequate permissions.
With XXXXX being a field of a table, query or form such as for example sq_cPuestos~sq_cUbicacion (puestos being the table and Ubicacion the field of said table)

The NTFS permissions of the original DB are set to full control for domain admins (only users with access to the net share the db is located at) and the db is working normally, not throwing any error or failing in any operation, but access keeps creating those additional db files with the aforementioned table. Any idea why or how can I fix it/prevent access from generating those files? And before you ask no, the MSysCompactError doesn't reflect which user is the one that doesn't have the required permissions.

Any help will be appreciated.

[Gicu]

The easy fix is probably to remove the Compact on Close from the file. Having that set on a network drive can cause bigger issues like corruption, compacting the back-end should always happen on a local drive. There are a few utilities that can be scheduled using the Windows scheduler to run the compacting, here is mine version:
https://forestbyte.com/ms-access-uti...end-compacter/
Cheers,

[Micron]

compacting the back-end should always happen on a local drive.

Don't agree. Pretty hard to share be tables when their on just your pc? Never had an issue doing that.

[Gicu]

You don't share the tables from the local drive, you copy the BE to local drive, run the compact and repair and copy the compacted file back to the shared network drive overwriting the original. I guess you got lucky...there are a few threads on this, one example here https://www.access-programmers.co.uk...repair.304128/ (post #18 mentions the same approach I usually take).

Cheers,

[Lhoj]

So... the general consensus is not to compact on close? I've read arguments for and against it and I was suggested to do so, but I've read people saying compacting on close could more easily lead to DB corruption. Then again, the error description about the permissions is really bugging me, as is the absurd size of a DB with just this one MSys table with around 250 records comprised of 4 fields, many of them blank. Each DB was still amounting to around 30MB, almost half of the original DB with all its data and attachments.

I have not had a single problem yet with the main DB and those 'error' dbs started appearing around 8 months ago apparently. Luckily with it being on a net share I've previous versions of the file up to 45 days prior and I manually backup it every now and then so I'm confident I got it covered in case of corruption though. I've checked the behaviour with the net share folder open and it indeed creates one of those database.mdb each time the DB is closed while it's compacting, but it gets deleted after the compacting is done. It seems that occasionally this 'no permissions' issue arises and prevents the proper compacting, and the databaseX.mdb remains, but I have no idea what could have caused it around 18 times in those 8 months or so.

Anyway, what's the better approach? Should or should I not use the compact on close option? The db is pretty small and the size reduction is negligible, any other reason to leave it on (aside from me forgetting to do it manually from time to time which will happen)?

Thanks for the tips!

[Welshgasman]

aside from me forgetting to do it manually from time to time which will happen)

I have a memory like a sieve, so I set reminders in a calendar, outlook, wherever.

[Micron]

I'd say it wasn't luck so much as it was a very good network. Anyway, I didn't cr every time it closed, just when it reached a certain size and the db code took care of that so no need to remember anything - especially since size should be the determining factor, not time passed.