Best Practices (1 Viewer)

[JustPunkin]

I suspect this is a pretty basic question, and I also suspect I know the right answer, but would like some confirmation.

I have created a database that will store various features of our product. I have a backend that contains the tables.

I was in the process of creating one front end that would allow the user to both enter and access the data. But, my sleepless thoughts wandered last night and made me wonder if would be better to have to separate front-end apps - one for entering data. I could have it open directly to the form for entering data, and it would change very infrequently. And, then a separate front-end app for accessing/searching the data. I anticipate this one has the potential to change more frequently.

Also, I would have the users put the front end for entering data on their personal drives; whereas I would like to keep the search one on the network drive. What is the risk in doing that?

Thanks! Kind of new to this

 

[Mike Krailo]

Even though the read only database is safe for viewing on the network, it is much more efficient for users to have the front end on their local machine. The forms don't have to be pulled over the network, just the data.

Another option is to have one front end that opens in data entry mode for only certain machines on your network. All others open in read only mode.

 

[Cronk]

There is increased risk of corruption if multiple users are accessing the one FE database.

 

[The_Doc_Man]

There are so many ways to skin this cat that we would be buried in fur by the time we finished the discussion. Not to mention some ticked-off cats.

FIRST AND FOREMOST, I agree with Cronk but will state it more emphatically. NEVER EVER in a gazillion years should you share a front-end that will be used by multiple users at once. NEVER EVER in a gazillion years should you allow users to directly touch the back-end either (though you didn't say anything about sharing the BE.)

The reason you don't share on the network like that is that Windows file locking is done for a file on the machine that OWNS the file. Which means if you have a bunch of users in any file, front-end OR back-end, their file locks are managed across the network, which is hundreds to thousands of times slower than having the locks managed in the memory of your local machine.

You CAN put FE files on each user's workstation or laptop or whatever, particularly if nothing much in those files changes often. You WILL take out locks on the FE file and contents, but memory operates at GHz speeds, networks operate far slower. Also, since the FE files are stored on EACH machine, the locks have no contention because nobody but user A ever touches files on user A's machine. No competition for those resources.

You can share the BE through Access if you set up all queries and other BE interactions to use OPTIMISTIC LOCKING or NO LOCKS. (I prefer to use OPTIMISTIC locks myself but admit it as a preference, not a rule.) Access DOES take out BE locks but they are very short-lived if you use OPTIMISTIC locking. So there is a window of opportunity to run into lock conflicts but it is very short.

There are many ways to manage your goal. Let me suggest that you use the forum's SEARCH facility to look for articles on "User Roles" - because that is what you are discussing by having limited access to a form to control your data. By isolating certain functions, you are "securing a database" - which is another topic you can examine here using the SEARCH facility. Sometimes for newer users, the problem is not that you don't know what you want to do but you don't know how to do it AND don't know what to ask because you don't know what WE call that particular action. So I've given you two topics that I believe relate to your end goal.

 

[Pat Hartman]

Even though Doc and the others have covered the issues, I'll add my two cents (since we're using idioms) It sounds like you don't have an automatic distribution method for the FE or you wouldn't be worried about changes to the FE. If you need help with a distribution method, there are two that are commonly used.
1. Shortcut to a .bat file
2. Shortcut to a distribution database

There are pros and cons to both. the .bat file is trivial but there are several distribution databases posted here so you don't have to create one from scratch.

I use the .bat file method. My .bat file distributes a new copy of the FE each time it runs. The upside to this is that I never have to worry about bloat or corruption in the FE. It also ensures that the saved querydefs always get fresh statistics from the BE when they run the first time so that the execution plans are always up to date. I also have version tables in both the FE and BE. And the BE version table is linked to the FE. When the FE opens, the opening form checks the versions and makes sure that they match. Each table has both FE and BE version numbers. That way, if the user accidentally opens the local FE rather than the shortcut and it happens to be old, I can give him a message and refuse to open the FE.

 

[JustPunkin]

Great responses, thanks everyone. You have given me a lot to look into and research. I'm sure I'll be back with more questions!