patternsqlMinor
Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?
Viewed 0 times
breachsqlconnectingdoingbackcomputerssecurityfrontmultipleend
Problem
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:
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!
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!
Solution
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.
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.
Context
StackExchange Database Administrators Q#142960, answer score: 7
Revisions (0)
No revisions yet.