Opening Access db via DAO in vb.net

Hi

In my vb.net app I am trying to open a db via DAO as below;


Imports dao

Dim dbSynch As dao.Database
dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")


The problem is that on the last line I am getting the error 'DBEngine' is a
type and cannot be used as an expression. What am I doing wrong?

Many Thanks

Regards

Arvin
Sat Feb 02 20:46:47 CST 2008

Try:

Dim dbSync As DAO.Database
Set dbSync = OpenDatabase("C:\MYDB.MDB")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"John" <John@nospam.infovis.co.uk> wrote in message
news:OIrkZafZIHA.3400@TK2MSFTNGP03.phx.gbl...
> Hi
>
> In my vb.net app I am trying to open a db via DAO as below;
>
>
> Imports dao
>
> Dim dbSynch As dao.Database
> dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")
>
>
> The problem is that on the last line I am getting the error 'DBEngine' is
> a type and cannot be used as an expression. What am I doing wrong?
>
> Many Thanks
>
> Regards
>
>

Cor
Sun Feb 03 01:14:02 CST 2008

Arvin,

I have tried this one, however I never have used DAO (I used ADO) and could
not get it to work.

I miss this one,
Dim OpenDataBase As New dao.DBEngine

But did not work either.

But the reason that I address this to you, is because of the fact that the
Set will direct be removed from VB2008 when it is typed.

Cor

"Arvin Meyer [MVP]" <a@m.com> schreef in bericht
news:eDNCI8gZIHA.4684@TK2MSFTNGP06.phx.gbl...
> Try:
>
> Dim dbSync As DAO.Database
> Set dbSync = OpenDatabase("C:\MYDB.MDB")
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
> "John" <John@nospam.infovis.co.uk> wrote in message
> news:OIrkZafZIHA.3400@TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> In my vb.net app I am trying to open a db via DAO as below;
>>
>>
>> Imports dao
>>
>> Dim dbSynch As dao.Database
>> dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")
>>
>>
>> The problem is that on the last line I am getting the error 'DBEngine' is
>> a type and cannot be used as an expression. What am I doing wrong?
>>
>> Many Thanks
>>
>> Regards
>>
>>
>
>

Michel
Sun Feb 03 03:37:02 CST 2008

>> What am I doing wrong?

Well :-)

1 Still using Access ( i hope you have a good reasson )
2 Is using DAO while it is already a long , long time ago declared obsolete
even in the end of VB6 it was already declared as obsolete technollogy and
ADO was prefered

If you nowadays still use ACCESS in my opinion for new projects you should
not ! but use one of the SQL engines closest to Access is the SQL CE engine
, e.g. sql server everywhere then you should connect with ADO.Net.

So having said this it is still possible to connect with DAO , however i
will sure not recomend it to you

set a reference to DAO Project=>Add
Reference=>COM=>Microsoft DAO 3.6 Object Library = dao360.dll or the 350
engine for ACCESS version < 2000

Imports System.Runtime.InteropServices


Dim Dbe As DAO.DBEngine
Dim Db As DAO.Database
Dim Rs As DAO.Recordset

Dbe = New DAO.DBEngine()
Db = DbE.OpenDatabase("full path to access mdb")
Rs = Db.OpenRecordset("sql query or table name ")


Above should work but i strongly recomend you to move to one of the sql
family products as nowadays there is absolutely no valid reasson to stick
with Access
for newly developed products .



hth

Michel








"John" <John@nospam.infovis.co.uk> schreef in bericht
news:OIrkZafZIHA.3400@TK2MSFTNGP03.phx.gbl...
> Hi
>
> In my vb.net app I am trying to open a db via DAO as below;
>
>
> Imports dao
>
> Dim dbSynch As dao.Database
> dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")
>
>
> The problem is that on the last line I am getting the error 'DBEngine' is
> a type and cannot be used as an expression. What am I doing wrong?
>
> Many Thanks
>
> Regards
>
>

Ed
Sun Feb 03 03:48:01 CST 2008


"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
news:elFW4jkZIHA.6140@TK2MSFTNGP02.phx.gbl...
>>> What am I doing wrong?
<snip>
> 2 Is using DAO while it is already a long , long time ago declared
> obsolete even in the end of VB6 it was already declared as obsolete
> technollogy and ADO was prefered
>
> If you nowadays still use ACCESS in my opinion for new projects you
> should not ! but use one of the SQL engines closest to Access is the SQL
> CE engine
> , e.g. sql server everywhere then you should connect with ADO.Net.
>
> So having said this it is still possible to connect with DAO , however i
> will sure not recomend it to you
<snip>

DAO is still the recommended method for accessing data in Microsoft Access
databases if you are using the Jet database engine. Its performance is
significantly better than ADO in this scenario.

Ed Metcalfe.

Michel
Sun Feb 03 06:17:35 CST 2008

