no read permission on 'table' - OleDb & Microsoft Access Database

Question

I want to get some information stored inside a Access Database with C#.

This is my Code:

using System.Data.OleDb;

OleDbConnection connection = new OleDbConnection(
       @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\tmp\bch\data.mdb;Persist Security Info=False"
);

connection.Open();

OleDbCommand command = new OleDbCommand("SELECT DesignId from Design WHERE Design='Bamboo'", connection);
OleDbDataReader reader = command.ExecuteReader();
int? id = null;
while (reader.Read())
{
   id = int.Parse(reader[1].ToString()!);
}

connection.Close();

I tried to connect with a OleDbConnection, which works perfectly fine, but i can't get any data from the Database, i always get the following error:

System.Data.OleDb.OleDbException: 'Record(s) cannot be read; no read permission on 'Design'.'

I am able to open the .mdb file by hand and look at the data, but if i execute the code with the same user, it doesn't work...

In some threads i saw the solution to grant the select command to the user first, like this:

OleDbCommand grantCommand = new OleDbCommand("GRANT SELECT ON TABLE Design TO Admin", connection);
grantCommand.ExecuteNonQuery();

Unfortinately this throws another exception and i don't know what to do...

System.Data.OleDb.OleDbException: 'Cannot open the Microsoft Access database engine workgroup information file.'

Answer 1

Ok, the first step is to setup a working connection.

So, in the project, let's add a new connection to the Access database.

It also not quite clear why a mdb is being used in place of an accDB file, but the mdb file format should work, but very "older" mdb files often can't be read/used by ACE data engine, and you have to choose + use the JET data engine.

So, in project settings, add a new connection.

And don't forget in above to hit the Advanced button, as you want to choose (or check and verify) that you picked ACE in place of JET. However, as noted, for a mdb file, you CAN use JET, but let's for now go with the ACE data engine.

So, clicking on Advanced in above, we can look at (and check) if we choose ACE data engine.

Thus, on advanced tab, check this setting:

You will also note that I did NOT use the test connection button. The reason why is I am using vs2022, and vs2022 is a x64 bit product, but my office/Access install is a x32 bit version of office.

You MUST SET and MUST MATCH the bit size of your .net project to that of the version of office/Access that you have installed.

So, that is this setting:

Thus, you can not (should not) use ANY CPU, and you MUST force the project to match the bit size of Access that you are using.

Ok, so with the above setting, then we can now say pull some data.

(I would test pulling some data first before trying to update some data).

So, a form with a button and a DataGridView.

Code:

private void button1_Click(object sender, EventArgs e)
{

    DataTable rstHotels = new DataTable();

    using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
    {
        string strSQL
            = @"SELECT FirstName, LastName, City, HotelName, Description
                FROM tblHotelsA
                ORDER BY HotelName";

        using (OleDbCommand cmdSQL = new OleDbCommand(strSQL, conn))
        {
            conn.Open();
            rstHotels.Load(cmdSQL.ExecuteReader()); 
            dataGridView1.DataSource = rstHotels;
        }
    }            
}

We have this result:

Note that if you using a version of Visual studio before vs2022, then the test connection button WILL work, but in vs2022, the test connection button when creating the connect as per above will NOT work unless you are running/using the x64 bit version of Access.

So, follow the above steps, and setup ONE connection that you can thus use throughout your application.