none
ADP Support in Access 2010

    Question

  • Is there an official statement from Microsoft about ADP support in Access 2010? Do we have to switch to linked tables over ODBC or is there a better, recommended method to access Sql Server form an Access application?
    Saturday, October 02, 2010 6:23 AM

Answers

  • "PHasler" wrote in message news:795d5b36-6d8e-43e2-9d17-1a168c197adb@communitybridge.codeplex.com...
    Is there an official statement from Microsoft about ADP support in Access 2010? Do we have to switch to linked tables over ODBC or is there a better, recommended method to access Sql Server form an Access application?
     
    There been no public statement, and a2010 supports ADP just fine.
     
    There is trade offs by using linked tables vs that of an ADP. ADP is better in that your queries you build are all native.
     
    The downsides of ADP to me are high sensitivity to the particular version of sql server, no local tables.
     
    And, for existing applications with lots of DAO code, moving to ADP means re-writing that code as ADO. So, if you talking about taking an existing application with VBA and dao code, then linked tables is a far better choice then ADP since you preserve the existing code, and very little need be changed.
     
    Right now, when I build a sql application, I use linked tables + DAO.
     
    For most things, you not going to see the performance.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    Saturday, October 02, 2010 7:23 PM
  • Hi PHasler,

    I've found some information about new project creating in A2010 here: http://office.microsoft.com/en-au/access-help/create-an-access-project-HA010341589.aspx?CTT=1#_Toc257281381

    Hope it will be useful!


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, October 05, 2010 10:10 AM
  • "PHasler" wrote in message news:83f858b7-a696-4471-8b8a-ebe28146a467@communitybridge.codeplex.com...
    Thanks for you answer. The reason I asked is that I couldn't find a way to create a new ADP Application in Access 2010. Thats why I imagine it's deprecated. Is that not so?
     
    It is harder to find in 2010.
     
    You simply have to supply the extension .adp when you create the file.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
     
     
     
     
    Tuesday, October 05, 2010 10:55 AM

