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

  • Question

  • 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:
    Connection String: Provider=Microsoft.ACE.OLEDB.12.0;Data Source="P:\My Files\Word\ADA II Text\Chapters\9 - 

    8\Access\ADAIICH9TEST.accdb";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;
    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.
    Wednesday, May 29, 2019 3:27 AM

All replies

  • Hello,

    If you want those here to assist guide you with VB.NET you are in the right place, if you are looking for Excel VBA let me know and I will move your question to the correct forum. My guess is you want Excel VBA help not VB.NET.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 29, 2019 1:44 PM
    Moderator