A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Opening Access db via DAO in vb.net



 
 
Thread Tools Display Modes
  #21  
Old February 3rd, 2008, 09:42 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Opening Access db via DAO in vb.net

"Norman Yuan" schreef in bericht
...
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]" wrote in message
...
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


  #22  
Old February 3rd, 2008, 09:59 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Opening Access db via DAO in vb.net

"Spam Catcher" wrote in message
. 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


  #23  
Old February 3rd, 2008, 10:27 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Spam Catcher
external usenet poster
 
Posts: 6
Default Opening Access db via DAO in vb.net

"Arvin Meyer [MVP]" wrote in
:

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/edition...omparison.mspx

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


--
(Do not e-mail)
  #24  
Old February 3rd, 2008, 10:30 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Spam Catcher
external usenet poster
 
Posts: 6
Default Opening Access db via DAO in vb.net

"Arvin Meyer [MVP]" wrote in
:

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?

--
(Do not e-mail)
  #25  
Old February 3rd, 2008, 11:02 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Opening Access db via DAO in vb.net


"Spam Catcher" wrote in message
. 1...
"Arvin Meyer [MVP]" wrote in
:

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 situation. Now practically speaking, that
doesn't happen much with decent quality equipment. As a matter of fact, in
my own experience, the last time I had any corruption was in the Spring of
2003 when the client had a faulty Dell WiFi card. Actually, I've only
experience corruption there's a memo field in the table being written to
during the write action. Multi-user has nothing at all to do with it, even a
single user writing to a database located on a server can trigger
corruption.

So when is there a good reason to use JET? Usually when cost is involved
because development is faster with JET and it's already on most user's
machines. There are also a few other reasons, like being able to use VBA
functions and even api's within queries, connecting multiple dissimilar
engines, etc.

When would I choose SQL-Server or another server based engine? A number of
reasons:

1. Large number of users.
2. The data is already in SQL-Server.
3. Security needs to be enforced at a high level.
4. Database is in use 24/7 and must be maintained without interference.
5. Flaky hardware or network that can't be fixed
6. Large project with more than 500 MB of data
7. Most of the users are on a WAN

Most databases do not fall into the above 7 reasons. The overwhelming number
are small business or departmental databases, used by less than a dozen
users and maintained by one of them. JET is an excellent engine for them.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



  #26  
Old February 3rd, 2008, 11:14 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Opening Access db via DAO in vb.net

"Spam Catcher" wrote in message
. 1...

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?


100K rows is a pittance for JET. I've searched through a half million rows
of data in a few seconds with properly designed indexes. SQL-Server Express
is about the same speed. Now if you have millions of rows of data, the full
SQL-Server product is definitely the one to choose.

I'm really sorry that you think that Access/JET is a single-user database.
You're obviously missing giving some of your users a fast, easily
maintainable solution.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


  #27  
Old February 3rd, 2008, 11:36 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Ed Metcalfe
external usenet poster
 
Posts: 82
Default Opening Access db via DAO in vb.net


"Spam Catcher" wrote in message
. 1...
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?


No! I have developed, and continue to support, three systems in which there
is a table of nearly three million records. To date I have had no corruption
issues and no performance issues.

Ed Metcalfe.


  #28  
Old February 3rd, 2008, 11:38 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Ed Metcalfe
external usenet poster
 
Posts: 82
Default Opening Access db via DAO in vb.net


"Michel Posseth [MCP]" wrote in message
...
You talk as a ACCESS developer , a person who writes solutions completely
in ACCESS , throws in some Access forms and or VBA


No, I talk as a developer who uses Access for certain situations. When it is
not appropriate and a better alternative is available then I will pick an
alternative.

Ed Metcalfe.


  #29  
Old February 3rd, 2008, 11:39 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Ed Metcalfe
external usenet poster
 
Posts: 82
Default Opening Access db via DAO in vb.net


"Ed Metcalfe" wrote in message
...

"Spam Catcher" wrote in message
. 1...
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?


No! I have developed, and continue to support, three systems in which
there is a table of nearly three million records. To date I have had no
corruption issues and no performance issues.

Ed Metcalfe.


And just to clarify, these are multi-user systems not single-user systems.

Ed Metcalfe.


  #30  
Old February 3rd, 2008, 11:46 PM posted to microsoft.public.access,microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.languages.vb
Ed Metcalfe
external usenet poster
 
Posts: 82
Default Opening Access db via DAO in vb.net


"Arvin Meyer [MVP]" wrote in message
...
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....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


Thank you. I'll do some further reading tomorrow.

Ed Metcalfe.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.