C# / VB.NET / C++ CLI: Read and write MS Access (mdb) database without ADO and SQL via Microsoft DAO

• Download DAO in CSharp [BINARY + SOURCE IN VS2008] - 165.6 KB
• Download DAO in CSharp [BINARY ONLY] - 40.3 KB
• Download DAO in VB.NET [BINARY + SOURCE IN VS2008] - 177.1 KB
• Download DAO in VB.NET [BINARY ONLY] - 45.5 KB
• Download DAO in C++ CLI [BINARY + SOURCE IN VS2008] - 195.2 KB
• Download DAO in C++ CLI [BINARY ONLY] - 54.3 KB

Introduction

Despite the fact that ADO is the main tool for interacting with the database Access, it has some serious drawbacks. 
Among them: 

• You need to use SQL
• A saving data in the database is too complexity process. UPDATE-, INSERT-, DELETE-queries are hard to learning by newbie. OleDbCommandBuilder, which generates these automatically, is very poor tool
• You need to create a counter field (id) to execute UPDATE-, INSERT-, DELETE-queries

It would be much easier to work with Access database tables such as matrices and save the changes without the mess with SQL. 

Microsoft DAO provides all of these capabilities.

(Of course, MS Access uses DAO, not ADO.)

But remember what Microsoft Windows doesn't contains DAO by default! This is a part of the Microsoft Office. You must to redistribute it with your applications: http://support.microsoft.com/kb/233002

How to add Microsoft DAO Object Library into your Visual Studio project?

1. Add to your project a reference to COM Microsoft DAO *.* Object Library.
2. Next you will use the classes from the namespace DAO.
3. Rebuild your application in x86 build configuration (C# and VB.NET only).

Code in C#

Open a database:

DAO.DBEngineClass DbEng = new DAO.DBEngineClass();
DAO.Database Db = DbEng.OpenDatabase(@"D:\Database1.mdb", null, null, null);
// Db.TableDefs - tables collection

Select a table:

DAO.Recordset rs = Db.OpenRecordset("Table1", DAO.RecordsetTypeEnum.dbOpenTable, null, DAO.LockTypeEnum.dbOptimistic);

Load table into grid:

//add neccessary columns to grid
foreach (DAO.Field oFl in Rs.Fields)
{
    DataGridView1.Columns.Add(oFl.Name, oFl.Name);
}

if (Rs.RecordCount == 0)
    return;

//add neccessary rows to grid
DataGridView1.Rows.Add(Rs.RecordCount);

Rs.MoveFirst();

for (int i = 0; i <= Rs.RecordCount - 1; i++)
{
    for (int j = 0; j <= Rs.Fields.Count - 1; j++)
    {
        DataGridView1.Rows[i].Cells[j].Value = Rs.Fields[j].Value;
    }

    Rs.MoveNext();
}

Add row into table:

Rs.AddNew();
Rs.Update((int)DAO.UpdateTypeEnum.dbUpdateRegular, false);

Edit row cell in table:

Rs.MoveFirst();
Rs.Move(***ROW INDEX***, null);

Rs.Edit();
Rs.Fields[***CELL INDEX***].Value = "Hurrah!";
Rs.Update((int)DAO.UpdateTypeEnum.dbUpdateRegular, false);

Delete row from table:

Rs.MoveFirst();
Rs.Move(DataGridView1.SelectedRows[0].Index, null);

Rs.Delete();

Code in VB.NET

Open a database:

Dim DbEng As New DAO.DBEngineClass
Dim Db As DAO.Database = DbEng.OpenDatabase("D:\Database1.mdb")

Select a table:

Rs = Db.OpenRecordset("Table1")

Load table into grid:

'add neccessary columns to grid
For Each oFl As DAO.Field In Rs.Fields
    DataGridView1.Columns.Add(oFl.Name, oFl.Name)
Next

If Rs.RecordCount = 0 Then Return

'add neccessary rows to grid
DataGridView1.Rows.Add(Rs.RecordCount)

Rs.MoveFirst()
For i = 0 To Rs.RecordCount - 1
    For j = 0 To Rs.Fields.Count - 1
        DataGridView1.Rows(i).Cells(j).Value = Rs.Fields(j).Value
    Next

    Rs.MoveNext()
Next

Add row into table:

Rs.AddNew()
Rs.Update()

Edit row cell in table:

Rs.MoveFirst()
Rs.Move(***ROW INDEX***)

Rs.Edit()
Rs.Fields(***CELL INDEX***).Value = "Hurrah!"
Rs.Update()

Delete row from table:

Rs.MoveFirst()
Rs.Move(***ROW INDEX***)

Rs.Delete()

Code in C++/CLI

Open a database:

DAO::DBEngineClass ^DbEng = gcnew DAO::DBEngineClass();
DAO::Database ^Db = DbEng->OpenDatabase("D:\\Database1.mdb", nullptr, nullptr, nullptr);
// TableDefs from C# or VB.NET = "default" in C++/CLI!!! O_O

Select a table:

DAO::Recordset ^Rs = Db->OpenRecordset("Table1", DAO::RecordsetTypeEnum::dbOpenTable, nullptr, DAO::LockTypeEnum::dbOptimistic);

Load table into grid:

//add neccessary columns to grid
for each (DAO::Field ^oFl in Rs->default) {
    DataGridView1->Columns->Add(oFl->Name, oFl->Name);
}

if (Rs->RecordCount == 0)
    return;

//add neccessary rows to grid
DataGridView1->Rows->Add(Rs->RecordCount);

Rs->MoveFirst();
for (int i = 0; i <= Rs->RecordCount - 1; i++) {
    for (int j = 0; j <= Rs->default->Count - 1; j++) {
        DataGridView1->Rows[i]->Cells[j]->Value = Rs->default[j]->default;
    }

    Rs->MoveNext();
}

Add row into table:

Rs->AddNew();
Rs->Update(static_cast<int>(DAO::UpdateTypeEnum::dbUpdateRegular), false);

Edit row cell in table:

Rs->MoveFirst();
Rs->Move(***ROW INDEX***, nullptr);

Rs->Edit();
Rs->default[***CELL INDEX***]->default = "Hurrah!"; // this ***** default again!!!
Rs->Update(static_cast<int>(DAO.UpdateTypeEnum::dbUpdateRegular), false);

Delete row from table:

Rs->MoveFirst();
Rs->Move(***ROW INDEX***, nullptr);

Rs->Delete();