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

0
votes

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.

1
ms-accesslinked-listadoadp
How is this question significantly different from the one you posed in stackoverflow.com/questions/2789736/… ? You might want to refer to that and explain exactly what issues raised in the discussion there have prompted you to think that you need to ask an entirely new question in order to get a proper answer. - David-W-Fenton
The questions are different. The other question asked for thoughts on Access 2003 vs. 2007. This question is asking users what they think of ADP vs. Linked tables. There does not appear to be any solid recommendation out there for users. Microsoft first says ADP is the way to go and then seems to reverse itself. - webworm
Just for the records: MS finally killed ADPs totally in Access2013 - user2097684

1 Answers

2
votes

ADPs have had no significant features added in a number of versions now. They also have some quirks compared to MDB/ACCDBs. There are probably less than 1% of the folks using ADPs who use MDBs/ACCDBs so support is much better for MDBs/ACCDBs.

The ADO part is immaterial as you can use either ADO or DAO.

If you use Windows authentication then there is no real difference between ADPs and linked tables as userid and passwords aren't stored in the linked tables metadata.

The number of users is immaterial. There is no reason why you couldn't have thousands of users in an Access FE against a SQL Server BE.