Here at our office, a small business with ~20 employees, we used to use a communal spreadsheet to keep track of job orders and everything associated with them. Of course, this meant that if somebody had some notes that needed to be added, and somebody else was already in the sheet, that they would have to ask around and see who was in it and have them close out, or wait… and wait… and wait.
I put together a MS Access database last year to replace the spreadsheet, which enabled multiple users to access the data at the same time. This worked great, but we recently hired a few new employees, and have had three database corruptions in the past two weeks. After looking into it, I determined that I apparently needed to split my database into a front-end and back-end, which I have done.
I now have two Access databases, one containing the various tables we use (clients, employees, job orders, etc) and one containing the various reports and forms we use to manipulate the data. The problem that I didn’t foresee is that one of our employees works off-site and doesn’t access the company server over the VPN in quite the same way that we all do. (He does not have assigned drives, which are referenced by the front-end to get to the back end).
Basically, I want to know if there is a better way for me to be handling things. The reason we do this is so our field personnel can add in comments about their progress on a job, or note that a job is completed so the office personnel can begin their work on it. The office personnel then do the same, and also mark things off so that the billing department knows when something is finished. The database is constantly being updated, all day, every day, from every possible direction.
So, is there another way that I should handle things? Something other than Access, or will Access be fine?
Access is not well-designed for multiple users. If you continue down this task you will be increasingly frustrated.
The problem that you describe is a common business challenge and not difficult to solve. There are off the shelf web-based database apps that you can customize for your own needs. You might even be able to use something like zoho.com.
Could you use relative paths for the front-end/back-end (because you’re talking about drive mapping, it sounds like you’re using file paths instead of linking in the tables).
Could you migrate the backend to SQL Server? Express Edition is free, and I’m working with it via Access at this very moment.
anson2995 is correct in that you will probably want to go with a web based database app as your ultimate solution. Someone with more direct experience will have to recommend one for you though.
In the short term, you might go back to the spreadsheets and set them to shared. Tools/Share Workbook on the menu. The only issue you will run into there is if two users attempt to modify the same cell at the same time. There are ways to coordinate around that though.
Then when the user saves their changes, it just updates what they changed on the server and pulls anyone else’s changes into the copy they have open.
Yep a server driven solution sounds like what you need.
i’d create a service that wraps around the database for clients to consume. A custom app for those local to the server, and a webapp for those telecommuting (or maybe even just a web app for everyone, if the requirements are light).
Don’t know of anything off-hand though, but Zoho looks promising. A simple custom solution like the one I mentioned shouldn’t cost that much.
Relative paths do require VBA in Access. However, you can link the tables from the back-end using UNC paths rather than drive-letter mappings, and this should work for your VPN user. While you’re doing this, you should probably make a separate front-end just for that user, which he can copy to his own hard drive. This way, only the data he’s using needs to be copied over the WAN, which should make a huge difference in speed.
Here’s how you do it: make a new copy of that front-end database, and delete all of your back-end links. Then, create new table links; but instead of browsing to the drive the back-end is on, type in the UNC path for the network share the drive is mapped to. Eg. if the drive shows in your My Computer as something like “E:\ (sharename on servername)”, you want to type in “\servername\sharename” into the box when you start to link the file. This will open up the E: drive using the UNC path, and that full path (instead of just E:) will be saved on the link definition. The table link will then work even for users who don’t have the E: drive mapped, as long as they have permissions on that network share.
Then, just have the remote user copy your new front-end to his PC. It still won’t be as fast as for a local user, but it should work. (I’ve done this more times than I care to admit …)
Edited to add: this is not intended to represent a superior solution to the one suggested upthread, which are probably much closer to optimal. It is just a quick & dirty fix that will let you keep using Access rather than learning a new application.