<snip>
> DAO is still the recommended method for accessing data in Microsoft Access
> databases if you are using the Jet database engine.
</snip>

Recomended by who ? as MS did not even bother to develop a 64 bit Jet oledb
driver for Access this means that even ADO.Net can`t work with Access
so in my opinion MS doesn`t want you to use Access at all in newly to
develop products . that is probably also the reasson why in all study
materials , examples etc etc only connections to one of the SQL family`s is
shown ( wich by the way do have 64 bit equivalants )


<snip>
Its performance is
> significantly better than ADO in this scenario.
>
</snip>

DAO `s perfomance is superb on ACCESS , as it is a specialized engine
optimized for this database

However my question is should you use a long time ago fased out technology
in a newly developed product ?
as i said when i was programming in VB6 ADO was already declared Obsolete
technology , so using it now in VB.Net is in my opinion foolish

And if we are talking about perfomance lets put the cards right and compares
against SQL server CE wich is the substitute for a Access database in
.Net


Michel



"Ed Metcalfe" <edmetcalfe@hotmail.com> schreef in bericht
news:%23tn0RnkZIHA.208@TK2MSFTNGP02.phx.gbl...
>
> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
> news:elFW4jkZIHA.6140@TK2MSFTNGP02.phx.gbl...
>>>> What am I doing wrong?
> <snip>
>> 2 Is using DAO while it is already a long , long time ago declared
>> obsolete even in the end of VB6 it was already declared as obsolete
>> technollogy and ADO was prefered
>>
>> If you nowadays still use ACCESS in my opinion for new projects you
>> should not ! but use one of the SQL engines closest to Access is the SQL
>> CE engine
>> , e.g. sql server everywhere then you should connect with ADO.Net.
>>
>> So having said this it is still possible to connect with DAO , however i
>> will sure not recomend it to you
> <snip>
>
> DAO is still the recommended method for accessing data in Microsoft Access
> databases if you are using the Jet database engine. Its performance is
> significantly better than ADO in this scenario.
>
> Ed Metcalfe.
>

Michel
Sun Feb 03 07:05:28 CST 2008


http://msdn2.microsoft.com/en-us/library/ms810810.aspx
scroll to the bottom to see that DAO is officially declared Obsolete ( a
long time ago )
note: that this paper was released the first time in January 2002 but it
was known to the programming comunity long time before that

I am a person with a strong readers memory ( everything i once read stays in
my mind ) so i just looked in my library i knew it was somewhere there
Chapter 8 Databases page 393 and page 394 of the official Core reference
guide of VB6 "Programming Microsoft Visual Basic 6.0".

If you read these 2 pages you will see that DAO is already in the
replacement phase in favor of ADO , the core VB6 book tells you between the
lines that DAO is VB 5.0 and in VB6 projects you should favor the new ADO
engine for the simple reasson that DAO is going to be replaced by ADO
remember that this book is written in early 1999 !!!

A funny thing i just encountered is that the writer of the book ( Balena )
tells you if you really need to use DAO or RDO buy the superb book
"Hitchiker`s guide to Visual basic and SQL server by William R. Vaughn "

Well "William R. Vaughn" is also known as "Bill Vaughn" active in these
newsgroups and he is the person who convinced me that there is absolutely no
reasson at all to stick using Access now we have SQL server CE

If a person walked into my office and dare to propose a desktop app written
in VS.Net 2008 with a ACCESS db backend wich uses DAO i would laugh an not
take this person serious annymore , i doubt if this person is qualified to
do his job right



Michel



"Michel Posseth [MCP]" <MSDN@posseth.com> schreef in bericht
news:u3sd55lZIHA.4808@TK2MSFTNGP05.phx.gbl...
> <snip>
>> DAO is still the recommended method for accessing data in Microsoft
>> Access databases if you are using the Jet database engine.
> </snip>
>
> Recomended by who ? as MS did not even bother to develop a 64 bit Jet
> oledb driver for Access this means that even ADO.Net can`t work with
> Access
> so in my opinion MS doesn`t want you to use Access at all in newly to
> develop products . that is probably also the reasson why in all study
> materials , examples etc etc only connections to one of the SQL family`s
> is shown ( wich by the way do have 64 bit equivalants )
>
>
> <snip>
> Its performance is
>> significantly better than ADO in this scenario.
>>
> </snip>
>
> DAO `s perfomance is superb on ACCESS , as it is a specialized engine
> optimized for this database
>
> However my question is should you use a long time ago fased out technology
> in a newly developed product ?
> as i said when i was programming in VB6 ADO was already declared Obsolete
> technology , so using it now in VB.Net is in my opinion foolish
>
> And if we are talking about perfomance lets put the cards right and
> compares against SQL server CE wich is the substitute for a Access
> database in .Net
>
>
> Michel
>
>
>
> "Ed Metcalfe" <edmetcalfe@hotmail.com> schreef in bericht
> news:%23tn0RnkZIHA.208@TK2MSFTNGP02.phx.gbl...
>>
>> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
>> news:elFW4jkZIHA.6140@TK2MSFTNGP02.phx.gbl...
>>>>> What am I doing wrong?
>> <snip>
>>> 2 Is using DAO while it is already a long , long time ago declared
>>> obsolete even in the end of VB6 it was already declared as obsolete
>>> technollogy and ADO was prefered
>>>
>>> If you nowadays still use ACCESS in my opinion for new projects you
>>> should not ! but use one of the SQL engines closest to Access is the SQL
>>> CE engine
>>> , e.g. sql server everywhere then you should connect with ADO.Net.
>>>
>>> So having said this it is still possible to connect with DAO , however
>>> i will sure not recomend it to you
>> <snip>
>>
>> DAO is still the recommended method for accessing data in Microsoft
>> Access databases if you are using the Jet database engine. Its
>> performance is significantly better than ADO in this scenario.
>>
>> Ed Metcalfe.
>>
>
>

