Prevent simultaneous users from using front end

[tommac]

I'm a volunteer supporting a local charity remotely. I have built them an Access front/end back end CRM, which has been in use for several years now on their LAN with both in house and remote users. Most users have their own dedicated front end in their own named network folder, but I know that several others use one, which is in an area of the network accessible to all.
In order to avoid the possible data corruption resulting from simultaneous users on the same front end, is there any way I can prevent more than one person from using this front end. The obvious way is simply to remove it from the network but that's a bit draconian and could cause some dislocation.
All suggestions gratefully received.

 

[CJ_London]

Easiest is to prevent any user from using it

perhaps something like this when the fe is opened

if currentproject.path=‘path where FE is stored’ then currentdb.exit

could make it friendlier by providing a message and even giving the option to copy to the users local drive then open the copied file

 

[tommac]

Thanks for the suggestion. Part of the problem is that their network is maintained by an IT contractor, who administers the network. Whenever I'm asked to add a new user, byt that user or their manager, I have to ask the contractor to create a named folder in an area I can access. I then place a copy of the front end in that folder. The contrator then creates for the new user a shortcut to the front end, and I'm not even sure where that short cut is located. It may be on the new user's laptop desktop or on a remote access desktop. New users come and go quite often and I suspect that new users simply connect to the common front end as the path of least resistance and that front end seems to be constantly in use, judging by the presence of the lock file.

If I follow your sugestion then it will effectively lock all users out of the shared front end for however long it takes the contractor to spring into action. I might give it a try though, as at least it will let then know what is happening. If there was any way I could identify the rogue users that would help. Then I could start the process of getting them an 'official' connection. As you've probably gathered I'm not very up on networking.

 

[CJ_London]

Perhaps you need to talk to your IT contractor and agree a strategy.

 

[tommac]

A fair point.

 

[The_Doc_Man]

It is always better to have the accdb file loaded on the c drive of the local PC.

Pat, I absolutely agree with you, but sometimes if you have an overzealous IT security guy (which I was cursed with in the Navy job), they "protect" the C drive by partitioning the physical drive into C and D drives, and require that anything end-user installed goes on D, while things installed by the IT staff go on C. And, of course, that means "D" drive for the "are you running locally?" question. Rare... but it happened to me.

You COULD try this: The File System Object can be used to find a drive for which there is a property called "DriveType" and it might be helpful here.

DriveType property (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

If that shared folder holding the front-end is on the network, you can locate CurrentDB.Name, use fso's .DriveLetter to find the drive letter (given the path) and then use the .DriveType to see if it is a fixed or network drive. There are sample codes for that in the linked article. Then disallow running from a network drive. Do NOT try this on the drive revealed by the tablename.Connect string (for a back-end table) because that WOULD be a network drive in this circumstance. But CurrentDB should do the trick. It would also work if you were starting with CurrentProject.Path, I think.

 

[tommac]

Thanks Guys.
Pat's suggestion looks promising, and I should have done something like that at the beginnning. I've got about 30 named non-sharing users though, so I think in the first instance I'll try renaming the shared FE file and see who yells. Then if there are more thn a couple. I'll revisit your suggestions.

 

[GPGeorge]

Thanks Guys.
Pat's suggestion looks promising, and I should have done something like that at the beginnning. I've got about 30 named non-sharing users though, so I think in the first instance I'll try renaming the shared FE file and see who yells. Then if there are more thn a couple. I'll revisit your suggestions.

A little advice on managing the relationship with the "IT Guy", aside from the solid basic technical advice above. I call it management by preferred beverage. Invite the IT Guy for a round of his or her preferred beverage and make friends. It can't hurt, and it might help a call for assistance get through quicker in the future.