Results 1 to 9 of 9
  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45

    Database not responding

    I have a db which has been working ok for several weeks now.


    I needed a way of reporting the number of records in each of the 80+ user-defined tables in the db and found the following SQL on the net

    Code:
     SELECT MSysObjects.Name, DCount("*",[Name]) AS RecCountFROM MSysObjects
    WHERE (((MSysObjects.Name) Not Like 'MSYS*') AND ((MSysObjects.Type)=1));
    Ever since I've executed this code the db hangs with a "Not Responding" message and eventually crashes.

    I can't think of anything else that I have done to cause the crash.

    Could the code be responsible?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Take a backup.
    Open the database whilst also pressing the shift key to bypass any automated running code.

    See if you can see and open any of the tables.
    If you can then your database is probably okay, it is the code that is running on load that is causing the hang.
    Disable that and then debug the code to remove the offending bit (probably that query) and then try again.


    If that query hangs the db, you could try linking all the tables into a new blank db and running the queriy in the new "empty" database.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Assuming the advice Minty gave works for you, there is nothing in the query that would have caused the issue.
    However, there needs to be a space before the word FROM.

    If Minty's advice doesn't solve it, one other thing to try is decompiling to remove any corrupt compilation code.
    Once again, make a backup first.
    See http://www.fmsinc.com/microsoftacces.../decompile.asp for details.

    After decompiling, you should recompile then compact.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks Minty. I have a backup prior to when I added the offending SQL, which I've reverted to.
    It would be good to understand what was causing the problem though, so I started the db as you suggested and deleted the query that contained the offending SQL.
    I also had a form containing a button that ran the query and when I've tried to delete that, the db crashes. I can do other things such as delete other forms but not the one that used to link to the offending query. Do you think I could use a script to delete the form?

  5. #5
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks Colin, I'll give this a go.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Decompiling will probably fix your form. If not, open database with shift key down and delete.
    And if it still won't go, copying all objects except that form to a new database will definitely work.

    One more thing.
    Look in the VBE. Do you have any STRANGE objects starting with ~TMPCLP.
    if so, these are deleted objects that weren't cleared from memory because your database crashed and so are still in MSysObjects
    These could possibly be the cause of the issues when you ran that query.
    They can't be deleted in the usual way and you can't edit MSysObjects.
    To find out how to delete them and fix other issues in MSysObjects see this thread https://www.access-programmers.co.uk...d.php?t=293579
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks both for your help today.

    I will now just use the version of the db I had backed up before the problems started. However I do need a way of being able to find out the record count of the 80+ tables in the db. Is there a safer way of doing this than using the SQL that seems to have caused the problems?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by keith2511 View Post
    Thanks both for your help today.

    I will now just use the version of the db I had backed up before the problems started. However I do need a way of being able to find out the record count of the 80+ tables in the db. Is there a safer way of doing this than using the SQL that seems to have caused the problems?
    As I said before there is nothing in that query that would have triggered the issue.
    It is perfectly safe to query MSysObjects.
    Have you followed through all the steps listed in previous posts?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a sub that should help
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : GetRecordCounts
    ' Author    : Jack
    ' Date      : 11/04/2014
    ' Purpose   : Subprocedure to Loop through all tables in this database and
    ' provide Table Name, RecordCount and Current Date to Immediate window.
    ' Does not review system tables (MSys) or deleted (~TMPC)
    '---------------------------------------------------------------------------------------
    '
    Sub GetRecordCounts()
              Dim db As DAO.Database
              Dim tbl As DAO.TableDef
              Dim SCNT As String
              Dim tblCount As Long
    10    On Error GoTo GetrecordCounts_Error
    
    20    Set db = CurrentDb
    30    For Each tbl In db.TableDefs
    40      If (Left(tbl.name, 4) <> "MSys") And Not (tbl.name Like "~TMPC*") Then
    50          SCNT = Format(CLng((tbl.RecordCount)), "0000")
    60          Debug.Print tbl.name & Space(35 - Len(tbl.name)) & Space(10 - Len(SCNT)) & SCNT & Space(3) & Date
    70          tblCount = tblCount + 1
    80      End If
    90    Next tbl
    100   Debug.Print "Total tables (non system) count :" & tblCount
    110   On Error GoTo 0
    120   Exit Sub
    
    GetrecordCounts_Error:
    
    130   MsgBox "Error " & Err.number & "  on line " & Erl & " (" & Err.Description & ") in procedure GetrecordCounts"
    End Sub
    Good luck

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Window Not Responding
    By GbJubes in forum Forms
    Replies: 2
    Last Post: 12-21-2015, 07:36 AM
  2. Database Not Responding
    By Subwind in forum Programming
    Replies: 8
    Last Post: 06-10-2014, 07:54 AM
  3. Access Not Responding
    By bigspace55 in forum Queries
    Replies: 1
    Last Post: 02-28-2013, 01:02 PM
  4. Access Not Responding
    By Phoenyxsgirl in forum Access
    Replies: 11
    Last Post: 11-25-2011, 11:40 AM
  5. Query Not Responding
    By Lady_Jane in forum Queries
    Replies: 9
    Last Post: 11-09-2010, 01:27 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums