access compact locks db on multiuser pc

[CJTyo]

I have a front-end that has been deployed to the C drive of some multiuser PC's via SMS, and has 'compact on close' checked. The backend is on a network drive.

The problem is after the first user logs in and uses the db, the next user to log in gets an error:

Microsoft Access cannot find c:\\hazcom\hazcom.mdb

The hazcom.mdb file is in the c:\\hazcom folder, it just won't recognize itself. Very bizarre. I've figured out if a clean copy of the hazcom.mdb file is loaded on the PC with the 'compact on close' unchecked, nobody seems to have a problem - however we haven't redeployed via SMS, yet.

My question is whether this makes sense to anyone else? It doesn't make any sense to me at all!

I'm betting it's a deployment issue with permissions on the installation of the hazcom.mdb file, but I can't convince the deployment team that it's not the 'compact on close' setting.

Help!?

Thanks,
C



*~*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[CJTyo]

In looking around online for a while now, I found a link to this MS article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;295234

For Access 2k it explains this issue:

The compact process always compacts the source Access database file or the Access project file to a new file. The newly created file inherits NTFS permissions that are different from the source file. The inheritance of the NTFS permissions is based on the version of Access that is used to compact the file.

Access 2000
In Microsoft Access 2000, the newly created file automatically inherits the default NTFS permissions of the TEMP folder that corresponds to the currently logged-in user account.

Note By default, the path of the TEMP folder is C:\Documents and Settings\<UserAccountName>\Local Settings\Temp where <UserAccountName> is the name of the user account that is currently logged in.

Now, this is exactly what is happening. Does anyone have any suggestions or bright ideas on something I can do via Access to still compact the files but not get them locked up like this? I ask because there is no way our IT group is going to do any permission level stuff... so either I figure it out on my end or just undo 'compact on close' and redeploy...

Thanks,
C

*~*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.