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

Question

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).

Unclasp

Answer 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;"

Boron

Thanks OdbcConnection did it. – Unclasp

when MDW is in picture, you can need different connection string. see here – Landri

Pretty good answer, you can also take a look to this other question to check how to use Microsoft Access with Entity Framewor. – Goodfellowship

Answer 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);
}

Unjaundiced

Excellent solution. Very helpful and worked for me! – Federate

Answer 3

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

Thunderstruck

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? – Unclasp

Please refer this link for available connection strings connectionstrings.com/access-2007 – Thunderstruck

Answer 4

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;
        }
    }
}

Homes

Isn't the OleDbConnectionStringBuilder class the better solution? msdn.microsoft.com/de-de/library/… – Cathouse

Answer 5

Try this..

using System.Data.OleDb;

OleDbConnection dbConn;

dConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Registration.accdb;");

Ikkela

If you are using MS Access 2010 – Ikkela

Answer 6

Another simplest way to connect is through an OdbcConnection using App.config file like this

  <appSettings>  
    <add key="Conn" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|MyDB.mdb;Persist Security Info=True"/>
  </appSettings>

MyDB.mdb is my database file and it is present in current primary application folder with main exe file.

if your mdf file has password then use like this

  <appSettings>
    <add key="Conn" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|MyDB.mdb;Persist Security Info=True;Jet OLEDB:Database Password=Admin$@123"/>
  </appSettings>

Jd