Click here to Skip to main content
12,300,379 members (52,056 online)
Click here to Skip to main content
Add your own
alternative version

Stats

15.8K views
988 downloads
12 bookmarked
Posted

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

, 18 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Read and write data to Access databases without limitations caused by the use of the SQL.

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();

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


You may also be interested in...

Comments and Discussions

 
QuestionDao usefull for Vb6 Pin
Member 1045517619-Aug-14 9:10
memberMember 1045517619-Aug-14 9:10 
AnswerRe: Dao usefull for Vb6 Pin
Emiliarge19-Aug-14 9:26
memberEmiliarge19-Aug-14 9:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160525.2 | Last Updated 18 Aug 2014
Article Copyright 2014 by Emiliarge
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid