Hi,
My company uses a fairly complex Access application to manage our purchases, sales, inventory, accounting, etc. The front end is an .accdb file that originally had linked tables connected to another .accdb file that served as the back end.
A few years ago, the back end was converted to SQL Server. When this was done, every table from the .accdb back end was copied to a SQL server table with the same name. Other than changing the links in the front end .accdb so that the linked tables now connect to the SQL server back end tables, the front end .accdb application was hardly changed at all.
The way this was done, I feel that the advanced capabilities of SQL Server were not taken advantage of. All of the forms and reports in the front end are filled by queries that are defined in the front end rather than the back end. It seems to me that to truly improve performance, rather than setting up table to table links from front end to back end so that the front end is still doing most of the "work" (processing queries, etc), it would have been better to create queries and stored procedures in SQL Server so that more of the "work" can be done on the back end.
We are now getting involved with a larger company that has "security concerns" about us using Access as the front end in this manner. I am going to meet with the IT folks from this company so that they can explain their concerns in detail, but I am trying to prepare in advance.
It seems to me that with the front end having direct links to the back end tables, someone with bad intentions who gained access to any computer running the front end might be able to bypass the interface defined by the application and directly manipulate the back end tables. I think that security could be enhanced if rather than having table to table links from the front end to back end, if we instead created views, stored procedures, or queries in the SQL server backend that returned read only recordsets to fill the front end forms and reports, and stored procedures for adding and/or modifying data when necessary.
It seems to me if I did this, it would be easier for us to limit what someone with bad intentions would be able to do if he/she gained access to one of the front end computers. I figure it would be easier to secure a single server than multiple client computers.
I am just wondering if anyone here can comment as to whether my reasoning makes sense. If I limit the capability of the front end by moving functionality to the back end as I describe, do you think I should be able to continue using access for the front end interface? Or is there anything about access that makes it less than ideal in terms of security? Is there an alternative to Access that I should consider for the front end? Also, with improving security being our main objective, is there any reason I should consider something other than SQL Server for the back end?
Any advice or suggestions will be greatly appreciated.
Thanks and best regards,
Paul