Cor
Sun Feb 03 08:33:58 CST 2008

Michael,

In my idea you should not put so much effort to tell that the world is
round.

:-)

Cor


"Michel Posseth [MCP]" <MSDN@posseth.com> schreef in bericht
news:u3sd55lZIHA.4808@TK2MSFTNGP05.phx.gbl...
> <snip>
>> DAO is still the recommended method for accessing data in Microsoft
>> Access databases if you are using the Jet database engine.
> </snip>
>
> Recomended by who ? as MS did not even bother to develop a 64 bit Jet
> oledb driver for Access this means that even ADO.Net can`t work with
> Access
> so in my opinion MS doesn`t want you to use Access at all in newly to
> develop products . that is probably also the reasson why in all study
> materials , examples etc etc only connections to one of the SQL family`s
> is shown ( wich by the way do have 64 bit equivalants )
>
>
> <snip>
> Its performance is
>> significantly better than ADO in this scenario.
>>
> </snip>
>
> DAO `s perfomance is superb on ACCESS , as it is a specialized engine
> optimized for this database
>
> However my question is should you use a long time ago fased out technology
> in a newly developed product ?
> as i said when i was programming in VB6 ADO was already declared Obsolete
> technology , so using it now in VB.Net is in my opinion foolish
>
> And if we are talking about perfomance lets put the cards right and
> compares against SQL server CE wich is the substitute for a Access
> database in .Net
>
>
> Michel
>
>
>
> "Ed Metcalfe" <edmetcalfe@hotmail.com> schreef in bericht
> news:%23tn0RnkZIHA.208@TK2MSFTNGP02.phx.gbl...
>>
>> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
>> news:elFW4jkZIHA.6140@TK2MSFTNGP02.phx.gbl...
>>>>> What am I doing wrong?
>> <snip>
>>> 2 Is using DAO while it is already a long , long time ago declared
>>> obsolete even in the end of VB6 it was already declared as obsolete
>>> technollogy and ADO was prefered
>>>
>>> If you nowadays still use ACCESS in my opinion for new projects you
>>> should not ! but use one of the SQL engines closest to Access is the SQL
>>> CE engine
>>> , e.g. sql server everywhere then you should connect with ADO.Net.
>>>
>>> So having said this it is still possible to connect with DAO , however
>>> i will sure not recomend it to you
>> <snip>
>>
>> DAO is still the recommended method for accessing data in Microsoft
>> Access databases if you are using the Jet database engine. Its
>> performance is significantly better than ADO in this scenario.
>>
>> Ed Metcalfe.
>>
>
>

Michel
Sun Feb 03 09:07:36 CST 2008


Well Cor ,

I guess you are right , but on the othe hand if nobady would bother , it
could become the standard that the world is flat :-)

Michel




