18

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:

Available Ole DB 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).

Improve this question

6 Answers 6

Reset to default
28

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;"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks OdbcConnection did it.
when MDW is in picture, you can need different connection string. see here
Pretty good answer, you can also take a look to this other question to check how to use Microsoft Access with Entity Framewor.
12

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);
}
Improve this answer

1 Comment

Excellent solution. Very helpful and worked for me!
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

Improve this answer

2 Comments

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?
Please refer this link for available connection strings connectionstrings.com/access-2007
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;
        }
    }
}
Improve this answer

1 Comment

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

Try this..

using System.Data.OleDb;

OleDbConnection dbConn;

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

1 Comment

If you are using MS Access 2010
0

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>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.