All replies

  • Here you can find some information :

    How to create a DSN-less connection to SQL Server for linked tables in Access

    http://support.microsoft.com/kb/892490

    Saturday, October 02, 2010 1:43 PM
  • "PHasler" wrote in message news:795d5b36-6d8e-43e2-9d17-1a168c197adb@communitybridge.codeplex.com...
    Is there an official statement from Microsoft about ADP support in Access 2010? Do we have to switch to linked tables over ODBC or is there a better, recommended method to access Sql Server form an Access application?
     
    There been no public statement, and a2010 supports ADP just fine.
     
    There is trade offs by using linked tables vs that of an ADP. ADP is better in that your queries you build are all native.
     
    The downsides of ADP to me are high sensitivity to the particular version of sql server, no local tables.
     
    And, for existing applications with lots of DAO code, moving to ADP means re-writing that code as ADO. So, if you talking about taking an existing application with VBA and dao code, then linked tables is a far better choice then ADP since you preserve the existing code, and very little need be changed.
     
    Right now, when I build a sql application, I use linked tables + DAO.
     
    For most things, you not going to see the performance.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    Saturday, October 02, 2010 7:23 PM
  • "PHasler" wrote in message news:795d5b36-6d8e-43e2-9d17-1a168c197adb@communitybridge.codeplex.com...
    Is there an official statement from Microsoft about ADP support in Access 2010? Do we have to switch to linked tables over ODBC or is there a better, recommended method to access Sql Server form an Access application?
     
    There been no public statement, and a2010 supports ADP just fine.
     
    There is trade offs by using linked tables vs that of an ADP. ADP is better in that your queries you build are all native.
     
    The downsides of ADP to me are high sensitivity to the particular version of sql server, no local tables.
     
    And, for existing applications with lots of DAO code, moving to ADP means re-writing that code as ADO. So, if you talking about taking an existing application with VBA and dao code, then linked tables is a far better choice then ADP since you preserve the existing code, and very little need be changed.
     
    Right now, when I build a sql application, I use linked tables + DAO.
     
    For most things, you not going to see the performance.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com

    Have you played with local temp tables and other whatnot that you can do with SQL Server as alternatives to tables local to a .mdb file?
    David H
    Saturday, October 02, 2010 7:45 PM
  • "David Holley" wrote in message news:1d2be78d-1758-44ec-8c69-a7c946deeeaa@communitybridge.codeplex.com...

    Have you played with local temp tables and other whatnot that you can do with SQL Server as alternatives to tables local to a .mdb file?
    David H
    No, not really. The issue is not so much that you need a temp scratch pad of data, but that of having the data local and not having to pull it down the network pipe. There are often some cases in which you want data local to the application. So, in terms of just a temp working scratch pad, sql server can work fine. However, for cases when you need or want a local file, or in instances where you can do local processing of data and not pay any bandwidth penalty, then I find linked tables more flexible. There also the issue of having more then one data source (local, Oracle, and then SqlServer). So, access in these cases can be a real landing pad, and can work + consume different linked data sources all at the same time.
     
    As mentioned, if you were building a application from scratch to work with SQL server, then ADP's are a very much a viable choice.
     
    However, when you have lots of dao code, linked tables preserves that investment, and moving to ADP costs too much time for no benefit.
     
    Some of this thus comes down to familiarly. I still use and prefer DAO out of habit, but I also willing to admit that the ADO object model is a bit cleaner in my humble opinion from a design point of view.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
     
    Saturday, October 02, 2010 7:56 PM
  • Thanks for you answer. The reason I asked is that I couldn't find a way to create a new ADP Application in Access 2010. Thats why I imagine it's deprecated. Is that not so?
    Tuesday, October 05, 2010 9:35 AM
  • Hi PHasler,

    I've found some information about new project creating in A2010 here: http://office.microsoft.com/en-au/access-help/create-an-access-project-HA010341589.aspx?CTT=1#_Toc257281381

    Hope it will be useful!


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, October 05, 2010 10:10 AM
  • "PHasler" wrote in message news:83f858b7-a696-4471-8b8a-ebe28146a467@communitybridge.codeplex.com...
    Thanks for you answer. The reason I asked is that I couldn't find a way to create a new ADP Application in Access 2010. Thats why I imagine it's deprecated. Is that not so?
     
    It is harder to find in 2010.
     
    You simply have to supply the extension .adp when you create the file.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
     
     
     
     
    Tuesday, October 05, 2010 10:55 AM
  • Hi

    As far as I am concerned, there is no substitute to ADP

    It is by far faster than Access linked tables

    I have used access with linked tables and the headache associated with them such as :

    broken links, New tables added to app and how to distribute to users and accidental deletion of DSN by users was and is a nightmare

    I am running a full ERP system using ADP with zero issues using sql 2008R2 and new datatypes

    i have conducted numerous  tests on data retrieval speeds

    Using ADP/ADO if way faster!!!!!

    ADP Syntax is same as Transact SQL, you use the management studio to develop all back end database objects

    For small application the future should be SQL express 200R2 and ADP

    In my opinion Microsoft should completely ditch linked tables and have all back end in SQL

    Furthermore Microsoft should develop "mini SQL" to replace the current access databases

    Why support multiple syntaxes and the headache associated with it

    dchen

    Saturday, December 24, 2011 7:34 PM
  • I personally support your opinion that ADP is far more superior than linked tables when it comes to use Access as a frontend to a sql-server backend database.  However, if you can read the writing on the wall, it's clear that ADP will soon be deprecated by MS and that in fact, nearly all new development on ADP have already be stopped since many years.

    This is because MS has taken the decision of dropping COM/DCOM and to replace them with the .NET platform and this includes, of course, any technology that is based on COM/DCOM like OLEDB on which ADP is itself based.

    MS has already annonced that Denali (the next version of SQL-Server) will be the last version of SQL-Server that will support the OLEDB provider for SQL-Server and that after that, the next version not only won't support this provider but will actively block it (in case you would try to use a version of the OLEDB provider from an older version of SQL-Server).  This means that ADP won't work at all for any version of SQL-Server after Denali.

    Tuesday, December 27, 2011 3:02 AM
  • I've just been working in a thread on Answers.Microsoft.Com trying to help a user who says that he gets the error "This form or report contains changes that are incompatible with the current database format ....In order to save your changes you must remove any layouts that have empty cells and/or set the Hasmodule property to "No"" when he tries to save design changes on a form.  I suspect he's using Layout View that creates some strange (not visible) Empty Cell controls that can't be saved in a legacy format.

    Has anyone else seen this?  The article on the Office website implies that designing forms and reports in an adp is fully supported, but this belies that.

    The thread on Answers is here:

    http://answers.microsoft.com/en-us/office/forum/office_2010-access/i-created-an-adp-from-access-2010-but-it-is-in/4102aa5e-e0b5-4121-9805-3ed6b2fae9d9

    John Viescas


    John Viescas Access MVP since 1993 Paris, France

    Saturday, June 30, 2012 6:03 AM