January 9, 2008 at 2:00 am
Hi, I have some experience developing an Access database for my department (but by no means a full-time Database developer). Now...as a millionth person on earth, I'm trying to upsize my Access database to SQL database, but get stuck at one early dilemma: Should I modify the existing front-ended MBE into ADP file, or alternatively maintain the front-ended MBE but instead link to all the tables on SQL server via ODBC connection? Both options are linked tables which reside on SQL server...Is there any difference? Pros and Cons? My most important concerns are the speed and the reliability of the database.
Thanks in advance for the advice.
January 10, 2008 at 8:14 am
I have upsized several MDB projects to SQL, starting with SQL 2000 near it's original release date. I never felt that the upsizing and table linking was a 'big-bang' for the conversion bucks. This thought was confirmed when we started upsizing the data to SQL and the rest of the objects and code to an ADP. The ADP offers you native sql integration. You will see the views, functions and stored procedures you will be creating on the sql server. Plus you will be able to alter you forms and reports to use stored procedures, passing criteria via the parameters, allowing the sql server to return only what you need. 'Big-Bang' for your conversion buck.
It will take you longer if you have older dao code, because it will need to be translated to ado. But the payoff should be near future for you.
[font="Arial"]Clifton G. Collins III[/font]
April 24, 2008 at 6:47 pm
Members of the Access team and MVPs have adivised me to avoid ADP (and ADO) unless required because: MDB and DAO is a richer function set, it provides more flexibilty, and it is much less expensive (time consuming) to develop and maintain. ADP requires much more REAL programming to work around the more limited feature set of ADO.
While the ADP/ADO paradigm was recommended by MS circa Office2000, it was a response to the slow networks of the immediate past. By Access2003, it was no longer being promoted. Now, ADP projects have been deprecated in Office2007 which implies that it will soon no longer be supported. The new ACCDB extension (an extension of JET) indicates that JET (now called ACE) is alive and well as a route to SQL.
April 25, 2008 at 3:34 pm
It is interesting how different people at the same company give the same but not the same advice. I still work in MDB as well as ADP projects in 2000, 2003/XP and 2007 environments. I can say that when MSSQL is involved the adp/ado partnership is much cleaner, faster and easier to maintain. ... and.. it is apparently still in production, not deprecated. But don't take my word on any of this, experiment with both and arrive at the best course of action for your particular project.
See Microsoft's site for Office 2007:
http://office.microsoft.com/en-us/access/HA101679531033.aspx
[font="Arial"]Clifton G. Collins III[/font]
May 15, 2008 at 3:10 pm
Several times I've heard rumors of the "deprecation" of ADPs but have never run across anything concrete to substantiate it. In fact, I believe I remember reading that support for ADPs is actually improving with Access 2007. (Or was it SQL Server 2008?)
Is there anywhere on the Microsoft site where they come right out and say what their long-term plans are for ADPs?
Scott MacCready (4/24/2008)
Members of the Access team and MVPs have adivised me to avoid ADP (and ADO) unless required because: MDB and DAO is a richer function set, it provides more flexibilty, and it is much less expensive (time consuming) to develop and maintain. ADP requires much more REAL programming to work around the more limited feature set of ADO.While the ADP/ADO paradigm was recommended by MS circa Office2000, it was a response to the slow networks of the immediate past. By Access2003, it was no longer being promoted. Now, ADP projects have been deprecated in Office2007 which implies that it will soon no longer be supported. The new ACCDB extension (an extension of JET) indicates that JET (now called ACE) is alive and well as a route to SQL.
May 15, 2008 at 6:07 pm
'As my friends say, be careful what you ask him [me].'
I think the answer to your question is in the tea leaves.
The SS team may have improved ado (and therefore adp) in sql08 but perhaps only as a side effect of attempting to fix issues for non-Access users of ADO. (I don't know anything about this.)
At the time, ADO was introduced to MSACCESS, JET (both Jet 'BLUE' - the Office/VB version and JET 'RED' - the engine used for Exchange) were both 'owned' by the same part of MS that owns SS (if I correctly understood something I read elsewhere). That group wanted to replace Jet RED with SQL (remember the unmet promises for Exchange 2003 and 2007?) and wanted to replace Jet BLUE with things like MSDE. They even went so far in Office2000 as making ADO the default protocol. However, by Office2003, this was no longer 'preferred' and in 2006, one of the top product managers on the MSACCESS team told me that they weren't 'recommending' ADO and ADP anymore (but not discouraging it, either). Another top hand in testing explained publicly in 2006 that his 'favorite' flavor was DAO due to the greater flexibility. They seemed to be nudging folks toward that old-style religion while trying not to offend the other sect. The MSACCESS team seemed to be hinting that DAO and MDB (pre-Office2007) was an advisable strategic move, at least for the middle future (i.e, into Office2007).
You might Google or Live to find a lengthy and hot thread between Michael Kaufman (formerly on the Access Dev Team) and Jamie Collins (I think) (also currently an MS employee), on Michael's blog (also check the still 'up' but mostly dormant site of his former firm -Trigeminal Software) about the religious controversy of ado vs. dao (maybe a year ago). You will learn that MS introduced ADO to MSACCESS to replace DAO [on the way to replacing ADO (and MDBs?)]. But, it didn't and therefore won't.
I spoke with Michael about this just two days ago. Here is his reading of the tea leaves: The Office (MSACCESS) team now 'owns' DAO and JET 'blue'. They wouldn't have acquired it if they didn't have plans. And, we see they did. ACCDB is a superset of MDB. Thus, the announced cessation of enhancement that accompanied the introduction of JET 4.0 is no longer operative. His take seems (to me) to be that since that is what they own and where they invested (creating ACCDB), this is an important part of their future. In addition, he thinks the future of ADO may be moving away from MSACCESS as he feels that ADO 'plus' as he put it, is not so compatible as the older architecture of ADO.
However, DAO may not be their entire future since, clearly, OFFICE generally is working hard to utilize WSS (and thereby SQLServer) as a backend. The fact that Gate's sole remaining functional role in s/w dev at MS is with the Office line implies to me that we should expect to see much more innovation (including in the backend) from them in OFFICE14 and beyond.
I rub elbows (actually their elbows almost reach the top of my head) with several VERY accomplished and successful commercial ACCESS developers who seem to be betting their business on DAO/MDB and using SQL (Express or higher) as a back-end where needed. They don't GENERALLY (not to say EVER) see ADO/ADP as the better path. These folks are also 'inside' MSACCESS, being used by the team to vet new features, strategies, etc. years ahead of product release. My surmise from observing the behaviour of these expert, experienced and best informed practioners is to go with MDB/DAO model. BTW, they don't like the multi-valued fields of ACCDB but will use it over MDB where other 'extended' features are useful.
May 16, 2008 at 1:58 pm
Thank you Scott for the detailed, informative and thoughtful reply.
I did look up the discussions you mentioned. For the benefit of others. I'll mention that Michael's last name is Kaplan (not Kaufman).
The following two links were enough to get my head spinning for a while:
http://blogs.msdn.com/michkap/archive/2007/07/13/3849288.aspx
http://www.eggheadcafe.com/software/aspnet/31568835/adp-vs-odbc.aspx
May 16, 2008 at 2:29 pm
OK, I remember now. Supposedly Access 2007 ADPs actually work with SQL Server 2005, in that you can modify database objects directly. Lacking Access 2007, I haven't actually tested this, but I hope it's true.
When we upgraded to SS 2005, I had to set it to "2000 compatibility mode" for it to work with our existing Access 2003 (and 2000) ADPs. Even so, it only supports "read-only" database access from Access. (Side note: A curse on Microsoft for giving their products such common names. :P) So... table (schema) updates, stored procedures, diagrams, etc. have to be managed directly through SS Management Studio. As much as I am a SQL Server (and Oracle) bigot, I have to admit that the Access interface to database objects is in many ways friendlier and easier to use than SS Management Studio or Enterprise Manager.
michael.tharp (5/15/2008)
... In fact, I believe I remember reading that support for ADPs is actually improving with Access 2007. (Or was it SQL Server 2008?)
May 16, 2008 at 5:13 pm
Thanks for the correction. How embarrassing? Very.
I hope Michael doesn't see it.
May 4, 2009 at 9:14 pm
thank you to gave this article:-P
May 7, 2009 at 4:19 am
Here you have documentation:
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply