Access / DAO - What's the Future

[TrevorS]

We are using Access as the "repository" for a C++ MFC application. It's
single user.
The schema is complex (100 tables) and can be a few hundred MBytes in
size.

We are starting to reach the limits: occassionl "Query too Complex" and
"Max Lock Files Exceeded". Although some of these we have tackled and
got around through better design.

We recently moved to Visual Studio 2005 and of course no longer have
the benifit of DAO wizards for creating RecordSets and refreshing them
when the schema changes. This too we have got around for now - just
using old classes as a model and creating them by hand.

We are about to start the next phase of our product and it will stress
the database even more.

The questions:

o Would you recommend that we change to SQL Server Express?
o What are our migration options? Do we have to convert everything to
ADO right away? I assume we can't use DAO with SQL Server. If we could
we migrate slowly - using ADO (or ODBC) for new classes.
o Or should we use ODBC?

Thanks
TrevorS.

 

[Miha Markic [MVP C#]]

Hi Trevor,

We are using Access as the "repository" for a C++ MFC application. It's
single user.
The schema is complex (100 tables) and can be a few hundred MBytes in
size.

We are starting to reach the limits: occassionl "Query too Complex" and
"Max Lock Files Exceeded". Although some of these we have tackled and
got around through better design.

We recently moved to Visual Studio 2005 and of course no longer have
the benifit of DAO wizards for creating RecordSets and refreshing them
when the schema changes. This too we have got around for now - just
using old classes as a model and creating them by hand.

We are about to start the next phase of our product and it will stress
the database even more.

The questions:

o Would you recommend that we change to SQL Server Express?

Most definitely. Perhaps you might consider Sql Server Everywhere, too
(single user, no install required - just copy&paste assemblies).

o What are our migration options? Do we have to convert everything to
ADO right away? I assume we can't use DAO with SQL Server. If we could
we migrate slowly - using ADO (or ODBC) for new classes.

Not sure about migration. However if you want to take advantage of .net then
you should go with ado.net and its native providers or even better, use an
ORM (i.e. LLBLGenPro - does many things for you, like wizards, but better)
if it fits your needs.

o Or should we use ODBC?

ODBC via ado.net is still an option but .. .see above.

 

[TrevorS]

Hi Trevor,



Most definitely. Perhaps you might consider Sql Server Everywhere, too
(single user, no install required - just copy&paste assemblies).


Not sure about migration. However if you want to take advantage of .net then
you should go with ado.net and its native providers or even better, use an
ORM (i.e. LLBLGenPro - does many things for you, like wizards, but better)
if it fits your needs.


ODBC via ado.net is still an option but .. .see above.

Thanks Miha,

It's the migration of the code that I am most concerned about.
Can I swap the database and keep DAO and then, as I add or change
business logic, begin to use ADO?

Trevor

 

[Miha Markic [MVP C#]]

Hi Trevor,

It's the migration of the code that I am most concerned about.
Can I swap the database and keep DAO and then, as I add or change
business logic, begin to use ADO?

I am not sure. There is limited support for ADO and I don't think that DAO
is supported in any way. That said nothing prevents you to use DAO with COM
interop thing (creating a managed wrapper around non-managed COM object - VS
does this automatically, too, when you reference the COM object. However, it
is not guaranteed that it will work just like that).

 

[William \(Bill\) Vaughn]

I posted a response on my blog.
http://betav.com/blog/billva/2006/09/question_of_the_day_dao_to_ado.html
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________