DAO, SQL Server 2005, and VB6

[Marcus]

I have some legacy VB6 code that uses DAO (Microsoft DAO 3.6) to access
an access 2000 database. In this app, Access is used soley for data
storage - there is no UI component. I have converted the database to
SQL Server 2005. Can I use DAO to access the new SQL Server database,
or must I change all the DAO calls to ADO? I would like to rewrite as
little code as possible.

Thanks,
Marcus

 

[David Browne]

I have some legacy VB6 code that uses DAO (Microsoft DAO 3.6) to access
an access 2000 database. In this app, Access is used soley for data
storage - there is no UI component. I have converted the database to
SQL Server 2005. Can I use DAO to access the new SQL Server database,
or must I change all the DAO calls to ADO? I would like to rewrite as
little code as possible.

Basically, yes. DAO can access ODBC, through which you can access SQL
Server. Using an Access Database with all the local tables replaced with
ODBC linked tables is an easy way to do this.

David

 

[Theo Verweij]

You can use interop to reference the DAO.dll.

 

[Marcus]

I am writing the code in VB6, not VB.Net. I don't think interop comes
into this, does it?

 

[Scott M.]

It doesn't, but this is a newsgroup for .NET, so we assumed you wanted DAO
in that environment.

 

[Mary Chipman [MSFT]]

If you are using VB6, then you'll want to rewrite using ADO. When you
use DAO, you're loading the Jet engine, which you don't need if
there's no Access UI involved.

--Mary

 

[Scott M.]

I was under the impression though, that for Access DAO could actually
perform better than with ADO.

 

[William \(Bill\) Vaughn]

Yup, but at a cost. Check the archives for details on the frailties and
foibles associated with JET.

--
____________________________________
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.
__________________________________

 

[Mary Chipman [MSFT]]

Yes, you are correct -- when you're using *Jet*, not Access. It's
important to differentiate between the Jet engine and the Access UI
(msaccess.exe). In your case, you're not moving from Access/Jet to SQL
Server with the intent of still using the Access UI, you're moving
from the VB6/Jet to SQL Server. If you were going to use Access as a
front-end, you'd be loading Jet regardless, so using DAO when talking
to Jet engine components on the client (like querydefs) is faster. But
if you're not using the Access UI, then DAO code is just dead weight
-- use ADO or ADO.NET.

--Mary