"Cor Ligthert[MVP]" <notmyfirstname@planet.nl> schreef in bericht
news:81D9185B-6C13-4052-A9A1-2AD72369F263@microsoft.com...
> Michael,
>
> In my idea you should not put so much effort to tell that the world is
> round.
>
> :-)
>
> Cor
>
>
> "Michel Posseth [MCP]" <MSDN@posseth.com> schreef in bericht
> news:u3sd55lZIHA.4808@TK2MSFTNGP05.phx.gbl...
>> <snip>
>>> DAO is still the recommended method for accessing data in Microsoft
>>> Access databases if you are using the Jet database engine.
>> </snip>
>>
>> Recomended by who ? as MS did not even bother to develop a 64 bit Jet
>> oledb driver for Access this means that even ADO.Net can`t work with
>> Access
>> so in my opinion MS doesn`t want you to use Access at all in newly to
>> develop products . that is probably also the reasson why in all study
>> materials , examples etc etc only connections to one of the SQL family`s
>> is shown ( wich by the way do have 64 bit equivalants )
>>
>>
>> <snip>
>> Its performance is
>>> significantly better than ADO in this scenario.
>>>
>> </snip>
>>
>> DAO `s perfomance is superb on ACCESS , as it is a specialized engine
>> optimized for this database
>>
>> However my question is should you use a long time ago fased out
>> technology in a newly developed product ?
>> as i said when i was programming in VB6 ADO was already declared Obsolete
>> technology , so using it now in VB.Net is in my opinion foolish
>>
>> And if we are talking about perfomance lets put the cards right and
>> compares against SQL server CE wich is the substitute for a Access
>> database in .Net
>>
>>
>> Michel
>>
>>
>>
>> "Ed Metcalfe" <edmetcalfe@hotmail.com> schreef in bericht
>> news:%23tn0RnkZIHA.208@TK2MSFTNGP02.phx.gbl...
>>>
>>> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
>>> news:elFW4jkZIHA.6140@TK2MSFTNGP02.phx.gbl...
>>>>>> What am I doing wrong?
>>> <snip>
>>>> 2 Is using DAO while it is already a long , long time ago declared
>>>> obsolete even in the end of VB6 it was already declared as obsolete
>>>> technollogy and ADO was prefered
>>>>
>>>> If you nowadays still use ACCESS in my opinion for new projects you
>>>> should not ! but use one of the SQL engines closest to Access is the
>>>> SQL CE engine
>>>> , e.g. sql server everywhere then you should connect with ADO.Net.
>>>>
>>>> So having said this it is still possible to connect with DAO , however
>>>> i will sure not recomend it to you
>>> <snip>
>>>
>>> DAO is still the recommended method for accessing data in Microsoft
>>> Access databases if you are using the Jet database engine. Its
>>> performance is significantly better than ADO in this scenario.
>>>
>>> Ed Metcalfe.
>>>
>>
>>
>

Norman
Sun Feb 03 09:13:44 CST 2008

With all other replies being post, I am just wondering: why on the earth do
you need to use DAO in .NET?


"John" <John@nospam.infovis.co.uk> wrote in message
news:OIrkZafZIHA.3400@TK2MSFTNGP03.phx.gbl...
> Hi
>
> In my vb.net app I am trying to open a db via DAO as below;
>
>
> Imports dao
>
> Dim dbSynch As dao.Database
> dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")
>
>
> The problem is that on the last line I am getting the error 'DBEngine' is
> a type and cannot be used as an expression. What am I doing wrong?
>
> Many Thanks
>
> Regards
>
>

Ed
Sun Feb 03 11:29:44 CST 2008


"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
news:u3sd55lZIHA.4808@TK2MSFTNGP05.phx.gbl...
> Recomended by who ? as MS did not even bother to develop a 64 bit Jet
> oledb driver for Access this means that even ADO.Net can`t work with
> Access
> so in my opinion MS doesn`t want you to use Access at all in newly to
> develop products . that is probably also the reasson why in all study
> materials , examples etc etc only connections to one of the SQL family`s
> is shown ( wich by the way do have 64 bit equivalants )

By the vast majority of people I speak to who are still developing solutions
in Microsoft Access using the Jet database engine. Regardless of whether
they should be there are still a lot of them and the overall performance of
ADO when working with Jet is, frankly, abysmal.

> DAO `s perfomance is superb on ACCESS , as it is a specialized engine
> optimized for this database
>
> However my question is should you use a long time ago fased out technology
> in a newly developed product ?

Probably not, however I never stated an opinion for or against Jet as a
suitable solution for John's application. I presume he has his reasons for
choosing it over more current technologies.

> And if we are talking about perfomance lets put the cards right and
> compares against SQL server CE wich is the substitute for a Access
> database in .Net

I'm sure you're right. Nevertheless DAO is, in most cases, *much* faster
when working with Jet than ADO. As John is using an MDB file I would
strongly recommend he tries DAO as well as ADO as, in my experience, it may
make the difference between a usable and unusable system.

Ed Metcalfe.

Arvin
Sun Feb 03 11:30:40 CST 2008

"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
news:%23dhY5TmZIHA.4180@TK2MSFTNGP06.phx.gbl...
>
> http://msdn2.microsoft.com/en-us/library/ms810810.aspx
> scroll to the bottom to see that DAO is officially declared Obsolete
> ( a long time ago )
> note: that this paper was released the first time in January 2002 but
> it was known to the programming comunity long time before that

At our last MVP Summit in Seattle, we were specifically told by Microsoft
that DAO is the preferred method of dealing with JET and SQL-Server
databases from Access front-ends, reversing decisions made ssome time ago.

ADO is obsolete, having been replaced by ADO.NET.

The design structure of Access for dealing with databases is so complete and
therefore complex, that I believe that there haven't been any .NET efforts
made to work with it yet. It may be several years before Microsoft builds
any .NET capability into Access. Until then, DAO is the faster and more
complete method of dealing with JET data.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ed
Sun Feb 03 11:49:18 CST 2008


