Access Data Project (ADP) - Access 2007 or 2003?

views:

203

answers:

+1 Q:

Access Data Project (ADP) - Access 2007 or 2003?

I have been tasked with writing an ADP application using Access. The back-end data will be stored in SQL Server. We have SQL Server 2000, 2005, and 2008 available to us. Requirements dictate that Access must be used but I may have a choice of using Access 2003 or Access 2007. My first thought was to push for Access 2007, because it was newer and we could use SQL Server 2005 or perhaps 2008, however, when searching the web for Access 2007 and ADP I find a lot of posts from people saying the speed is very slow and they are running into lots of problems getting Access 2007 running with ADP.

If you had the choice of using Access 2003 or Access 2007 for an Access Database Project which one would you go with and why? Thanks.

+2 A:

Personally, I find Office 2007 to be ok, but the Access 2007 UI really gets me frustrated! The object text search feature is useful, but I miss being able to see a full list of objects, with descirption.

As to the data side of things, if you have a free choice, I'd use a MDB file and link to SQLServer using ODBC linked tables, rather than use a ADP. Apparently, ADPs are deprecated as they are less efficient. See the comments here. Here's an excerpt:

But not going through Jet is a drawback, because Jet is so smart and efficient. ADO has terrible problems (like bypassing views and going direct to the underlying tables for otherwise unupdatable DML commands) and ADPs turn out to have more layers between them and the data than MDBs with ODBC. This is why MS has been deprecating ADPs in favor of MDB/ODBC for about 5 years now.

mdma 2010-05-07 15:41:06

ADPs are designed for use with SQL Server and can be much faster than mdbs.

Remou 2010-05-07 15:53:02

The reason that ADP is being mandated is for security. DBAs want to control access to views, tables, and stored procedures via SQL Server. They do not want any data stored within Access, only SQL Server.

webworm 2010-05-07 15:58:59

I'm sure you know, when you link tables via ODBC no data is stored in access. Of course, you may be concerned that users will create tables and store data there, but you can prevent this by setting user permissions access database. Anyway, I don't personally think ADPs are that bad - just passing on the advice I was given. :-)

mdma 2010-05-07 16:02:39

Do you think linked tables via ODBC is a better way to go?

webworm 2010-05-07 16:39:41

I'm not sure. Linked tables can be a pain if your SQL schema is changing often - you have to keep relinking the tables. I've also seen problems when Access thinks I have a composite PK when the table has a unique composite index (representing the natural key) an doesn't see the PK. This causes update problems - newly inserted rows are reported as deleted, in acess (but are still there in SQL server.) On the other hand, we didn't like using ADP because it fetches 10,000 rows maximum, and my colleague wanted to see all the data. (You can change this in the program settings.)

mdma 2010-05-07 16:50:07

A feature with MDB/ODBC is that you can link tables from more than one SQL database, which can sometimes be useful. In my case, it was also useful to separate the core data from application-specific queries (applications were basically end users using access to query data - knowledgable users that wanted to be able to create their own queries.)

mdma 2010-05-07 16:53:53

@Remou: MS itself does not believe that ADPs with SQL Server are faster than MDB/ODBC, because they have deprecated ADPs in favor of MDB/ODBC except for reporting.

David-W-Fenton 2010-05-07 19:06:39

Mandating ADPs for security purposes is CRAZY. ADPs provide design access to the SQL Server (if the user has the appropriate permissions), something an MDB with ODBC linked tables has never provided. To me, they have it completely backwards! Either way, security roles should be used on the SQL Server to tightly control access, but keep in mind that if you lock down the base tables and create views with permissions defined for user roles, an ADP will sometimes attempt to bypass the views because it's using ADO, which is too smart for its own good in trying to make non-updatable SQL updatable.

David-W-Fenton 2010-05-07 19:09:06

ansaurus

tags:

views:

answers:

Access Data Project (ADP) - Access 2007 or 2003?

related questions