Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error opening MSysobject (97)

    I have a VB project were I am trying to find all the access databases on a server and determine if they have code modules.

    I am working on the function where I try to open a found file and read the MSysObjects table. I am getting an error:
    3112 Records cannot be read; no read permission on MSysobjects
    There is no security on this MDB and when I open the mdb directly I can run the qry w/o a problem so I am fairly certain I am goofing up the code.

    Here is what I am doing:
    Dim db As DAO.Database
    Dim rstInput As DAO.Recordset


    Set db = DBEngine.Workspaces(0).OpenDatabase("pathfilename" )

    Set rstInput = db.OpenRecordset _
    ("SELECT [Type], [Name], [Connect] FROM MSysObjects", dbOpenForwardOnly, dbReadOnly)

    The SET statement is the one that throws the error. I have tried with a 97, 2000, and 2002 mdb format. I am using DAO 3.6 and have the reference set properly

    Does anyone have an idea?

    Thanks - Dave

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error opening MSysobject (97)

    The MSysObjects table is a hidden system table in Access. All Access database have security on them. You just don't see it unless someone applies a password to the Admin account. Externally, you may have to pass the Admin login and an empty password to get access to the system tables, althouth I can't say for certain because I've never tried to look at them from VB.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error opening MSysobject (97)

    That makes sense but I'll be darned if I can find or figure out the syntax. Here is what doesn't work:

    Set wks = DBEngine.CreateWorkspace("", "Admin", "")
    Set db = wks.OpenDatabase _
    ("pathfilename", False, True, , ";pwd=")

    Any ideas?

    Thanks - Dave

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error opening MSysobject (97)

    All you need in the second statement is the path and file name. You've already passed it the user and password in the first line, and the table is read-only regardless since it's a system table. The problem actually occurs when you try to set the recordset object. You can actually instantiate a tabledef object for the MSysObjects table, but you don't have permissions to read it. I suspect that's part of the way that Access protects itself from outside intrusion.

    There's another way to do it less directly. Try something like this:

    <pre> Dim wsp As dao.Workspace
    Dim dbs As dao.Database
    Dim ctr As dao.Container
    Dim doc As dao.Document
    Dim intLoop As Integer
    Dim strContainer As String

    Set wsp = DBEngine.CreateWorkspace("", "Admin", "", dbUseJet)
    Set dbs = wsp.OpenDatabase("D:Access97 DocsDisplayNames97.mdb", True, False)

    On Error Resume Next
    For intLoop = 1 To 3

    strContainer = Choose(intLoop, "Modules", "Forms", "Reports")

    Set ctr = dbs.Containers(strContainer)
    For Each doc In ctr.Documents
    Print doc.Name
    Next doc
    Next intLoop
    Set doc = Nothing
    Set ctr = Nothing
    Set dbs = Nothing
    Set wsp = Nothing</pre>


    I had this printing the names of the modules, forms and reports to a form. Does that help?
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error opening MSysobject (97)

    Charlotte,

    Thanks for the idea. It doesn't let me do everything I wanted (getting DNS, all object names, etc ) but certainly enough to figure out which databases need further review as we plan our upgrade to 2002.

    Thanks for your help.
    Dave

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error opening MSysobject (97)

    If by DNS you mean the connection for the tables, you can read that directly from the tables themselves through their Connection property (I'm assuming we're talking about linked tables), and that is accessible with DAO.

    I don't know which "all objects" you are referring to. MSysObjects holds the information on "documents" in an Access database, i.e., tables, queries, forms, reports, macros and modules. It doesn't hold any details on what's inside those objects.

    If you're trying to analyze these databases, you might want to look into one of the third party tools that will do some of this for you. Try <A target="_blank" HREF=http://www.fmsinc.com>http://www.fmsinc.com</A> and take a look at their Total Access tools.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •