I was called to office when the new Access database system I designed failed to open on one workstation.
As I searched the web, thanks to obrienpubs, it turns out that for some XP systems (workgroup network sharing, not set to connect to domain, in my case), it will not allow the shared file from another computer to be opened when the access file is in use by another (No warning/error/popup message). And if the file is not being used by others, it will open after a popup: Open File - Security Warning...Unknown Publisher, Do you want to open this file?
In my case, I solved the problem a little differently.
I went to Control Panel-> Security Center-> Internet Options-> Security Tab-> Local Intranet->Sites...button->Advanced...->Typed in the network name of the sharing computer and click Add. Then restart and Voila!