Users on network can't open Db after Compacting 1

[chris3942]

We have a small network. Several users are able to update the
db from their workstations because I've shared the folder in which the db is saved. They have complete access to do changes to the design of the db as well as update data. Because the db size was getting excessive I compacted the db. I did this by opening the db on the pc where the db exists. I went to tools/database utilities and selected "Compact an repair database". Since compacting the database users who log on via the shared network drive can no longer open the db. The message they get is: "Microsoft Access can't find the database file 'H:\filename.mdb'". If I open Windows Explorer and log onto the network drive "H:\" I can see "filename.mdb" in the file list, but if I try to open the db I get the same message. I made a copy of the db before I compacted and the copy works just fine from the remote PC's. However, changed to the compacted db have already been made so we cannot revert to the copy. How do I correct this problem?

 

[Darrylles]

Hi,

It sounds more like an op sys issue rather than Access.

Have you tried copying the file to another folder and running it from there?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[chris3942]

Thanks Darrylles,

Your answer led me to figure out what was causing the problem. I copied the file from the shared folder it normally resides in to another folder and was able to run it from a peer PC on the network in that other folder. Then I copied it back into the original shared folder, overwriting the existing db file, and was again able to run the db from the peer PC.

Now for the cause of the failure after compacting the db:

When Access compacts a db it creates a new file in a temp folder somewhere on the drive. It than deletes the original file and copies the new file from the temp folder back into the folder where the db usually resides. I believe it does this somehow without updating the file system. When it does this, the file comes back with the "permissions" from the temp folder. Well, that temp folder is not shared to the other users on the peer to peer network. When the user tries to use the db he/she gets the message that the db cannot be located, although it is on the mapped shared drive, because the file "appears" to be in a different folder to users.

When I copy the db file to another folder and copy it back into the shared folder, overwriting the existing file, the new copy of the file now assumes the "permissions" of the shared folder and users that are mapped to the shared folder can now use the db again.

This problem can happen when Access is running on a Win2K machine as well as on one running XPpro so I don't think I would say this is an OS problem, rather it is an Access 2000 problem. I think this problem should have been corrected in one of the four service packs I've applied to Office 2000.
Go figure.

Chris

 

[Darrylles]

Hi chris,

I thought about talking about the 'temp' file that MS creates, but it didn't make sense - because the temp file is always deleted on successful 'compact & repair', and you didn't mention such a file.

I can understand that the user that initiates the 'compact and repair' would replace 'share' properties from his / her 'temp' folder due to the fact that the 'temp' file was produced there.

However, I still maintain that this IS an OS problem (network or otherwise) as opposed to MS Access, as YOU could see the file and yet the OS could not find it on clicking it.

I do believe that you still have a OS / network problem, as you should NEVER be able to SEE a file that does not exist.
Yes - it may be a SP problem - thus OS.

You've found your explanation from above somewhere - can you please point this thread to it - for future reference?

ATB & thnx for the star


Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

Hi Chris,

I'd like to emphasise that I'm not defending Access as opposed to OS here (God knows that Access has it's problems); I'm simply trying to isolate the cause of the problem.

This thread currently states that MS Access IS the cause of the problem, but I can't believe this when a Win2K or XP pc allows Explorer to see a file - then state that it isn't there.

This must be a OS / network problem - the fact that an MS Access 'compact & repair' produced the file in question is regardless.

ATB

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[chris3942]

I think that when Access moves the file from the temp folder
the db file comes back with the "permissions" that were assigned to it while in the temp folder. The user on the network has privleges to files in the shared folder only. When the user on the network tries to use the just compacted db file, even though it's in the shared folder, the file has "permissions" as if it were in the temp folder and refuses to run for the user on the network because he/she doesn't have permissions to files in the temp folder. I found some info on the Microsoft Knowlege Base that may help explain what's happening. Here's the link to tip on the Knowlege Base:

http://support.microsoft.com/kb/295234/en-us.

Chris

 

[puforee]

Having read this I have a question. The DB's I create at work are also shared. To keep them compact I set the "Compact on close" option to on. This setting compacts the DB everytime it is closed no matter who opened it. Does this mean I could be looking at a problem because of the temp file security settings?

Thanks,

 

[Darrylles]

Hi puforee,

Well, it sounds like it might be a problem if your OS permissions settings are the same as Chris's OS.
I haven't come across this in some pretty tight security setting scenarios (International Aerospace).

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[mikew12345]

I've seen this kind of problem before, both in MS Access and VB6. They both have a problem with path/filenames where any part of it has more than one period. For example, if you try to open a file named my.stuff.mdb, you can browse to it in Access, but when you try to open it, it'll say file not found.