Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Problems with accessing MSysObjects ...

    Ok ... This worked at one time ... when I initially developed it and it has not changed code-wise ... Any ideas on why I get the following error?

    Code:
    Function RemovePreviousTemporaryFiles() As Integer
        On Error GoTo Err_RPTF
        
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
    'SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
    '(Left$([Name],4) <> "Msys") AND (Right$([Name],8) <> "Template")  AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
        SQLString = "SELECT MSysObjects.Name FROM MsysObjects"
        SQLString = SQLString & " WHERE ((Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (Right$([Name],8) <> 'Template') AND (MSysObjects.Type)=1) ORDER BY MSysObjects.Name;"
        TrgRecSet.Open SQLString, FormsConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                If Right(TrgRecSet.Fields(0).Value & "", 6) = CurrentUser() Then
                    DoCmd.SetWarnings False
                    DoCmd.DeleteObject acTable, TrgRecSet.Fields(0).Value
                End If
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        RemovePreviousTemporaryFiles = 1
    Exit_RPTF:
        Exit Function
        
    Err_RPTF:
        
        MsgBox Err.Number & ": " & Err.Description
        RemovePreviousTemporaryFiles = 0
        Resume Exit_RPTF
    End Function
    Error Msg:
    Attached Thumbnails Attached Thumbnails msysobjects error.bmp  

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Ok ... This worked at one time ... when I initially developed it and it has not changed code-wise ... Any ideas on why I get the following error?

    Code:
    Function RemovePreviousTemporaryFiles() As Integer
        On Error GoTo Err_RPTF
        
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
    'SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
    '(Left$([Name],4) <> "Msys") AND (Right$([Name],8) <> "Template")  AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
        SQLString = "SELECT MSysObjects.Name FROM MsysObjects"
        SQLString = SQLString & " WHERE ((Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (Right$([Name],8) <> 'Template') AND (MSysObjects.Type)=1) ORDER BY MSysObjects.Name;"
        TrgRecSet.Open SQLString, FormsConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                If Right(TrgRecSet.Fields(0).Value & "", 6) = CurrentUser() Then
                    DoCmd.SetWarnings False
                    DoCmd.DeleteObject acTable, TrgRecSet.Fields(0).Value
                End If
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        RemovePreviousTemporaryFiles = 1
    Exit_RPTF:
        Exit Function
        
    Err_RPTF:
        
        MsgBox Err.Number & ": " & Err.Description
        RemovePreviousTemporaryFiles = 0
        Resume Exit_RPTF
    End Function
    Error Msg:
    What condititions changed since it worked last?

    What line does the error occur on?

    Does it show up when you compile or just when you run it?

    Have you tried to change the cursortype to adOpenKeyset? It seems to be the most versatile.

    Gregg

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problems with accessing MSysObjects ...

    Originally posted by basicmek
    What condititions changed since it worked last?

    What line does the error occur on?

    Does it show up when you compile or just when you run it?

    Have you tried to change the cursortype to adOpenKeyset? It seems to be the most versatile.

    Gregg
    Gregg,

    1) On my open statement
    2) Runtime only
    3) My CursorType being forwardonly is perfectly fine ...

    I'm wondering if it has to do with my using ADO to attempt to retrieve the internal table listing ...

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Gregg,

    1) On my open statement
    2) Runtime only
    3) My CursorType being forwardonly is perfectly fine ...

    I'm wondering if it has to do with my using ADO to attempt to retrieve the internal table listing ...
    But you said it worked fine before. Were you using DAO before?

    Gregg

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problems with accessing MSysObjects ...

    Originally posted by basicmek
    But you said it worked fine before. Were you using DAO before?

    Gregg
    Gregg,

    Nope strictly ADO. I don't even have the DAO library referenced ...

    That's the thing ... This code has not changed. As it is printed ,it ran. Now? Not ... I just got done taking the SELECT and running it thru the QBE with no problems ... Interesting ... I'm trying to figure out why ...

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Gregg,

    Nope strictly ADO. I don't even have the DAO library referenced ...

    That's the thing ... This code has not changed. As it is printed ,it ran. Now? Not ... I just got done taking the SELECT and running it thru the QBE with no problems ... Interesting ... I'm trying to figure out why ...
    What fun would it be if everything stayed the same and was predictable?

    Just seems like something in your operating environment must have changed....

    Gregg

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Ok ... This worked at one time ... when I initially developed it and it has not changed code-wise ... Any ideas on why I get the following error?

    Code:
    Function RemovePreviousTemporaryFiles() As Integer
        On Error GoTo Err_RPTF
        
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
    'SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
    '(Left$([Name],4) <> "Msys") AND (Right$([Name],8) <> "Template")  AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
        SQLString = "SELECT MSysObjects.Name FROM MsysObjects"
        SQLString = SQLString & " WHERE ((Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (Right$([Name],8) <> 'Template') AND (MSysObjects.Type)=1) ORDER BY MSysObjects.Name;"
        TrgRecSet.Open SQLString, FormsConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                If Right(TrgRecSet.Fields(0).Value & "", 6) = CurrentUser() Then
                    DoCmd.SetWarnings False
                    DoCmd.DeleteObject acTable, TrgRecSet.Fields(0).Value
                End If
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        RemovePreviousTemporaryFiles = 1
    Exit_RPTF:
        Exit Function
        
    Err_RPTF:
        
        MsgBox Err.Number & ": " & Err.Description
        RemovePreviousTemporaryFiles = 0
        Resume Exit_RPTF
    End Function
    Error Msg:
    Did you intend to not declare the string variable SQLString in the function?
    The only reason I ask is that I was going to run your code in a database of my own and upon compile it barked at the variable not declared. Of course, I have the Option Explicit statement automatically inserted in my modules. Probably nothing....

    In addition, I was able to run your code without problems on my machine. I simply substituted the DSN for CurrentProject.Connection

    Don't know...

    Gregg
    Last edited by basicmek; 02-19-04 at 13:20.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problems with accessing MSysObjects ...

    Originally posted by basicmek
    Did you intend to not declare the string variable SQLString in the function?
    The only reason I ask is that I was going to run your code in a database of my own and upon compile it barked at the variable not declared. Of course, I have the Option Explicit statement automatically inserted in my modules. Probably nothing....

    In addition, I was able to run your code without problems on my machine. I simply substituted the DSN for CurrentProject.Connection

    Don't know...

    Gregg
    Gregg,

    SQLString is a form-level variable ... I'll try that CurrentProject.Connection also ... Thanks!

  9. #9
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Gregg,

    SQLString is a form-level variable ... I'll try that CurrentProject.Connection also ... Thanks!
    I've seen this No Read Permission error before many times... The only time the error was a legitimate (ie not corruption of some kind) reason is when I was working on security...

    If the database is joined to a separate .mdw file, other than System.mdw, I'd check that you haven't somehow lost that join... I'd look around in security... See that you're signed in as the right name and that no changes have been made to the permissions on these objects...

    And when you've exhausted all security possibilities... get ready to deal with corruption... lol...

    Do a compact and repair... Try importing all objects and relationships to a blank database and see if you get the error running it from there...

    I don't think this is a problem with your code at all...

    HTH

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problems with accessing MSysObjects ...

    Originally posted by Trudi
    I've seen this No Read Permission error before many times... The only time the error was a legitimate (ie not corruption of some kind) reason is when I was working on security...

    If the database is joined to a separate .mdw file, other than System.mdw, I'd check that you haven't somehow lost that join... I'd look around in security... See that you're signed in as the right name and that no changes have been made to the permissions on these objects...

    And when you've exhausted all security possibilities... get ready to deal with corruption... lol...

    Do a compact and repair... Try importing all objects and relationships to a blank database and see if you get the error running it from there...

    I don't think this is a problem with your code at all...

    HTH
    Trudi,

    Actually it was ... For whatever reason, going thru ODBC using ADO "hides" (for lack of a better word) the MSysObjects table ... If I do what Gregg suggested and use the CurrentProject.Connection in place of the FormsConnect connection my ADO code works just fine. I just did that ... BINGO!

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Trudi,

    Actually it was ... For whatever reason, going thru ODBC using ADO "hides" (for lack of a better word) the MSysObjects table ... If I do what Gregg suggested and use the CurrentProject.Connection in place of the FormsConnect connection my ADO code works just fine. I just did that ... BINGO!
    Well that's cool... Learned something new... But what I don't get is that you didn't have the problem before... Why all of a sudden?

    Oh well... What would my day be like without a few things that make me go hmmmmmm?? lol

    Glad it's worked out for you...

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problems with accessing MSysObjects ...

    Originally posted by Trudi
    Well that's cool... Learned something new... But what I don't get is that you didn't have the problem before... Why all of a sudden?

    Oh well... What would my day be like without a few things that make me go hmmmmmm?? lol

    Glad it's worked out for you...
    Yes I know ... Now All I gotta figure out is WHY my DB keeps corrupting on the network ... Ran great for 3 weeks straight and then *WHAP* 3 times down Tues, and once yesterday and once today (for now) ... And it running on a 2000 workstation forcing a restriction of a max of 10 user connections ... But I digress ...

  13. #13
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Yes I know ... Now All I gotta figure out is WHY my DB keeps corrupting on the network ... Ran great for 3 weeks straight and then *WHAP* 3 times down Tues, and once yesterday and once today (for now) ... And it running on a 2000 workstation forcing a restriction of a max of 10 user connections ... But I digress ...
    This may not help, but I just thought I'd search around a bit on that error number you were getting. The information here seemed appropriate and may help.

    http://msdn.microsoft.com/library/de...idererrors.asp

    Gregg

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Problems with accessing MSysObjects ...

    Originally posted by basicmek
    This may not help, but I just thought I'd search around a bit on that error number you were getting. The information here seemed appropriate and may help.

    http://msdn.microsoft.com/library/de...idererrors.asp

    Gregg
    Gregg,

    That is useful info but, in my instance the database HAS corrupted ... The next poor shmuck who tries to get in gets the dreaded "This may not be an access database file or the database may be corrupt." msgbox pop up ... Then my fun (and ridicule from the end-users) begins ...

  15. #15
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Problems with accessing MSysObjects ...

    Originally posted by M Owen
    Gregg,

    That is useful info but, in my instance the database HAS corrupted ... The next poor shmuck who tries to get in gets the dreaded "This may not be an access database file or the database may be corrupt." msgbox pop up ... Then my fun (and ridicule from the end-users) begins ...
    Sorry about that. I can appreciate your pain. I'll keep my eyes open for any useful info. I've got a ton of reference material for Access developing and the latest versions cover ADO pretty thoroughly.

    Gregg

Posting Permissions

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