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.
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.
Here's an option that gets the IT person out of the loop once it is set up ONCE.
1. Have the IT person create a hidden folder on the network. Only you, the IT Guy and a system admin should be able to see the folder. Other people have access to it but they can't see it by navigating the drive.
2. Place the master copy of the FE in this folder.
3. Create a .bat file that copies the FE to the user's local drive.
4. Place the .bat file in this hidden folder.
5. In this folder create a shortcut that opens the batch file.
once this is all set up, send existing users a link to this shortcut with instructions to add it on their desktop. That completes the transformation.
To add future users send them a link to the shortcut with instructions. You don't need to involve the IT guy at all.
I have the .bat file always download a fresh copy of the FE. Some people have the .bat file compare versions and only download if there is a new version. I prefer to always download a fresh copy. That way you never have to worry about corruption or bloating. The overhead is minimal so that isn't a consideration.
When you want to distribute a new versions of the FE, my strong preference is for you to do this outside of business hours, especially if there is also a BE change. To distribute a new FE (no changes in the BE), just zip the old FE and keep it for a while in the folder in case you need to revert for some reason. Then the next time a user opens the shortcut, the new version is automatically downloaded. Most of the time, unless you are correcting a bug, it won't matter if a user continues to use the old FE for the rest of the day. It is only if there are BE changes or serious bug fixes that you will have to force the users to shut down and transition.
The .bat file has four instructions. Here is a sample used by one of my clients:
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
The first line makes a directory on the user's C: drive. Mine is named dwglog (drawing log). Give yours a meaningful name
The second line deletes the current local copy of the FE. My app name is DrawingLog.accdb. Use your app name
The third uses UNC notation to copy the master FE from the server to the local directory. Using UNC notation eliminates the problems caused by users mapping network drives inconsistantly.
The fourth line opens the application.
This .bat file is as simple as it gets. I didn't bother with error checking because neither of the first two lines would raise an error if they failed and you want to get an error if 3 or 4 don't work so the user will call you. Once the .bat file has been tested, it won't "break" unless someone makes a change without informing you.
In addition, you will want to add code to the FE so that if the starting directory is not the "c:\DwgLog" (obviously, use your own name) then the code displays an error message and closes down. To add a little more security, you can add a version table to the FE and a version table to the BE. When you update the FE, update both tables so they reflect the newest version number. Then in addition to checking the startup directory, run a query that joins the two ver tables and compares the versions. If the versions are different, do NOT open the app.
This will probably inconvenience somebody initially but once everyone is correctly set up, you shouldn't have to worry about it.
Running the FE from a server directory is not recommended. For starters, you are having to create new directories for each user. But, since Access loads the FE into memory when you open it and there is a lot of communication between what is loaded in memory and the actual file on the server, this can slow down the app unnecessarily, especially if your LAN is slow or prone to dropping bits. 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.
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.
@The_Doc_Man ,
If the PC's are dumb and so everything runs from a server partition, you can always use the Citrix version of the .bat file which uses the user profile as a place to hang the FE.
Code:
md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb
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.
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.
