Question of the Day: DAO to ADO to ADO.NET
A recent question on the newsgroups asked about the life-expectancy of DAO and if it makes sense to transition to ADO to access their database.
The problem here is not DAO or even ADO classic (COM-based ADO). The problem here is the JET engine.
When you write an application using JET whether you you Access or VB6 or VB.NET or any other language, you are limited by the capacity and architecture of the shared-file architecture of JET. Your security is limited by JET's inability to be encrypted and since by its very nature it's designed to be passed around as a file and accessed by whomever receives it, it should not contain private data. Sure, there are lots of situations where a DBMS as simple as JET is okay--as in the home or for some non-secure office applications. But IMHO, JET is really not suitable for businesses that need to pass manage secure data. We've talked about and debated the merits and foibles of JET for many years here on this newsgroup and in other forums.
The next question that invariably gets asked is what are the migration paths? Should one transition to SQL Server or SQL Server Everywhere or some other DBMS? What about the applications? Will they run faster on SQL Server or other engines? There is no simple answer here. Applications, if they are written well, take advantage of the engine's strengths and work around its limitations. Most folks have considerable time and talent invested in JET and the applications written to access the data. Sadly, much of this logic has to be redesigned to take advantage of the strengths and limitations of the target DBMS. Can SQL Server be faster than JET? Only perhaps. For small databases, SQL Server can be cumbersome. SQL Server Everywhere (SSEv) is better suited for lightweight applications and yes, I expect it is faster than JET for these applications but again, you'll have to reengineer your application and the skills you use to get to it in order to transition to its use. On the other hand, for applications that have to serve more than one user (SSEv is not designed for that) and have to scale to dozens or hundreds of users, SQL Server is ideal. It's well supported by the tools, documentation and third-party books. But again, you'll have to re-write your application to interface with SQL Server. DAO is not ADO is not ADO.NET and that's just the tip of the sword. The features and benefits of SQL Server are very different than those exposed by the now obsolete JET engine.
hth