"Arvin Meyer [MVP]" <a@m.com> wrote in message
news:O7FMCqoZIHA.4196@TK2MSFTNGP04.phx.gbl...
> At our last MVP Summit in Seattle, we were specifically told by Microsoft
> that DAO is the preferred method of dealing with JET and SQL-Server
> databases from Access front-ends, reversing decisions made ssome time ago.
>
> ADO is obsolete, having been replaced by ADO.NET.
>
> The design structure of Access for dealing with databases is so complete
> and therefore complex, that I believe that there haven't been any .NET
> efforts made to work with it yet. It may be several years before Microsoft
> builds any .NET capability into Access. Until then, DAO is the faster and
> more complete method of dealing with JET data.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>

Arvin,

Is this information published on the Microsoft website anywhere? The most
recent article I could find was this:

http://support.microsoft.com/kb/225048

Ed Metcalfe.

Michel
Sun Feb 03 12:14:37 CST 2008


> At our last MVP Summit in Seattle, we were specifically told by Microsoft
> that DAO is the preferred method of dealing with JET and SQL-Server
> databases from Access front-ends, reversing decisions made ssome time ago.
>
> ADO is obsolete, having been replaced by ADO.NET.

Seen from the perspective of an ACCESS developer i can inmagine this
decission as DAO is superior in perfomance

However this topic is about a VB.Net developer interacting with a ACCESS
database , and from that point seen i can not find a valid reasson to use
Access at all as a database with anny engine wether it is DAO or ADO.Net


This is confusing me :

> The design structure of Access for dealing with databases is so complete
> and therefore complex, that I believe that there haven't been any .NET
> efforts made to work with it yet. It may be several years before Microsoft
> builds any .NET capability into Access. Until then, DAO is the faster and
> more complete method of dealing with JET data.


As far as i know the versions of Access starting from 2003 use the MSDE
engine or SQL express
so i see Microsoft never upgrade these engines to .Net as there are already
sql 2005 engines out there wich already support the .Net framework and
languages
I guess time will fase out the Jet engine .........


Or am i missing something here ?


Michel



"Arvin Meyer [MVP]" <a@m.com> schreef in bericht
news:O7FMCqoZIHA.4196@TK2MSFTNGP04.phx.gbl...
> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
> news:%23dhY5TmZIHA.4180@TK2MSFTNGP06.phx.gbl...
>>
>> http://msdn2.microsoft.com/en-us/library/ms810810.aspx
>> scroll to the bottom to see that DAO is officially declared Obsolete
>> ( a long time ago )
>> note: that this paper was released the first time in January 2002 but
>> it was known to the programming comunity long time before that
>
> At our last MVP Summit in Seattle, we were specifically told by Microsoft
> that DAO is the preferred method of dealing with JET and SQL-Server
> databases from Access front-ends, reversing decisions made ssome time ago.
>
> ADO is obsolete, having been replaced by ADO.NET.
>
> The design structure of Access for dealing with databases is so complete
> and therefore complex, that I believe that there haven't been any .NET
> efforts made to work with it yet. It may be several years before Microsoft
> builds any .NET capability into Access. Until then, DAO is the faster and
> more complete method of dealing with JET data.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>

Michel
Sun Feb 03 12:58:41 CST 2008


"Ed Metcalfe" <edmetcalfe@hotmail.com> schreef in bericht
news:%230miUpoZIHA.5612@TK2MSFTNGP06.phx.gbl...
>
> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
> news:u3sd55lZIHA.4808@TK2MSFTNGP05.phx.gbl...
>> Recomended by who ? as MS did not even bother to develop a 64 bit Jet
>> oledb driver for Access this means that even ADO.Net can`t work with
>> Access
>> so in my opinion MS doesn`t want you to use Access at all in newly to
>> develop products . that is probably also the reasson why in all study
>> materials , examples etc etc only connections to one of the SQL family`s
>> is shown ( wich by the way do have 64 bit equivalants )
>
> By the vast majority of people I speak to who are still developing
> solutions in Microsoft Access using the Jet database engine. Regardless of
> whether they should be there are still a lot of them and the overall
> performance of ADO when working with Jet is, frankly, abysmal.
>

Here comes our difference in perspective ,

You talk as a ACCESS developer , a person who writes solutions completely
in ACCESS , throws in some Access forms and or VBA

I see it from the perspective of a software developer , i just use a DB as a
storage


>> DAO `s perfomance is superb on ACCESS , as it is a specialized engine
>> optimized for this database
>>
>> However my question is should you use a long time ago fased out
>> technology in a newly developed product ?
>
> Probably not, however I never stated an opinion for or against Jet as a
> suitable solution for John's application. I presume he has his reasons for
> choosing it over more current technologies.
>
>> And if we are talking about perfomance lets put the cards right and
>> compares against SQL server CE wich is the substitute for a Access
>> database in .Net
>
> I'm sure you're right. Nevertheless DAO is, in most cases, *much* faster
> when working with Jet than ADO. As John is using an MDB file I would
> strongly recommend he tries DAO as well as ADO as, in my experience, it
> may make the difference between a usable and unusable system.
>
> Ed Metcalfe.

