No read permission on MSysObject error

Question

I am trying to connect to an MS Access database (.mdb) through OleDb. My query is

SELECT * FROM ListQueries

which fetches me the error

SQL Execution Error.

Executed SQL Statement: SELECT * FROM ListQueries Error Source: Microsoft JET Database Engine Error Message: Records cannot be read; No read permission on 'MSysObjects'.

Then I tried this answer, but it did not help. Then I saw another answer says to do this.

strDdl = "GRANT SELECT ON MSysObjects TO Admin;"
CurrentProject.Connection.Execute strDdl

I do not know how to implement that in my web project. Was writing something like this as per this suggestion by @HansUp

Alternatively, it should work from c# if you run it from an OleDb connection to the Access db

The code is,

OleDbConnection con;
using (con = new OleDbConnection(Connection.connectionString()))
{
    con.Open();
    using (var com = new OleDbCommand("GRANT SELECT ON MSysObjects TO Admin", con))
    {
        com.ExecuteNonQuery();
    }
    using (var com = new OleDbCommand("Select * from ListQueries", con))
    {
        using (var dr = com.ExecuteReader())
        {
            while (dr.Read())
            {
                qryList.Add(SQLReaderExtensions.SafeGetString(dr, "Name"));
            }
            dr.Close();
        }
    }
    con.Close();
}

The first com.ExecuteNonQuery() gives me this error.

Cannot open the Microsoft Jet engine workgroup information file.

I would really like to know how to grant permission for an OleDb call to work. Any suggestions will be wonderful

P.S: BTW, I am using MS Access 2010.

Polymer

I strongly suggest that you do not use MS Access system objects. There are other and better ways to get the information. What exactly are you trying to do? – Oxyacid

@Remou: Queries in MS-Access are analogous to Views in SQL for most part, IMHO. – Polymer

You have a choice of ADO (msdn.microsoft.com/en-us/library/kcax58fh(VS.80).aspx) and DAO. Which would you prefer? Note that in ADO there is a difference between action and select queries. – Oxyacid

@Remou: it worked, will you post it as answer. thanks a lot for your time :) i am been sitting for hours with this. thanks a ton – Polymer

Answer 1

I strongly suggest that you do not use MS Access system objects. There are other and better ways to get the information.

You have a choice of ADO and DAO. Which would you prefer? Note that in ADO there is a difference between action (adSchemaProcedures) and select queries (adSchemaViews).

For example,

public static List<string> GetAllQueriesFromDataBase()
{
    var queries = new List<string>();
    using (var con = new OleDbConnection(Connection.connectionString()))
    {
        con.Open();
        var dt = con.GetSchema("Views");
        queries = dt.AsEnumerable().Select(dr => dr.Field<string>("TABLE_NAME")).ToList();
    }

    return queries;
}

Oxyacid

@naveen you are the c# person :) – Oxyacid

dont embarass me more :) – Polymer