Access Database, Connection string jiggery pokerery!

Question

Hi,

I am trying, from my c# codefile to access an Access Database. If I use the:

SqlConnection connection = new SqlConnection(connectionString)

with the connection string being:

connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\testing_dev\sm_development\App_Data\SMWeb.mdb"

I get an error when I try and create a dataset that the word 'provider' is not supported! What am I doing wrong?

Thanks, R.

Answer 1

Try this.

OleDbConnection connection = new OleDbConnection();
string connectionString= @"Data Source=F:\testing_dev\sm_development\App_Data\SMWeb.mdb";

OleDbConnection is in the System.Data.OleDb namespace.

Answer 2

The "Sql" in "SqlConnection", "SqlCommand", and generally anything in System.Data.SqlClient refers strictly to Sql Server. MS Access is definitely not Sql Server. Look in the System.Data.OleDb namespace instead.

Answer 3

Put down the SqlConnection and pick up the OleDbConnection:

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

public class DataAccess
{
    string connectionString = ConfigurationManager.ConnectionStrings["KeyName"].ConnectionString;

    public DataSet GetData( string sql, string tableName )
    {
        using( var conn = new OleDbConnection( connectionString ) )
        {
            conn.Open();
            var da = new OleDbDataAdapter( sql, conn );
            var ds = new DataSet();
            da.Fill( ds, tableName );
            return ds;
        }
    }
}