How to connect to a MS Access file (mdb) using C#?

c# ms-access oledb

105,185

Solution 1

The simplest way to connect is through an OdbcConnection using code like this

using System.Data.Odbc;

using(OdbcConnection myConnection = new OdbcConnection())
{
    myConnection.ConnectionString = myConnectionString;
    myConnection.Open();

    //execute queries, etc

}

where myConnectionString is something like this

myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" + 
"Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

See ConnectionStrings

In alternative you could create a DSN and then use that DSN in your connection string

Open the Control Panel - Administrative Tools - ODBC Data Source Manager
Go to the System DSN Page and ADD a new DSN
Choose the Microsoft Access Driver (*.mdb) and press END
Set the Name of the DSN (choose MyDSN for this example)
Select the Database to be used
Try the Compact or Recover commands to see if the connection works

now your connectionString could be written in this way

myConnectionString = "DSN=myDSN;"

Solution 2

Here's how to use a Jet OLEDB or Ace OLEDB Access DB:

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

string myConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source=C:\myPath\myFile.mdb;" +                                    
                           "Persist Security Info=True;" +
                           "Jet OLEDB:Database Password=myPassword;";
try
{
    // Open OleDb Connection
    OleDbConnection myConnection = new OleDbConnection();
    myConnection.ConnectionString = myConnectionString;
    myConnection.Open();

    // Execute Queries
    OleDbCommand cmd = myConnection.CreateCommand();
    cmd.CommandText = "SELECT * FROM `myTable`";
    OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close conn after complete

    // Load the result into a DataTable
    DataTable myDataTable = new DataTable();
    myDataTable.Load(reader);
}
catch (Exception ex)
{
    Console.WriteLine("OLEDB Connection FAILED: " + ex.Message);
}

Solution 3

What Access File extension or you using? The Jet OLEDB or the Ace OLEDB. If your Access DB is .mdb (aka Jet Oledb)

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

namespace MembershipInformationSystem.Helpers
{
    public class dbs
    {
        private String connectionString;
        private String OleDBProvider = "Microsoft.JET.OLEDB.4.0"; \\if ACE Microsoft.ACE.OLEDB.12.0
        private String OleDBDataSource = "C:\\yourdb.mdb";
        private String OleDBPassword = "infosys";
        private String PersistSecurityInfo = "False";

        public dbs()
        {

        }

        public dbs(String connectionString)
        {
            this.connectionString = connectionString;
        }

        public String konek()
        {
            connectionString = "Provider=" + OleDBProvider + ";Data Source=" + OleDBDataSource + ";JET OLEDB:Database Password=" + OleDBPassword + ";Persist Security Info=" + PersistSecurityInfo + "";
            return connectionString;
        }
    }
}

Solution 4

You should use "Microsoft OLE DB Provider for ODBC Drivers" to get to access to Microsoft Access. Here is the sample tutorial on using it

http://msdn.microsoft.com/en-us/library/aa288452(v=vs.71).aspx

View more solutions

105,185

Author by

Sushan Ghimire

In the end, only three things matter: how much you loved, how gently you lived and how gracefully you let go of things not meant for you. --Buddha

Updated on July 23, 2022

Comments

Sushan Ghimire almost 2 years

I'm trying to connect to a mdb file and I understand that I would need Microsoft.OLEDB.JET.4.0 data provider. Unfortunately, I do not have it installed on the (University) machine. Since, they don't provide that provider, I believe there should be a way around.

How can I connect to the file without Microsoft.OLEDB.JET.4.0 or is there any alternative ?

I have following providers:

I have tried using OLE DB Provider for Microsoft Directory Services, to which while testing connection, I get 'Test succeeded but some settings were not accepted by the provider'. I took that string and used it anyway and I got ADsDSOObject' failed with no error message available, result code: DB_E_ERRORSINCOMMAND(0x80040E14).
Sushan Ghimire about 12 years

That gave me: Test Connection failed because of an error in initializing provider. Data source name not found and no default driver specified.. Do you think this provider does not exist?
Kishore Kumar about 12 years

If you are using MS Access 2010
Cinchoo about 12 years

Please refer this link for available connection strings connectionstrings.com/access-2007
Sushan Ghimire about 12 years

Thanks OdbcConnection did it.
itsho almost 10 years

when MDW is in picture, you can need different connection string. see here
Bionic over 9 years

Isn't the OleDbConnectionStringBuilder class the better solution? msdn.microsoft.com/de-de/library/…
Pastor Cortes almost 7 years

Pretty good answer, you can also take a look to this other question to check how to use Microsoft Access with Entity Framewor.
Fritz45 almost 5 years

Excellent solution. Very helpful and worked for me!