Which is "generally: faster for viewing recrods on a form:

1.  Using a recordset created with ADO 
2.  Using a View out on the MS SQL7

I am trying to figure out if I am better using Access ADP or MDB for my 
front end.  

-Mitch

Where possible, using an ADP is usually preferable to using 
an MDB to connect to SQL Server, and set-based SQL will often
perform identical tasks quicker than using a procedural language
to loop through an ADO recordset.

A word on MDBs vs ADPs:

When you use an MDB you are linking SQL Server tables (and 
views too, if you want) to your Access app, just like you 
would link Access tables and queries.  Of course that means 
that you can?t work directly with certain SQL Server objects 
or object properties when using an MDB. On the other hand, 
because you?re linking, not much of anything has to change on 
the Access side, even if query, form, and report objects already 
exist. This is because the Jet engine is still a player, along 
with the ODBC API. You don?t need to know T-SQL and you don?t 
have to create SQL Server objects. You work with the linked SQL 
Server tables and views just like you?d work with linked Access 
tables and views; all the Access query tools are available to you, 
including Access-specific stuff like crosstab queries. The up 
side of the MDB approach is speed of implementation. You get an 
instant Access app that uses SQL Server data just by creating a DSN, 
selecting File | Get External Data | Link Tables, and selecting 
ODBC databases. The downside is that the performance and scalability 
of your Access app usually doesn?t improve, and sometimes degrades.  
This isn?t simply because the Jet engine gets involved, and therefore 
everything gets pulled across the wire, and is processed locally. 
That is not always the case. Jet can query SQL Server and have only 
the requested records returned. The problem is that you can design 
your queries in such a way (e.g., by using certain Access-specific 
functions, Access-SQL extensions, certain types of joins, etc.) that
force Jet to retrieve all the records and process them locally. And 
as always, you get your standard set of Jet issues when working with 
many users and very large tables.

ADPs eliminate all these Jet-related considerations by eliminating Jet. 
ADPs are not databases, and do not contain databases (hence no local 
storage); they are simply containers for application objects and 
connection information. All of your data objects are SQL Server objects 
that live on SQL Server and are accessed through the SQL Server OLE DB 
provider and the ADO cursor engine (using client-side recordsets). So 
all of SQL Server's database management and performance features are 
available to you: lots of users, big tables, transaction logging (not 
possible in linked Jet apps because Jet runs on the client), Windows 
2000 security, DTS, OLAP, etc, etc, etc.

That said, there is no DIRECT correlation between performance and
using an MDB or ADP. Database design, application design, number of simul-
taneuos users, etc, are all factors that have to be taken into 
consideration.  
 
Regarding your question about ADO-based parent/child forms: I don?t 
think its possible (probably not even desirable) to link, synch, etc.,
an unbound child form control in Access using only ADO at run-time. 
At least I can?t think of how or why you would do it (You can, however, 
link a listbox in that manner). For example, there doesn?t appear to be 
any way to assign an ADO recordset object to a subform control's recordset 
or record source property. Standard syntax for referring to a subform 
property doesn't work. Besides, the controls on the form itself would 
still need to be bound to the underlying data to populate correctly. Even 
when attempting this kind of thing in VB, you would want to use some type 
of data grid control to display the many-side of the relationship 
properly. I?d unbind the parent form, but keep the child form bound to a 
recordset filtered by criteria set using the parent form. Also, why try 
and rewrite all that navigation functionality, if its even possible? 
Yikes!?!

Hi Michael,

Disregard my comments about the unbound subforms and ADO. I confused 
myself. Of course you can have an unbound Access front-end MDB that uses 
ADO and a parent/child style form. I think you'd need to set the control 
source property of the text boxes on the subform at design-time, but the 
row source property of the form could be set a run-time based on values 
selected in the parent form. Can't imagine you'd want navigation buttons 
on the subform as well though. Anyway, have to play with that one for a 
bit.