Which one can be the better one data access component for desktop application and why?
1.) For MS Access 2007: ADO, OLEDB, DAO
2.) For MSSQL 2005: ADO, OLEDB, DAO
A:
Ask yourself: What data access features do I need, and choose your layer based on that. MS Access is file-based data storage, so if you have more than one user using your app then Access is a bad choice. I would steer clear of MSA and focus on newer technologies.
MSSQL is much better, as it is scalable, supports multiple concurrent users, and has additional management tools like backup and reporting services. SQL 2005 Compact Edition allows you to attach the database at run time, great for single instance desktop apps.
A third DAC you might want to look at is SQLite also for file-based solutions or desktop applications.
Wez
2010-06-01 08:46:37
Jet/ACE (which is the database engine you are actually meaning to refer to when you say "Access") is perfectly able to handle multiple users, though without special efforts, the user populations are going to be limited to less than 20-25 simultaneous users (depending on the type of interaction the users have with the data). Please don't spread misinformation about Access/Jet/ACE.
David-W-Fenton
2010-06-03 01:42:43
David I did not say it cannot handle multiple users, but to avoid that can of worms. Running a Jet-based 40 user system for 4+ years gives insight into the internals more than any specs on paper can. Don't be biased, open up to new technologies :)
Wez
2010-06-03 07:09:22
You said it's a bad choice for more than one user, with no further qualification. That is simply not true. It *CAN* be a bad choice, but it quite often is a very *good* choice.
David-W-Fenton
2010-06-03 17:40:10
The fact that I call you out on an erroneous statement does not make me biased. As to new technologies, I use them all the time, when they are appropriate. If you want new technology, have a look at Access 2010 with Sharepoint 2010 -- there's a place where really exciting things are happening.
David-W-Fenton
2010-06-03 17:41:53
A:
DAO is hopelessly antiquated, it doesn't support the engines you listed. OLEDB and ADO are complementary, OLEDB provides the dbase engine interface, ADO is a grab bag of the classes you'll use in your code to program the dbase interface. ADO.NET in the .NET framework.
For Access you'd use the classes in the System.Data.OleDb namespace, your connection string uses the ACE provider. For SQL you'd best use the classes in the System.Data.SqlClient namespace. Visit connectionstrings.com for help with configuring the connection strings.
Hans Passant
2010-06-01 09:48:37
thnx... it helped me alot for selecting the right one... I am starting with oledb for my access db.
KoolKabin
2010-06-01 09:59:03
DAO is not "hopelessly outdated" -- it's in full development as the native data interface layer to Jet/ACE, the database engine for Access. Jet was forked by the Access dev team for Access 2007 and renamed ACE, and is continuing to be developed extensively. DAO is being updated accordingly. It is true that DAO is tied to Jet/ACE, but that's an awfully good way to access a whole lot of data, because Jet has a lot of intelligence built into it.
David-W-Fenton
2010-06-03 01:40:08
Classic ADO is completely dead and nobody should be using it for anything, except where there is no better alternative.
David-W-Fenton
2010-06-03 01:43:12
http://meta.stackoverflow.com/questions/51441/most-and-least-dangerous-tags-to-answer-among-the-tags-with-1000-questions I bet it's this pita.
Hans Passant
2010-06-03 02:03:23
+1
A:
As far as the Access part of his answer, I disagree with Hans.
If you are using Access, you'd be much better off using DAO or ADO. DAO is the choice of many as it is usually easier and is the native data access model for MSAccess. Look at http://stackoverflow.com/questions/1039224/is-it-better-to-use-ado-or-dao-in-access-2007 for a more thorough discussion.
KevenDenen
2010-06-01 17:02:32