DAO and SQL server

We are considering migrating a large piece of VB code from Access to
SQL server. All the code is in DAO, are there any known
incompatibilities WRT SQL server and DAO?

Many thanks
SiSd (sd_bradford@.hotmail.com) writes:
> We are considering migrating a large piece of VB code from Access to
> SQL server. All the code is in DAO, are there any known
> incompatibilities WRT SQL server and DAO?

I have never used DAO, but a colleague of mine told me the other day,
that DAO was designed for Access, and is cumbersome to use with SQL Server.

In any case, DAO is old technology, and I would suspect that you don't get
full support for newer features in SQL Server with DAO, but I could be wrong
on that particular point. Nevertheless, I would consider ripping out DAO
in favour of ADO which is more up to date. Admittedly, I find ADO quite
ugly as well, and ADO .Net is a lot more palatable. That would however call
for a migration to VB .Net, which may be an overkill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Sd" <sd_bradford@.hotmail.com> wrote in message
news:e66eafb3.0412200220.17a18286@.posting.google.c om...
> We are considering migrating a large piece of VB code from Access to
> SQL server. All the code is in DAO, are there any known
> incompatibilities WRT SQL server and DAO?
> Many thanks
> Si

There's stuff you can do with tables and queries which would probably cause
a problem.
You can forget compact and repair operations.
Table search is potentially a problem as you'll minimum have to switch to
(SQL) attached tables.
Hopefully the code and data is already split between two databases?

If you just use a bunch of selects and inserts then they're quite possibly
going to be relatively painless to convert.
If you have to do it anyhow.
I would suggest create corresponding sql tables manually.
Perhaps start off using DTS to load em in and see what you get.
Attach these to a copy of your code database.
Give it a twirl and see what happens.

--
Regards,
Andy O'Neill