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

问题:

I have a project that will be using Access 2003 as the front-end and the data will be 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 front-end and SQL Server as the data store? Especially when using linked tables. Thanks.

回答1:

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.

回答2:

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).

回答3:

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.