Need help creating Data Access Object for Access databases RRS feed

  • Question

  • User1913889050 posted

    I have a small web application that displays data from a desktop application, and I am updating it.  Converting DataGrids to ListViews, adding in some Ajax.

    I would like to create a DAO for the databases, but the desktop application from which I get the data has the WORST database design ever.  Think of it as forcing Access to work like a flat file - something you could do in Excel.  When trying to create a DAO with Visual Studio, VS sort of says "You want what..?" and I don;t know what to tell it.

    Typically the examples one finds through Google are using SQL Server (lucky buggers) or XML, and I cannot see my way through to a solution for my particular problem.

    I'm open to outright help, referrals to reference articles or discussions, even to suggestions to buy a book...  Anything that would help.



    Friday, November 14, 2008 9:45 AM

All replies

  • User-319574463 posted

     For general Access question try Access World Forum http://www.access-programmers.co.uk/forums/

    Also look at http://www.mikesdotnetting.com/Category.aspx?Category=10 done by an esteemed fellow contributor to this site, MikesDotNetting!

    Wednesday, November 19, 2008 7:45 AM
  • User1913889050 posted

    Sorry: I need to learn to create a .NET 3.5 DAO to be used with Access databases.  I know how to use the DAO for Access databases on the desktop - although this particular desktop application's developer could use some help!  :o)

    Mikesdotnetting doesn't seem to have a relevant article for what I need to learn; I go there often.

    Thanks for trying to help.  I appreciate your time.


    Wednesday, November 19, 2008 9:56 AM
  • User-821857111 posted

    There are no articles on this topic on my site because I have never used DAO.  Well, not DAO as I understand it.  Just to clarify we are on the same page, are you trying to use that DAO in a web scenario?  And what is it that you are trying to achieve exactly?


    Wednesday, November 19, 2008 6:04 PM
  • User1913889050 posted

    Well hi, Mike!

    The web application is a mongrel mix of .Net 1.1 and 2.0; it uses DataGrids where ListViews and GridViews are probably (almost certainly) more appropriate.  As part of the update and conversion to .Net 3.5, I hope to be able to better isolate the data access layer.

    From somewhere I don't remember, I found a home-rolled data calender the uses an Access database, and uses a DAO (calendarDAO.cs) to handle the read/write back to a single Access database (I arrogantly didn't bookmark the tutorial, and cannot find it again).  I've been studying how the author created and used a DAO and have been playing with that for weeks trying to adapt it to what I need to do, and while it has hints, it has no real help on how I would use a similar DAO to open and combine multiple databases.  I know how to open and combine the multiple databases in a codefile, but I hope to be able to separate that out of the rest of the business logic and put it into what can be called as a DAO to populate the new data displays.

    Is this a reasonably compehensible explanation of what I hope to do?  If not, please ask and I'll try to explain it -- I'm new to working in the 3.5 framework, and I may be stumbling around and mixing-up the terms.

    And thanks, Mike.  What a privilege!


    Thursday, November 20, 2008 7:43 AM
  • User-821857111 posted

    Hi Tinker,

    You make me sound like a celebrity.  I'm not sure quite how that makes me feel... strange, to say the least [;)] 

    Your quest to separate concerns is laudable.  It might be that a custom entity approach would be best.  If you would like to know more, have a look at Imar Spaanjaars excellent article series:  http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=416


    Thursday, November 20, 2008 10:08 AM
  • User1913889050 posted

    You post a lot of good stuff and help a lot of people. Mike, and I am grateful you took an interest in my question.  And I always meet the nicest folks here in the forums, and am grateful to everyone who points me in a helpful direction (or tells me when I'm uselessly pounding my head against the wall).

    Imar's articles may help me see the light, and thanks.

    This is the DAO for an Access database from the calendar tutorial I mentioned that originally set me off on this path.  It seems to fit the MSDN defintion:

    using System;
    using System.Data;
    using System.Data.OleDb;

    public class CalendarDAO {
      private string strConn;
      private OleDbConnection dbConn;

      public CalendarDAO()
        strConn = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["calendarTest"].ToString();
        dbConn = new OleDbConnection(strConn);

      public void CloseConnection()

      public DataTable Get_Events (int currentDate, int month, int year) {
        OleDbCommand cmd = null;
        OleDbDataAdapter daOleDb = new OleDbDataAdapter();
        DataTable dtEvents = new DataTable();

        string strSQL = null;
        strSQL = "SELECT * FROM Events WHERE @date1 >= dateFrom AND @date2 <= dateTo";
        cmd = new OleDbCommand(strSQL, dbConn);
        OleDbParameter paramDate1 = new OleDbParameter("@date1", currentDate, month, year);
        OleDbParameter paramDate2 = new OleDbParameter("@date2", currentDate, month, year);

        daOleDb.SelectCommand = cmd;

        return dtEvents;


    Now I'm off to follow Imar's articles.



    Thursday, November 20, 2008 10:40 AM