I have done some comparisations in the past and posted the results in these
newsgroups , ( i was once a sceptic to ) between DAO interop , legacy
ADO interop and ADO.Net and indeed DAO is much faster untill you do
subsequent requests on the same result sets, the caching mechanism of ADO
then kicks in however the overall winner is DAO .

However as a VS developer you have the option to ditch ACCESS completely as
a storage engine , and use native .Net technology and if you take the
lowest version of the native SQL engine ( the everywhere edition a.k. SQL
CE ) the cards are shufled in a different way . And you get a lot of extra
advantages
to call a few deployability ( xcopy deployment ) , Scalability ( to express
or higher depending the needs )

There was only one thing that holded me with ACCESS in the past as a storge
db in my VS projects , and this was the security of our data , in the
company i worked for at that moment we owned the data that was distributed
to the end users , so we had to lock the database file this was only
possible with ACCESS at that moment with a custom workgroup file . However
with SQL server everywhere edition you can protect your data in a simular
way so i cannot inmagine why a developer would want to use a ACCESS
database for storage of his data in a VS project .

But i am always openminded and eager to learn so maybe he can surprise me
with a good reasson ,,, and did i not provide him with the code he needed to
open the db with DAO in VB.Net ? . Although i would not recomend it to
use ACCESS this way ( or even at all as a storage db in a VS.Net project )
that wasn`t his question :-) .

I just wanted to point out the more elegant solutions for data storage that
are availlable right now for VS.Net developers

I enjoyed this discussion , nice thingy from these multi group postings is
that you encounter people with a totally different view of things, and you
might learn something new .

regards

Michel Posseth

Michel
Sun Feb 03 13:17:24 CST 2008

Well after thoroughly thinking about this question , the answer seems

You don`t

:-)

Or maybe we are both missing something here

Regards

Michel



"Norman Yuan" <FakeName@FakeEmail.Not> schreef in bericht
news:O7dgfdnZIHA.5348@TK2MSFTNGP03.phx.gbl...
> With all other replies being post, I am just wondering: why on the earth
> do you need to use DAO in .NET?
>
>
> "John" <John@nospam.infovis.co.uk> wrote in message
> news:OIrkZafZIHA.3400@TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> In my vb.net app I am trying to open a db via DAO as below;
>>
>>
>> Imports dao
>>
>> Dim dbSynch As dao.Database
>> dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")
>>
>>
>> The problem is that on the last line I am getting the error 'DBEngine' is
>> a type and cannot be used as an expression. What am I doing wrong?
>>
>> Many Thanks
>>
>> Regards
>>
>>
>

Spam
Sun Feb 03 13:40:21 CST 2008

"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in
news:u3sd55lZIHA.4808@TK2MSFTNGP05.phx.gbl:

