[Access 2003] Cannot open any more tables

[Ecniv]

Hi,

I'm getting the above message in Access when hovering over table links.

Once it appears I can not
- Save
- open connections to other dbs
- open tables/queries


Have MS 'upgraded' recently and messed up the memory/open/closing of tables?

Is there a fix (apart from closing Access)?

Any useful links...?

[Siddharth Rout]

Are you closing all the connections correctly?

Can I see some code?

Also download and install the Jet 3.5 Update: Download Jet35Upd.Exe (SP3) Check google for the same...

[Ecniv]

Hiya,

Well thats the scary part. I am not running any code. The hovering the mouse is on the main database window in MS Access.

Hence the wondering about a 'fix' from MS... maybe there was one and it broke it. Again.

[Siddharth Rout]

Did you try downloading and installing the link mentioned above?

[Ecniv]

Thanks for the link - unfortunately I do not have admin rights to install anything on here. There was an sp 2003 upgrade earlier in the month... hmmm.

I shall try to d/l and run it though incase it works

[Ecniv]

D/led - ran... seemed to install (at least no errors)

Opened access, trundled through the pop up of the linked table path... after 30 or so...

[Siddharth Rout]

can you upload your table?

[Ecniv]

Sorry nope. I tried a db repair n fix but that didn;t do anything. Its after I've checked several of the tables links. Once its up no table opens, and you cannot do anything with the db (including save). Looks like Access is opening a table link to get the info but is not closing afterwards... sigh.

[Siddharth Rout]

It doesn't give you any error number?

[Ecniv]

not on the main window.

in code:
3420 Object invalid or no longer set.

from

Set rst = DBEngine(0)(0).OpenRecordset(strSql, , dbOpenSnapshot)

[Siddharth Rout]

not on the main window.

in code:
3420 Object invalid or no longer set.

from

Set rst = DBEngine(0)(0).OpenRecordset(strSql, , dbOpenSnapshot)

As I mentioned in post 1, can I see the rest of the code? Are you closing the connection correctly?

[Ecniv]

Kool : As I posted... :

Well thats the scary part. I am not running any code. The hovering the mouse is on the main database window in MS Access.

As in, the window with the table list.

[Siddharth Rout]

Yeah you are not, but the code is there as per post 11. i need to check that complete code....

[Ecniv]

post #11 was because you wanted an error message. That comes if I try to run anything once (and only after) the cannot open tables message appears. before that if I were to run anything it would work fine.

What I was trying to find is why if I am only moving the pointer over table links to see where they are linked to Access complains. It has nothing to do with my code because I have not run it (I held shift as I opened it - 58 tables later... msg).

Its probably a bug that ms introduced to boost us up to 2007... but that may be a more sinacle/copnspiracy theorist view

Does that help?

If you want the code in that particular sub I can post it...

[Siddharth Rout]

Check this link ...

see if there is something which is conflicting?

If you want the code in that particular sub I can post it...

If you please... I don't want to leave any stone unturned

[Ecniv]

*Coulda sworn I posted back... ok...

I'll post the whole module (its to do with filling listboxes) however I think if I get the error message then even Select * from table won't work....
Sub in question is : CListSBUserSections
* Can't post - its too long in text hehe

    Dim rst As DAO.Recordset
    Dim lngLoop As Long
    Dim strSql As String
    Dim lngR As Long, lngStart As Long, lngMR As Long
    
    On Error Resume Next
    
    CMlngMaxCols = 2
    
    strSql = "SELECT" & vbCrLf
    
    strSql = strSql & " tblLUUserSections.UserSectionID, tblLUUserSections.UserSection, sq.CountOfScreenDescription as Totalscrs"
    strSql = strSql & " FROM tblLUUserSections LEFT JOIN"
    
    strSql = strSql & " (SELECT" & vbCrLf
    strSql = strSql & " tblLUUserSections.UserSectionID, Count(tblLUUserScreens.ScreenDescription) AS CountOfScreenDescription" & vbCrLf
    strSql = strSql & " FROM " & vbCrLf
    If Not (blnModeDev Or ublnIsDev) Then
        strSql = strSql & "(" & vbCrLf
    End If
    strSql = strSql & " tblLUUserSections" & vbCrLf
    strSql = strSql & " INNER JOIN tblLUUserScreens ON tblLUUserSections.UserSectionID = tblLUUserScreens.SectionID" & vbCrLf
    
    If Not (blnModeDev Or ublnIsDev) Then
        strSql = strSql & " ) inner JOIN tblLUUserToScreens ON tblLUUserScreens.ScreenID = tblLUUserToScreens.ScreenID" & vbCrLf
        strSql = strSql & " where tblLUUserToScreens.UserId = " & ulngUserID & vbCrLf
    End If
    
    'v2.0.4
    strSql = strSql & IIf(Not (blnModeDev Or ublnIsDev), " AND", " WHERE")
    strSql = strSql & " tblLUUserScreens.hidefromsb=false" & vbCrLf
    
    strSql = strSql & " GROUP BY tblLUUserSections.UserSectionID" & vbCrLf
    strSql = strSql & " ) AS sq ON tblLUUserSections.UserSectionID = sq.UserSectionID"
    strSql = strSql & " ORDER BY tblLUUserSections.UserSection;"
    
    

    Set rst = DBEngine(0)(0).OpenRecordset(strSql, , dbOpenSnapshot)
    
    If rst.EOF Then
        CMlngMaxRows = 1 + IIf(CMblnUseHeader, 1, 0)
        ReDim CMaryOfData(CMlngMaxRows, CMlngMaxCols)
        
    Else
        rst.MoveLast
        rst.MoveFirst
        lngMR = rst.RecordCount
        ReDim CMaryOfData(lngMR, CMlngMaxCols)
        
    End If
    
    lngStart = 0
    If CMblnUseHeader Then
        CMaryOfData(0, 0) = "Section ID"
        CMaryOfData(0, 1) = "Section"
        
        lngStart = 1
    End If
    
    If rst.EOF Then
        CMaryOfData(lngStart, 0) = "-1"
        CMaryOfData(lngStart, 1) = "No Records to show"
    Else
        
'v2.0.9
'   changed the view - only shows those with screens
        CMlngMaxRows = lngStart
        For lngR = 0 To lngMR - 1
            
            If Nz(rst("Totalscrs"), 0) > 0 Then
                CMaryOfData(CMlngMaxRows, 0) = Nz(rst("usersectionID"), -1)
                CMaryOfData(CMlngMaxRows, 1) = Nz(rst("usersection"), "") & " (" & Nz(rst("Totalscrs"), 0) & ")"
                CMlngMaxRows = CMlngMaxRows + 1
            End If
            
            rst.MoveNext
        Next
    End If
    rst.Close
    
    Set rst = Nothing
    
    ReDim CMaryOfWidths(CMlngMaxCols)
    For lngLoop = 0 To CMlngMaxCols
        CMaryOfWidths(lngLoop) = 1250
    Next
    CMaryOfWidths(0) = 0
    
    If Err.Number <> 0 Then
        MsgBox "Error filling list box (SB User Sections)" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation
        Err.Clear
    End If