MS Access - Linked tables vs Access Data Project (ADP)? Security?

I am trying to determine the best approach when designing a new Access based application. Due to decisions made by others I have to use Access 2003 as my front end and SQL Server as my back-end data store (I would have preferred to use Winforms/WPF and SQL Server 2008 but that is another story).

Originally I was thinking of using Access Data Projects (ADP) as data security is a big issue in this project and ADPs would allow me to store everything (except VBA. reports, and forms) within SQL Server. Problem is that many developers I have spoken to suggest that using ADO is something that Microsoft has tried and then abandoned. They suggest using linked tables will provide a more consistent and less buggy experience.

I was hoping to get some feedback on what others think the best solution might be. Additionally, I would be interested to know if others consider linked tables to be a security risk over ADP. Thanks for any assistance.

Edit ... just wanted to add that the number of people using this application will be small (10 to 20). I should also add that this application is being developed from scratch. No conversion is needed.

In http://stackoverflow.com/questions/2789736/access-data-project-adp-access-2007-or-2003 I've already commented that the security thing is a red herring -- security is in the back-end, not in the front end, and you actually expose the back end to more exploitation with an ADP than an MDB since the ADP can design the SQL Server objects.

David-W-Fenton 2010-05-10 22:19:11

So you are saying that the more "secure" option is to go with linked tables as the MDB has no way to alter the SQL Server objects while the ADP has the ability to modify SQL Server tables, stored procedures, etc ..?

webworm 2010-05-11 12:32:33

Why not use ADE's to prevent users from being exposed to the design elements of your app?

voon 2010-05-11 13:50:17

webworm. I'm not going to argue that one is more secure than the other. I haven't done a lot of research in this area. That said users shouldn't be running ADPs anyhow nor should they have those kinds of rights to those objects.

Tony Toews 2010-05-11 19:27:01

voon, then why not distribute the MDB/ACCDB as an MDE/ACCDE then? Which is what I would do anyhow.

Tony Toews 2010-05-11 19:27:40

Tony, I assume you're referring to linked tables in MDEs?.. In which case, yes, it is mostly the same. However, some minor advantages to using ADPs/ADEs are that they are faster than going through ODBC and the ability to run sql statements and sp's directly on the sql sever (which for MDEs you can work-around by creating a separate ODBC connection)

voon 2010-05-11 20:14:34

Is there a way to prevent a MDE or ADE from being opened by a user using Access? ADP/ADE was presented to me as the way to go for security reasons yet now I am not to sure. I was looking at linked tables only because the current Microsoft suggestions is to NOT use ADP.

webworm 2010-05-11 20:35:03

webworm, Put a table in SQL Server with the allowed user names with restricted update privileges. If they're not allowed then exit the app. API: Get Login name http://www.mvps.org/access/api/api0008.htm

Tony Toews 2010-05-11 23:05:05

voon, to me the minor advantages aren't worth the quirks and lack of past and future enhancements to ADPs.

Tony Toews 2010-05-11 23:05:55

@webworm: you seem to be confusing design of the front-end objects with design of the back end objects. I've never used ADPs, so I don't know if ADE turns off the back-end design tools or not. But it's rather a moot point in comparison to MDB/ACCDE and ODBC, since there are no back-end design capabilities involved there that need to be turned off.

David-W-Fenton 2010-05-12 21:42:13

@voon: Microsoft states that the ADP "speed advantage" doesn't exist, except for reporting. That's the reason why they have been deprecating ADPs in favor of MDB/ACCDB with ODBC for about 5 years now. Their explanation (the URL of which I can't locate right off) indicated that there are more layers involved between an ADP and a SQL Server than between an MDB/ACCDB and a SQL Server, and these intermediary layers drag down performance.

David-W-Fenton 2010-05-12 21:44:25

@Tony Toews: why would you need a table to store the user names? Shouldn't you be able to use the appropriate APIs to get NTFS user group membership to determine if a user has permission? Or, for that matter, just attempt something that the non-allowed user can't do, and trap the error...

David-W-Fenton 2010-05-12 21:46:19

@webworm: I don't think there's any security advantage to ADP/ADEs at all. This is why I've objected to that as a reason to use ADPs. Perhaps the person who suggested this to you can provide an explanation. From where I sit, security is a back end issue, except in regard to protecting your front-end objects from design changes.

David-W-Fenton 2010-05-12 21:47:45

Thanks for the feedback "David-W-Fenton". Might you be able to suggest a resource (i.e. book) that might cover Access client/server development using linked tables? The only texts I have found keep talking about using ADPs for Access client/server development. No doubt this is because at the time these books were published ADP was being pushed heavily by Microsoft. The points you make seem to make a lot of sense. I am just trying to grasp why Microsoft would push ADP over linked tables when linked table are actually more secure.

webworm 2010-05-13 13:15:13

@David-W-Fentin. Hrmm, shame about the extra layers slowing down ADP performance...What about when you run join queries? For an ADP, SQL server would do the join on the server and then send the results. With linked tables, wouldn't Access need to retrieve the tables involved locally to do the join?

voon 2010-05-13 19:14:00

Vonn, you are correct although Access/Jet/linked tables will do their best to run as much on the server as possible. The solution though is to link to your views and stored procedures from within Access so they appear as linked queries. Then you run those natively from within Access.

Tony Toews 2010-05-13 23:07:54

For me, the bible is Andy Baron and Mary Chipman's SQL Server for Access Developers book: http://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446/

David-W-Fenton 2010-05-14 02:27:27

You're absolutely correct that the books published when A2000 first came out (the version that introduced ADPs) all drank the Kool Aid. The problems didn't become obvious for a couple of years and a couple more versions of Access. ADPs were a moving target and ADO was too smart for its own good. Many people tried very hard to make ADPs work and gave up on them.

David-W-Fenton 2010-05-14 02:28:43

Security is not the reason MS was pushing ADPs over MDBs. I don't know who came up with the idea that ADPs are more secure than MDBs, but whoever it was really didn't understand any of the factors involved. Security is a back-end database issue, not a front-end issue.

David-W-Fenton 2010-05-14 02:29:52

When using a linked table solution does Access bring any data down to the MDB/MDE file? Even for processing?

webworm 2010-05-14 11:36:09

Would it be safe to say that by using linked tables a developer can accomplish all of what ADP were designed to do as long as all objects (tables, views, stored procedures) are managed on SQL Server?

webworm 2010-05-14 11:41:26

Of course Access brings data down to the local PC. It has to do that to display it in a form/report, etc. If you've properly designed your MDB/ACCDB, all the data processing will happen on the server, and all that will happen locally is the rendering of the data. The difference in this regard between ADP and MDB is that ADP will do 100% of processing on the server, while MDB will do 100% on the server unless you make a mistake that causes Jet/ACE to think it has to do the work itself (or you encounter one of the circumstances in which Jet/ACE guesses wrong about what can be handed off).

David-W-Fenton 2010-05-14 19:57:08

"all of what ADP were designed to do" -- no, of course an MDB/ACCDB can't do all of what the ADP was made to do. But in terms of what the ADP can do as a front end, yes, MDB/ACCDB can do it all.

David-W-Fenton 2010-05-14 19:58:10

ansaurus

tags:

views:

answers:

MS Access - Linked tables vs Access Data Project (ADP)? Security?

related questions