> <snip>
>> DAO is still the recommended method for accessing data in Microsoft
>> Access databases if you are using the Jet database engine.
> </snip>
>
> Recomended by who ? as MS did not even bother to develop a 64 bit Jet
> oledb driver for Access this means that even ADO.Net can`t work with
> Access so in my opinion MS doesn`t want you to use Access at all in
> newly to develop products . that is probably also the reasson why in
> all study materials , examples etc etc only connections to one of the
> SQL family`s is shown ( wich by the way do have 64 bit equivalants )

Agreed - Acecss is the way of the Dodo in terms of application development.
Microsoft has pretty much pigeon holed it into a pure personal desktop
database now.

--
spamhoneypot@rogers.com (Do not e-mail)

Arvin
Sun Feb 03 13:47:34 CST 2008


"Ed Metcalfe" <edmetcalfe@hotmail.com> wrote in message
news:e%23GPQ0oZIHA.220@TK2MSFTNGP04.phx.gbl...
>
> "Arvin Meyer [MVP]" <a@m.com> wrote in message
> news:O7FMCqoZIHA.4196@TK2MSFTNGP04.phx.gbl...
>> At our last MVP Summit in Seattle, we were specifically told by Microsoft
>> that DAO is the preferred method of dealing with JET and SQL-Server
>> databases from Access front-ends, reversing decisions made ssome time
>> ago.
>>
>> ADO is obsolete, having been replaced by ADO.NET.

>
> Is this information published on the Microsoft website anywhere? The most
> recent article I could find was this:
>
> http://support.microsoft.com/kb/225048

As you can see, that article hasn't been updated in over 4 years. There may
be some info on the Access Team blog:

http://blogs.msdn.com/access/

I did a cursory search using DAO vs ADO and found:

http://blogs.msdn.com/access/search.aspx?q=DAO+vs+ADO&p=1

which seems to mention several postings by the MS-Access PMs on using DAO in
Access 2007. Sorry I did not do further research, but you're welcome to.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin
Sun Feb 03 14:02:53 CST 2008

Actually, you are missing quite a lot. Yes this is definitely from the
perspective of an Access developer. You must realize that the question
concerned an Access, or more accurately a JET database. JET, has always been
the default engine for Access since version 2.0. Access can however use any
other DBMS that has connectivity, including all Microsoft engines, and many,
if not most, non-Microsoft engines. .NET is not an engine, rather it is a
code base which is not as yet be fully integrated with other, especially
older, technologies, but usually is can use those technologies. COM is one
example, DAO, another.

Contrary to your supposition, Microsoft has upgraded the JET engine with the
release of Access 2007, and is currently upgrading it for the next release.
More, I cannot say because of NDA restrictions.

I will not discuss whether or not to use Access as a front-end, or JET as a
back-end because we both have certain prejudices. I will say, that more
Access databases are in existence than all other databases combined, and it
has been that way for 15 years. I don't argue with that kind of success, I
benefit from it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
news:uA06XGpZIHA.5980@TK2MSFTNGP04.phx.gbl...
>
>> At our last MVP Summit in Seattle, we were specifically told by Microsoft
>> that DAO is the preferred method of dealing with JET and SQL-Server
>> databases from Access front-ends, reversing decisions made ssome time
>> ago.
>>
>> ADO is obsolete, having been replaced by ADO.NET.
>
> Seen from the perspective of an ACCESS developer i can inmagine this
> decission as DAO is superior in perfomance
>
> However this topic is about a VB.Net developer interacting with a ACCESS
> database , and from that point seen i can not find a valid reasson to use
> Access at all as a database with anny engine wether it is DAO or ADO.Net
>
>
> This is confusing me :
>
>> The design structure of Access for dealing with databases is so complete
>> and therefore complex, that I believe that there haven't been any .NET
>> efforts made to work with it yet. It may be several years before
>> Microsoft builds any .NET capability into Access. Until then, DAO is the
>> faster and more complete method of dealing with JET data.
>
>
> As far as i know the versions of Access starting from 2003 use the MSDE
> engine or SQL express
> so i see Microsoft never upgrade these engines to .Net as there are
> already sql 2005 engines out there wich already support the .Net framework
> and languages
> I guess time will fase out the Jet engine .........
>
>
> Or am i missing something here ?
>
>
> Michel
>
>
>
> "Arvin Meyer [MVP]" <a@m.com> schreef in bericht
> news:O7FMCqoZIHA.4196@TK2MSFTNGP04.phx.gbl...
>> "Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
>> news:%23dhY5TmZIHA.4180@TK2MSFTNGP06.phx.gbl...
>>>
>>> http://msdn2.microsoft.com/en-us/library/ms810810.aspx
>>> scroll to the bottom to see that DAO is officially declared Obsolete
>>> ( a long time ago )
>>> note: that this paper was released the first time in January 2002 but
>>> it was known to the programming comunity long time before that
>>
>> At our last MVP Summit in Seattle, we were specifically told by Microsoft
>> that DAO is the preferred method of dealing with JET and SQL-Server
>> databases from Access front-ends, reversing decisions made ssome time
>> ago.
>>
>> ADO is obsolete, having been replaced by ADO.NET.
>>
>> The design structure of Access for dealing with databases is so complete
>> and therefore complex, that I believe that there haven't been any .NET
>> efforts made to work with it yet. It may be several years before
>> Microsoft builds any .NET capability into Access. Until then, DAO is the
>> faster and more complete method of dealing with JET data.
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>>
>
>

Arvin
Sun Feb 03 14:35:53 CST 2008

"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
news:uFZzMZpZIHA.1132@TK2MSFTNGP06.phx.gbl...

> Here comes our difference in perspective ,
>
> You talk as a ACCESS developer , a person who writes solutions completely
> in ACCESS , throws in some Access forms and or VBA
>
> I see it from the perspective of a software developer , i just use a DB as
> a storage

That, is a huge difference. I can't speak for Ed, but I can tell you that
all I do is write database solutions. Access is not my only tool, but I must
say, that it is uniquely appropriate for database application construction,
if one is building those applications to run on a workstation.

I've been developing databases since 1981, and Access databases since 1992.
I've made living developing databases since 1994. I've been an MVP in Access
since 2000, and also some security related technologies, holding dual MVP
status for 1 year (that's no longer possible).

JET, is definitely not appropriate for all uses. Neither is any database
engine. I do prefer Microsoft engines though, because of the really great
support that Microsoft gives its developer community. That does not mean
that I don't use other technologies, but it does mean that if I'm busy. I'll
sooner turn down jobs that don't use technologies that I can't give my
clients what I feel is the best value for their money. For instance, I refer
all jobs where the Internet may offer a better alternative than the LAN to
other developers. Most of my work is not enterprise wide, although I do lots
of work for government and Fortune 500 clients. For enterprise wide
solutions, Access in not suited for any but the most lightly used
applications. Most of the enterprise apps I see use VB, Java, or .NET
solutions which are not particularly interesting to me because they require
larger teams to build successfully.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin
Sun Feb 03 14:42:07 CST 2008

> "Norman Yuan" <FakeName@FakeEmail.Not> schreef in bericht
> news:O7dgfdnZIHA.5348@TK2MSFTNGP03.phx.gbl...
>> With all other replies being post, I am just wondering: why on the earth
>> do you need to use DAO in .NET?

"Michel Posseth [MCP]" <MSDN@posseth.com> wrote in message
news:uKcRojpZIHA.5784@TK2MSFTNGP03.phx.gbl...
> Well after thoroughly thinking about this question , the answer seems
>
> You don`t
>
> :-)
>
> Or maybe we are both missing something here

