Access as a front-end to SQL Server - ADO vs DAO?

views:

184

answers:

+1 Q:

Access as a front-end to SQL Server - ADO vs DAO?

I have a project that will be using Access 2003 as the font-end and the data being stored in SQL Server. Access will connect to SQL Server via linked tables with all the database logic (stored procedures, views) within SQL Server.

Given this setup, would it be better to use ADO or DAO within Access? Is it just a matter of preference or is one more suited to Access as a font-end and SQL Server as the data store? Especially when using linked tables. Thanks.

+1 A:

Write pass-thru queries as opposed to the linked table approach. Performance will be greatly improved. Writing an Access app?

Create some local tables to store some query results. You can dynamically change the pass-thru queries as needed as well as connection info for connecting to multiple databases.

Edit: General consensus is ADO for connecting to sql server/other sources and DAO for mdb only sources.

buckbova 2010-06-11 20:23:06

Pass-Through queries can be used with linked tables, they are not mutually exclusive.

webworm 2010-06-11 20:31:01

@webworm, performance is the issue and not compatibility. Better off making requests for as limited amount of data as possible instead of an entire table.

Jeff O 2010-06-11 20:40:37

Linked tables and pass-through queries are not mutually exlusive. Use linked tables where reasonable and use other techniques when perforamnce demands it. Then you get the best of both worlds. Also with SQL Server local tables aren't usually required.

Tony Toews 2010-06-11 20:56:19

Your assessment of the consensus is WRONG. With ODBC linked tables, you use ADO only for those things that you can't do otherwise, or that aren't efficient. This means only occasional use of ADO (mostly for getting editable recordsets for things that are otherwise not, e.g., recordset-returning sprocs, etc.).

David-W-Fenton 2010-06-11 23:58:23

Jeff, your concern about "entire table" is rather interesting. Are you one of those people who believe that Access brings down the entire table every time you open a recordset or form?

Tony Toews 2010-06-12 21:08:46

To draw out what Tony is saying, Access/Jet/ACE will not pull down a whole MDB, ever, nor will it pull down a whole table *unless* you've worked very, very hard to do everything completely wrong (e.g., no indexing, selecting on an expression).

David-W-Fenton 2010-06-13 21:22:25

DAO was used to access MDB databases from any COM application. I don't believe it can even connect to SQL Server.

ADO is a successor of DAO. ADO can connect to many databases, but interacts especially well with SQL Server. So in your situation, using ADO seems like a no brainer.

Andomar 2010-06-11 21:46:41

This answer is wrong on the facts about DAO vs. ADO. ADO is *not* the successor to DAO, but the successor to ODBC. MS pushed ADO in the A2000 time frame in an effort to push aside ADO and the Jet database engine, but that failed (for very obvious reasons -- it was a bloody stupid idea). Classic ADO is dead, having been superseded by ADO.NET, which, despite bearing superificial resemblences to classic ADO, is a completely different animal. DAO is the interface to the Jet/ACE database engine, through which you can access just about any data store on the planet.

David-W-Fenton 2010-06-11 23:56:36

@David-W-Fenton: As far as I know, the successor to ODBC was OLE DB. ADO was a COM wrapper around OLE DB. Pushing aside the Jet engine was a very good idea: the Jet engine was efficient, but answered many queries incorrectly

Andomar 2010-06-12 00:35:39

But MS has changed it's mind and gone whole hog into further commitment of the Jet database engine starting with Access 2007. It was *never* a good ideas for Access, though certainly Jet didn't fit well at all into the .NET family of development tools. Anyone claiming MS has deprecated Jet with Access is reading KB articles applying to MS's larger family of development tools and mis-applying their advice to Access, where Jet/ACE is anything but deprecated -- it is being enhanced and promoted to a great degree.

David-W-Fenton 2010-06-12 20:18:56

On the OLEDB vs. ADO, yes, of course, but it doesn't change the point. ADO was *not* a successor to DAO at all. Your answer is wrong and needs to be edited or deleted.

David-W-Fenton 2010-06-12 20:19:48

Microsoft promoted phasing out DAO in favor of ADO for a long time; why else would they have developed the ADP? If they changed their mind in 2005, I wouldn't know about it, my users are still on Access 2003.

Andomar 2010-06-12 21:11:51

Well, ADO/OLEDB/ADO.NET were perhaps MS's replacement for DAO for it's post-VB6 development tools, given that MS had promoted DAO/Jet as a common data interface with the VB family of tools (I don't know what they used in their C-based tools), so I guess you are right in that sense, but ADO/OLEDB is very different from DAO. ADO, like ODBC, is a generic database abstraction interface, while DAO is a interface to Jet/ACE, which provides access to other data sources. This is a big difference, and why ADO was never going to replace DAO in Access.

David-W-Fenton 2010-06-13 21:26:15

ADPs were developed as a way to further MS's enterprise database agenda. It was not something that was helpful to the future of Access. The fact that MS now deprecates ADPs in favor of MDB/ACCDB with ODBC shows that, as well as the fact that ADO stopped being the default data interface after A2000. ADPs have always been highly flawed (and a moving target -- each of the three versions has different bugs/gotchas, some of which are regressions), and not nearly the performance improvement that was promised (which is why they are now deprecated).

David-W-Fenton 2010-06-13 21:29:06

+1 A:

Use an MDB with ODBC linked tables. Because you are using ODBC, you are connecting via Jet, so it's obvious that DAO is the default choice for data access.

ADO should be used only for those things that can't be done otherwise, or that are performing poorly.

In short, you build your SQL Server app just like you would a pure-Access app (assuming you build an app with a Jet/ACE back end to retrieve data efficiently, which should be a no-brainer), and only resort to server-side functionality or ADO when the default Access approach is inefficient or doesn't get the result you need (e.g., an editable recordset in the case of recordset-returning sprocs).

David-W-Fenton 2010-06-12 00:01:04

-1 An Access front end to SQL Server is not an MDB file (it's an ADP file)

Andomar 2010-06-12 00:29:57

Er, what? Please undo the downvote, as you are simply wrong. An MDB as front end to SQL Server via ODBC is MS's recommended configuration. MS has been deprecating ADPs with SQL Server in favor of MDBs for almost five years now. The original question asks about DAO, and that shows right there that an ADP is not being contemplated in the first place, since DAO doesn't work in an ADP, since there's no Jet involved.

David-W-Fenton 2010-06-12 20:17:06

Andmar - ADPs haven't had any significant enhancements for years, maybe even a decade. They also have quirks.

Tony Toews 2010-06-12 21:07:04

@David-W-Fenton: My brain usually switches from reading to fighting mode after parsing "you are simply wrong", but I'll give you the benefit of the doubt ;)

Andomar 2010-06-12 21:14:08

Thanks for reading for substance instead of getting hung up on style.

David-W-Fenton 2010-06-13 21:21:12

Could someone explain today's downvote?

David-W-Fenton 2010-06-22 01:25:41

ansaurus

tags:

views:

answers:

Access as a front-end to SQL Server - ADO vs DAO?

related questions