C++ CLI How to read data from a table in Access database?

Question

For example, if I have an Access Database Data.accdb with 2 tables inside: Table1 and Table2, how can I read data from Table1? I know how to open the Sql connection though.

#using <system.data.dll>

using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::Data::OleDb;

SqlConnection ^connection = gcnew SqlConnection(L"server=local;Trusted_Connection=yes;database=Data;");
connection->Open();

But beyond that, I don't know what to do next. How to open Table1 and read data from it, like read ID column?

Sebacote Sebacote · Accepted Answer · 2015-08-31T13:18:02

You can use OleDb to manipulate an Access database.

#include <msclr\auto_handle.h>

int main(array<System::String ^> ^args)
{
    using namespace System;
    using namespace System::Data::OleDb;

    OleDbConnection connection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\MyDatabase.accdb;Persist Security Info=False;");
    connection.Open();

    // Reading all records
    {
        msclr::auto_handle<OleDbCommand> command = connection.CreateCommand();
        command->CommandText = "select * from Table1";
        msclr::auto_handle<OleDbDataReader> reader = command->ExecuteReader();

        while (reader->Read())
        {
            Console::WriteLine(
                "ID={0}, DESCRIPTION={1}",
                reader.get()["ID"],
                reader.get()["DESCRIPTION"]);
        }
    }

    // Insert new record
    {
        msclr::auto_handle<OleDbCommand> command = connection.CreateCommand();
        command->CommandText = "insert into Table1 (ID, DESCRIPTION) values (3, 'THREE')";
        command->ExecuteNonQuery();
    }

    // Changing the DESCRIPTION field for all records
    {
        msclr::auto_handle<OleDbCommand> selectCommand = connection.CreateCommand();
        selectCommand->CommandText = "select * from Table1";
        msclr::auto_handle<OleDbDataReader> reader = selectCommand->ExecuteReader();

        while (reader->Read())
        {
            auto newDescription = reader.get()["DESCRIPTION"]->ToString();
            newDescription += " - UPDATED!";

            msclr::auto_handle<OleDbCommand> updateCommand = connection.CreateCommand();
            updateCommand->CommandText =  "update Table1 set DESCRIPTION=? where ID=?";
            updateCommand->Parameters->AddWithValue("DESCRITPION", newDescription);
            updateCommand->Parameters->AddWithValue("ID", reader.get()["ID"]);
            updateCommand->ExecuteNonQuery();
        }
    }

    return 0;
}

C++ CLI How to read data from a table in Access database?

1 Answers