What you're missing is that it's not .NET that is using DAO as much as the
JET engine. And you can use ADO very successfully with the JET engine, but
is will be slower. Typically, that speed in a well designed database with a
well designed query is of little concern, becuase it is not likely that you
will be searching through more than 100K rows in JET, so a 1/2 second
doesn't mean much. But if you are looping through a recordset with 100K of
rows, it can mean a significant difference.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin
Sun Feb 03 14:59:13 CST 2008

"Spam Catcher" <spamhoneypot@rogers.com> wrote in message
news:Xns9A39954872AC8usenethoneypotrogers@127.0.0.1...

> Agreed - Acecss is the way of the Dodo in terms of application
> development.
> Microsoft has pretty much pigeon holed it into a pure personal desktop
> database now.

My clients which include Fortune 500 companies, Federal and local
government, and numerous others will be quite surprised. Departmental and
low user count applications are Access's forte. I do suspect that there are
far more single user Access applications, but the number multi-user apps
runs in the millions.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Spam
Sun Feb 03 15:27:15 CST 2008

"Arvin Meyer [MVP]" <a@m.com> wrote in
news:O##3leqZIHA.4696@TK2MSFTNGP05.phx.gbl:

> My clients which include Fortune 500 companies, Federal and local
> government, and numerous others will be quite surprised. Departmental
> and low user count applications are Access's forte. I do suspect that
> there are far more single user Access applications, but the number
> multi-user apps runs in the millions.

Yes, it "works", butmost developers are only using it because it is what
they're comfortable with. But with all the limitations with Access such as
the threat of DB corruption in a multi-user environment, why not use a real
embedded DB like SQL Compact Edition?

Even Microsoft acknowledges that Access isn't intended to be a general
purposes database engine - rather a personal DB:

http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx

I just don't see the point of using Access anymore when there are better
alternatives out there.


--
spamhoneypot@rogers.com (Do not e-mail)

Spam
Sun Feb 03 15:30:06 CST 2008

"Arvin Meyer [MVP]" <a@m.com> wrote in
news:OJ8wAVqZIHA.4332@TK2MSFTNGP04.phx.gbl:

> What you're missing is that it's not .NET that is using DAO as much as
> the JET engine. And you can use ADO very successfully with the JET
> engine, but is will be slower. Typically, that speed in a well
> designed database with a well designed query is of little concern,
> becuase it is not likely that you will be searching through more than
> 100K rows in JET, so a 1/2 second doesn't mean much. But if you are
> looping through a recordset with 100K of rows, it can mean a
> significant difference.

If you're processing 100K rows, shouldn't that be a red flag that you're
making access do something it's not designed to do? Access is a personal
database - for that much data I'm sure you'll get better performance with
SQL Server Express. Yes, it's more software to install, but it can always
be installed on a central server and shared amongst multiple clients?

--
spamhoneypot@rogers.com (Do not e-mail)

Arvin
Sun Feb 03 16:02:20 CST 2008


"Spam Catcher" <spamhoneypot@rogers.com> wrote in message
news:Xns9A39A7682739Ausenethoneypotrogers@127.0.0.1...
> "Arvin Meyer [MVP]" <a@m.com> wrote in
> news:O##3leqZIHA.4696@TK2MSFTNGP05.phx.gbl:
>
> Yes, it "works", butmost developers are only using it because it is what
> they're comfortable with. But with all the limitations with Access such as
> the threat of DB corruption in a multi-user environment, why not use a
> real
> embedded DB like SQL Compact Edition?

Since I'm a database developer, and I do use several different database
engines, including SQL-Server, I might be able to shed some light on the
reasons.

First of all, we must separate Access from JET, which is one of the engines
which is usable with Access. I am assuming that you mean the JETengine. JET,
being a file-server based engine can corrupt if the data is used in bound
forms, if there is a disconnect during a write. Server based engines are
less likely to corrupt in that si