1 2 Previous Next 22 Replies Latest reply: Mar 15, 2007 1:23 PM by 482477 RSS

    Can't access MS ACCESS

    216630
      Hello,
      I just tested the new feature of connecting to a MS Access db. Yet I get
      An error was encountered performing the requested operation:
      
      [Microsoft][ODBC Microsoft Access Driver] Datensätze 
      können nicht gelesen werden. Keine Leseberechtigung auf
      'MsysObjects'.
      
      Vendor code -1907
      Meaning: can't read - no permission for MsysObjects.

      Does MS Access have to be installed?

      Merry christmas
      Marcus
        • 1. Re: Can't access MS ACCESS
          436317
          Hi,
          yes you have to install MS Access. How else do you think you can read something?
          • 2. Re: Can't access MS ACCESS
            216630
            Hi,
            yes you have to install MS Access. How else do you
            think you can read something?
            I don't know how SQL-Developer is reading the data.E.g. I don't have to install Excel to read XLS.files because OpenOffice can read the XLS-format too.

            Marcus
            • 3. Re: Can't access MS ACCESS
              Kris Rice-Oracle
              You have to grant access to the system objects which is off by default. To do this you would have to open the mdb with Access and goto the Tools->Options and make sure System Objects is checked.

              Then you'll have to grant access to those system objects. This is done my going to Tools->Security.


              This is about the extent of what I know about when the guys get back next week , they should be able to answer more if you are still stuck.

              -kris
              • 4. Re: Can't access MS ACCESS
                216630
                Then you'll have to grant access to those system
                objects. This is done my going to Tools->Security.
                Hello Kris,
                that's it. When I allowed 'read' for MSys... tables I could connect.

                Thanks
                Marcus
                • 5. Re: Can't access MS ACCESS
                  castorp
                  You have to grant access to the system objects which
                  is off by default. To do this you would have to open
                  the mdb with Access and goto the Tools->Options and
                  make sure System Objects is checked.

                  Then you'll have to grant access to those system
                  objects. This is done my going to Tools->Security.
                  Why is that?

                  Which information is needed by SQL Developer that is not provided by the JDBC API?

                  I can get a list of tables and views through JDBC without the need to give read access to those tables (it does not even require Access to be installed, the necessary ODBC driver is part of Windows)
                  • 6. Re: Can't access MS ACCESS
                    90461
                    Hello All,

                    I still can't get this working. I've got a access database called db1. There are 2 tables and a linked table it Microsoft Access 2000 file format.

                    1) tools options and under the view tab I've checked the box next to system objects.
                    2) Tools\Security\User and Group Permissions
                    3) Checked the box next Groups
                    4) Selected Users from the User/Group Name box
                    5) Select all the MSys objects from the object name box where object type box is set to table
                    6) checked the read data box which automatically also checks the read design box
                    7) click apply and then ok
                    8) Closed Access Database
                    9) Open SQL Developer select create new connection
                    10) Name connection browsed to mdb file
                    11) click test button

                    Permissions Error
                    No Read Access to System Tables. Modify Access db before retrying

                    If I click ok, in the bottom left hand corner of the New/Select Database connection it says success. If I then click connect it comes up with the same error message, but it closes the new connection box and I can see the connection in the connection tree. When I click the + next to the connect and then to the tables it says ".....no read permission on 'MsysObjects'.

                    am I doing something wrong?

                    I've just checked and if I do a SQL Statement it works, even though the above error messages etc appear and I can't actually see the table queries etc in the connection tree.

                    Message was edited by: rmcrae
                    rmcrae
                    • 7. Re: Can't access MS ACCESS
                      485301
                      Hi Thomas,
                      Access to MSysObjects is required to be able to list items such as forms, macros, modules etc.

                      Regards,
                      Dermot.
                      • 8. Re: Can't access MS ACCESS
                        Barry McGillin
                        mccrae,
                        Everthing u are doing is right. The problem as dermot puts it is that you need to be able to read those tables to use the explorer. Access is not great at sharing its metadata with the world and so it requires SU proviliges on the db to do that.
                        You will definitely get the connection, its just jdbc/odbc, but you need the extra permissions to get the navigator to show you the objects in the dtabase.
                        B
                        • 9. Re: Can't access MS ACCESS
                          529882
                          After checking "system objects" in the View section of Tools > Options, you have to make sure that Admin can read those new objects.

                          In Tools > Security > User and Group Permissions, check each table that is prepended with "MSys..." to see if the Permissions boxes are checked. If they are not, check them. Once Admin has full privileges on these tables, SQLDeveloper should have no problems.
                          • 10. Re: Can't access MS ACCESS
                            90461
                            Thanks All,

                            I thought I had checked the permissions on the MSys... tables, but I must have not applied them or something, as I've just gone back in and they were unchecked.

                            Now that I've checked all the MSys... tables its working!

                            Cheers
                            • 11. Re: Can't access MS ACCESS
                              310908
                              I checked all permission from all tables to all user and to all groups.
                              Thereafter I could open the connection to the MSAccess-database, but the containers (Tables Views etc. ) are 'empty'.

                              Where did I go wrong ??
                              • 12. Re: Can't access MS ACCESS
                                562688
                                I did exacly the same and I am encountering the same difficulty browsing MS Access DB. All the containers are empty.

                                Does anyone have any clue to this problme?

                                OSD site does not provide any walk throughs or tutorials on how to go about accessing MS Access DB. I wonder if this feature even works as claimed.
                                • 13. Re: Can't access MS ACCESS
                                  564177
                                  I have exactly the same problem. I have checked read access to all Msys* stuff. And the connect to Access goes fine, but all empty ?

                                  My access is Access 2003 on Windows XP

                                  regards
                                  Mette
                                  • 14. Re: Can't access MS ACCESS
                                    216630
                                    Hello Michael,
                                    it worked for me this way with SQL-D 23.64. Yet with 25.14 I get only empty folders.

                                    Regards
                                    Marcus
                                    1 2 Previous Next