3

I have a MS Access frontend that I would like to install on 5-7 computers, so they can access a SQL Server stored on a shared network drive:

enter image description here

If possible, is it safe and can my data be corrupted? Otherwise, how I have 5-7 users access the SQL Server simultaneously by using an interface?

Thank you very much for your time!

  • 1
    I don't understand why you can't give your users installs of SQL Server Management Studio and restrict their access with the correct roles etc... – Molenpad Jul 5 '16 at 15:45
  • Hi! I didn't know that was an option. I started building a database with MS-Access, and then expanded it with SQL-server. Could I use MS-Access with SQL Server Management Studio somehow? I'm sorry if I'm confusing you. – Johnathan Jul 5 '16 at 16:12
  • @Molenpad not everyone is comfortable or capable of using SQL. I believe OP is using Access to create a user interface for end users. – Sir Swears-a-lot Jul 6 '16 at 18:44
5

If they have access to the tables directly with insert, update and/or delete rights then they can corrupt your data. If you prevent this by limiting the access-rights or by an MS-Access application then they can/should not be able to corrupt your data.

About business rules. According to Wikipedia: A business rule is a rule that defines or constrains some aspect of business and always resolves to either true or false.. These rules can be defined in the database as constraints but are more often checked by an application. For example a company can give a discount to a customer only in special cases. Or a client can only be deleted if it has no outstanding payments. If the business rule is checked by an application then giving somebody access directly to the tables with update and/or delete rights can corrupt your data.

  • Thank you for your answer! So, if I installed the MS-Access front-end on several computers, and people would use it simultaneously, the server should work properly, preventing corrupt data, right? – Johnathan Jul 5 '16 at 16:10
  • 1
    SQL-Server will correctly handle any simultaneous access to the data. For me corrupt data also means that it does not follow the business rules. The business rules will not be guaranteed by SQL-Server. – Marco Jul 6 '16 at 6:44
  • Hi Marco! Can I ask you what are the business rules? – Johnathan Jul 6 '16 at 13:28
  • @Johnathan I will add it to my answer. – Marco Jul 6 '16 at 13:50
7

Yes you can use MS Access as a front end to SQL Server. Its quite common. I have done this myself in the past.

No, Access itself won't corrupt data. However your Access application needs to be designed so that it respects SQL Server constraints and rules. Also how will your Application stop users from making simple mistakes, like deleting records if they're not permitted to.

The most common problem I had was dealing with concurrency. When 2 clients are doing things at the same time such as inserting a record. Do you let Access generate the primary key? or do you let SQL Server manage it? In either case after you've inserted rows how do you ensure that each client is still dealing with the same row?

This is a consideration for anyone developing a multi user system. It just takes some time and thought.

Some advice: Make sure that you define the primary key in SQL Server. If you don't do this Access won't allow you to update records as it can't guarantee a lock on a single record. Primary keys and indexes also have a huge impact on performance.

Edit: I've just been thinking about your Question title a bit more. You mention "security breach". If security is a concern, You need to consider how users will connect to the database. Domain authentication? or SQL authentication, and how you log actions taken by users.

  • Hi Peter! Thank you for your answer. :) Can I ask about what you mean by respecting SQL server constraints and rules? Thank you! – Johnathan Jul 6 '16 at 13:27
  • 2
    Primary keys must be unique, foreign key constraints, unique indexes, data must fit datatype. If you don't follow the rules SQL will reject the transaction and return an error to access. For example if you try inserting 2 rows with the same primary key. This is not a problem for SQL but in access it will appear as an error. This is how SQL enforces data integrity. – Sir Swears-a-lot Jul 6 '16 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.