2
votes

I am making an Access app that will be using Jet exclusively(No SQL Server), and split into front end back end architecture. I have weighed the pros and cons of bound/unbound, and still would like to pursue unbound in this situation.

I have a handful of classes and modules I will be importing into this project which rely on ADO record sets. However, I have read several indications which suggest using DAO to populate Access forms http://support.microsoft.com/kb/281998 {Requirements for Microsoft Jet} . I know that these are totally different libraries, and cannot share info from one another. However, I was thinking that my classes and other ADO dependent objects could use a mix of local tables/queries and form control values which would avoid a potential collision.

So my question: If I only us DAO to fill forms in this project, am I still asking for trouble? If so, what kind of problems should I be aware of? Or is it reasonable to expect that this separation could co-exist if I am careful, and the distinction is explained in the apps documentation?

1
ADO and DAO can both be used successfully in the same Access db application. I do it often without trouble, so I don't really understand your concern. Also I don't understand how bound vs. unbound fits into this question. If you encounter a problem which seems to be caused by a conflict between them, ask a question specifically about that problem.HansUp

1 Answers

9
votes

Mixing DAO and ADO in the same application doesn't really present any problems or complications. I think your question really has a lot more to do with when and why would you use one or the other.

My own standard policy in Access development has been to use ADO only when DAO won't work for something I want to do. For example, with ADO you can access data stores other than Access such as Visual Foxpro, Oracle, MySQL, etc. But ADO isn't always required to achieve this either since you can often use ODBC instead, which means you would then be using DAO together with ODBC linked tables.

Recently I have somewhat switched gears to where I prefer to use ADO exclusively, but I recognize that this is not the common practice among many experienced Access developers. I'm using SQL Server 2008/2012 Express and forms bound to ADO recordsets, and I'm avoiding using any ODBC linked tables at all. My basic reason is that ADO gives me a few more options and more control, although it does come with it's costs. I use a lot of disconnected recordsets and then I "manually" (VBA) write the changes back to the database only if the user clicks the save button. This gives the user the option to make a bunch of changes to a form and it's subforms, but still cancel out if he chooses. With disconnected ADO recordsets it's up to you to determine how to get data changes to the server, although non-disconnected recordsets automatically submit their changes. As near as I can tell, the only ADO recordset type that automatically receives all additions, changes, and deletions from the server (adOpenDynamic) cannot be bound to a form, but that's really not a huge concern if you just want to be able to use ADO bound forms for adding/editing/deleting records.

I've read numerous places that ADO doesn't have any performance advantages over DAO, and in some cases may actually be slower. I cannot say one way or the other, but I don't think this is a big concern. ADO has the advantage that you can actually make your application work across slow and/or unstable network connections (such as WAN/Internet), which is really not feasible with DAO/ODBC. With a pure ADO solution, you are in charge of handling the connection object and all fetches of data. You can set the connection and command timeouts and if the timeouts occur, the connection fails, etc., it's up to you to decide how to handle it. You could, for example, make X number of reconnection attempts. None of this is really possible in DAO/ODBC. As far as I know, the connection object isn't even exposed with ODBC, other than the fact that you can setup the ODBC connection string.

It does take a lot more code to do everything in ADO, particularly if you want to use disconnected recordsets. Recordsets have to be fetched (or fabricated) using code. If you use disconnected recordsets, data has to be written back to the server using code. Whether you use disconnected or connected recordsets, Master/Child relationships on forms have to be manually managed using code (you can't use the Master/Child Link properties).

One potential downside with ADO is that it isn't possible to bind a report to an ADO recordset unless you are using an Access Data Project, which isn't really recommended at this point, seeing that MS is dropping support for ADP's. If you want to use something other than DAO for data on a report, you would have to use Pass Through Queries, and if your data store is MS Access it would make no sense to do that.

I think any discussion about bound and unbound forms is completely unrelated to any discussion about DAO and ADO. Forms can be bound to ADO Recordsets with very few trade-offs. An unbound form could get it's data from a DAO Recordset or ADO Recordset and there would be no difference so unbound forms and ADO are no more related or unrelated to each other than DAO and unbound forms. I really only use unbound forms for creating my own Message Boxes and certain kinds of input boxes or record selection boxes. Usually it's a case where I want data displayed on buttons for a touch screen application and then I go unbound. If I could get similar behavior from Textboxes (and I probably could if I tried hard enough), there would be few cases where an unbound form would be necessary.

It seems to me there has been an idea propagated that unbound forms are the way real professionals develop Access applications. Or that unbound forms are the only way that you get performance. Or that unbound forms should be used if you're not using MS Access as your data store. But none of these ideas really hold up to any scrutiny. Binding forms to ADO recordsets is much easier than going completely unbound. And it's not even possible to use Datasheet Views or Continuous Forms in an unbound manner. If you really want to go unbound in a grid-style view you'd have to use an ActiveX grid control such as iGrid from 10tec, or the MS List View control which usually have more overhead since there is the time needed to fetch the Records and the additional time needed to fill out the grid controls with the data. An unbound form has no performance gains that I can think of over binding the form to an ADO recordset. And there's really no kind of data store that can't use an ADO Recordset, even if you have to use a fabricated ADO recordset.

This is a tremendous oversimplification but your primary performance gains in MS Access come from maximizing the performance of your data store (which usually means moving to SQL Server) and carefully managing how much data you load and present to the user. The easiest way to do the latter is with ADO, but you can also do it with DAO/ODBC as well. ODBC actually has one advantage over ADO, called lazy loading. You can bind a datasheet form or continuous subform to a very large table/DAO recordset and the loading of each record will occur as you scroll. Its a feature I'm not very fond of and I've had users complain about it since you don't get to see the records until you release the scroll bar, but I'd have to argue that it is one of the most efficient ways of handling large amounts of data (> 50,000 records).

There's a fairly extensive article on the UtterAccess Wiki that details the pros and cons of DAO versus ADO (Note that the article got deleted and the only way to view it is the look at the history of what was at one time. Just make sure you scroll down below the diff/comparison). And there's another great article on unbound forms at AccessExperts.com written by Juan Soto.