No read permission on 'MSysObjects' (Error No: -2147217911) when Using Microsoft Excel VBA to Query Access accdb files only - mdb works fine

0
votes

No read permission on 'MSysObjects' (Error No: -2147217911) when Using Microsoft Excel VBA to Query Access accdb files only - mdb works fine

Using Microsoft Excel VBA to Query Access.

With an mdb file this connection string opens the mdb file fine and the SQL String returns the tables I want.

Connection String: Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\My Files\Word\ADA II Text\Chapters\9 -



8\Access\ADAIICH9TEST.mdb";User Id=admin;Password=;
SQL String: SELECT MSysObjects.Name From MSysObjects WHERE MSysObjects.Name Not Like "MSyS*" And Type=1 AND 

Flags=0 ORDER BY MSysObjects.Name;

With an accdb file this connection string opens the accdb file fine but the SQL throws the following error:

Error No: -2147217911 Error Desc: Record(s) cannot be read; no read permission on 'MSysObjects'.

Why does it have access to MSysObjects when reading the mdb file but not the accdb file? How do I grant access either programmatically or from within the Access File?

I have already tried executing the following statements prior to running the SQL:

'cn.Execute "GRANT SELECT ON MSysObjects TO Admin;"
'cn.Execute "GRANT SELECT ON TABLE MSysObjects TO PUBLIC;"

They throw the error: Error No: -2147467259 Error Desc: Cannot open the Microsoft Access database engine workgroup information

file.

1
excelvbams-access

1 Answers

0
votes

Within Access you need to give permission for the default user to access the table.

In the immediate window with Access execute 

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

And your SQL will work after that.

You might want to change your connection string. IIRC Jet won't work after Access 2003 so try

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\...ADAIICH9TEST.accdb;