2

If I try to read the schema of a Access 2013 database I receive the following error :

no read permission on 'MSysRelationships'

Now the help tells me that

User-level security features are not available in Access apps, web databases, 
or databases that use one of the new file formats (.accdb, .accde, .accdc, .accdr).

=> How can I read the schema of that DB ?

Edit :

here is my code in Linqpad :

var stringBuilder = new OleDbConnectionStringBuilder();
stringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
stringBuilder.DataSource = @"C:\Recommendations.accdb";

using (var connection = new OleDbConnection(stringBuilder.ToString().Dump()))
{
    connection.Open();
    var schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
    var sheetName = schemaTable.AsEnumerable().Select(row => row.Field<string>("TABLE_NAME")).First();
    schemaTable.Dump();

    string  namesQuery = "SELECT DISTINCT szRelationship, szReferencedObject, szObject FROM MSysRelationships";
    string  namesQuery2 =  string.Format("select Name, Age from [{0}]", "Users");

    using (var command = new OleDbDataAdapter(namesQuery, connection))
    {
        var dataSet = new DataSet();
        command.Fill(dataSet, sheetName);
        dataSet.Dump();
    }
    connection.Close();
}
nicolas
  • 9,549
  • 3
  • 39
  • 83
  • What mechanism are you using to try and "read the schema"? FWIW, I just typed `SELECT * FROM MSysRelationships` into the SQL pane of an Access_2013 query and it worked fine. – Gord Thompson Mar 19 '13 at 16:21
  • those new interface really have me lost. I cant even see where SQL Pane would be located..... – nicolas Mar 19 '13 at 17:13
  • I can access the MSysResources through the Gui once I set the hidden tables to show (File / Options / Current Database / Navigation Options) – nicolas Mar 19 '13 at 17:19

2 Answers2

2

Judging by the responses to a similar earlier question here, I suspect that you simply cannot directly read the system tables in Access via an OleDb connection. However, you might be able to get the information you need via the OleDbConnection.GetSchema method (details here).

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
2

In the Connection string, set 'User Id=Admin'.

Open the database with Access, and Ctrl-G to get the 'Immediate' window, and then execute the following command:

CurrentProject.Connection.Execute "GRANT SELECT ON MSysRelationships TO Admin;"

This will give 'User ID' permission to read (SELECT) from that table. The same applies to the other system tables, like 'MSysObjects'.

nwsmith
  • 475
  • 4
  • 8