Gathering data from Access database

Question

I want to gather some data from some tables of an Access Database, I've found some solutions online, but I haven't found ways to fill a datatable, or dataset, and get each single field properly.

Is it easier for me to get whole tables then get just the info that i want, or should I make a lot of searches in the access DB getting just what i Want each time? Any code snippets for it?

info:

• The Access Database is in an ACCDB file, with no user or password
• I'm currently using VB.NET, but it doesn't matter if you answer in C#

--[EDIT]--
Sub question:
Connecting to ACCDB format MS-ACCESS database through OLEDB

Parthenos

Answer 1

From here, you use the OleDbDataReader:

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

class MainClass
{
  static void Main(string[] args)
  {
    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\\Northwind.mdb";

    OleDbConnection conn = new OleDbConnection(connectionString);

    string sql = "SELECT * FROM Orders";

    OleDbCommand cmd = new OleDbCommand(sql, conn);

    conn.Open();

    OleDbDataReader reader;
    reader = cmd.ExecuteReader();

    while (reader.Read()) 
    {
      Console.Write(reader.GetString(0).ToString() + " ," );
      Console.Write(reader.GetString(1).ToString() + " ," );
      Console.WriteLine("");
    }

    reader.Close();
    conn.Close();
  }
}

Clarethaclaretta

Still applying, ass soon as I complete it i'll post in here. I'm migrating from other DB, so I have to take each data (connstr, location) from somewhere different. Thank you, by the way =) – Parthenos

The connection string should include Microsoft.ACE.OLEDB.12.0 not Microsoft.JET.OLEDB.4.0 (connectionstrings.com/access-2007) – Draggletailed

It works. But what if i want to call the field from it's name, not from it's ordinal position in the table ? (without selecting only the field) – Parthenos

You can't directly. What you can however do is set up an Enum with the correct name->ordinal lookup, but this can get messy. – Clarethaclaretta

Thank you. It actually didn't work with ACCDB, only with .MDB (damn) check question linked in the edit. – Parthenos

The Jet 4 provider won't work with ACCDB. The answer should be edited to use the ACE provider since it's quite clearly stated in the original question that it's an ACCDB not MDB. – Diarrhoea

@KyleRozendo I ask a related Question here: #14476468 , would you please check it – Bathilda

Answer 2

If you can fill a DataSet, you have all data (fields) in memory.

• In your Project, use the Data menu to add a DataSource.
• Follow the Wizard. It will create a Typed DataSet for you.
• Drag the new DataSource to a Form. That will show you the code to fill the DS.

Crispa

I ask a related Question here: #14476468 , would you please check it – Bathilda

Answer 3

I wrote this test program to retrieve data from a DAO database. This should work for you too.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;


namespace TestReadCfg
{
  class Program
  {
    static void Main(string[] args)
    {
        string connectionString =
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
        + "c:\\Avtron\\addapt\\Configuration\\testDao.db;Jet OLEDB:Database Password=RainbowTrout;";

        string queryString = "SELECT * from Sections order by Address";

        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            // Create the Command and Parameter objects.
            OleDbCommand command = new OleDbCommand(queryString, connection);
            try
            {
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                int iRecNbr = 1;
                while (reader.Read())
                {
                    String sRecord = string.Empty;
                    sRecord = string.Format("Record {0}: ", iRecNbr);
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        sRecord += string.Format("{0} ", reader[i].ToString());
                    }
                    Console.WriteLine(sRecord);
                    iRecNbr++;
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();

        }

   }
}

Secunderabad