Best Practices

[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.

 

[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!