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

Damn! Can not have it to work!

I am a noob toSQL server. I need it to create a database that should work along with my program. Firts time I have installed Microsoft SQL Server 2005 with all components (because I was not shure which components may appear necessary later). It does not work. I could create a database in SQL Server management Studio, but as soon as I tried to add a new table to the database an error message appeared: "Object reference not set to an instance of an object. (SQLEditors)". All the same happened in Visual Studio (they call it Business development studio or something like that). As I tried to connect a database an error message displayed: "can not make connection.... Object reference not set to an instance of an object". I formatted my hard drive, reinstalled windows (Windows Server 2003 R2), Installed SQL Server (this time, only database services an workstation components), downloaded and installed SP2. and it is all the same, damn thing does not work. Whatever i do - trying to add a new table or new view to existing database - I get the same damn ansver. And there is not an error number, why?
I would appreciate any advise.

pls refer this,

http://www.mcse.ms/message2008943.html they have solved reinstalling the workstation components

http://fabdata.wordpress.com/2007/03/24/object-reference-not-set-to-an-instance-of-an-object-sqleditors/ also this one

|||Deepak Rangarajan,
Unfortunately, that didn't work for me. I uninstalled workstation components, even nativeclient. restarted an installed them again. All the same - can not cerate table, can not create view, can not connection to databese in VS.