MS Access 2016 - 'System Resource Exceeded' - Windows 10

The error you're getting could be caused by many different things. First, be sure that your installations of Office and Windows are fully up to date, and that you have applied any relevant hotfixes.

Next - is your database split into a backend (Tables only) and a Frontend (everything else)? If not, then the first order of business is to do that. Access has a splitter wizard, or you can do it yourself. After splitting, make copies of the FE and provide a copy to each user that needs it.

After splitting you may need to perform maintenance on your database. Make a backup of your database, then do this:

1. Compact the database
2. Compile your database - from the VBA Editor click debug - Compile. Fix any errors, and continue doing this until the menuitem is disabled.
3. Compact again.

You may also need to decompile your database. To do that, make a shortcut with this as the target:

"full path to msaccess.exe" /decompile

Run that, then select your database when Access opens. After if opens completely, run the 3 steps above again.

Finally, you may need to create a new, blank database and import everything to that new database.

in addition to what Scott said, I would add that these can be notoriously difficult to track down.  They can be due to environment, the DB itself, or bugs in Access.

 Also I'd add to look for a pattern; are you working with data at the time (i.e. filtering), opening/closing a form, etc.

 in regards to records, if your dealing with large record sets, you can be running out of locks, which is one possible cause for that message as well.

 At the startup of the app, do:

 dbEngine.SetOption dbMaxLocksPerFile , 100000

 and see if that helps at all.

Jim.

Keep us updated and let us know if anything has changed.

Some other things to check as well:

1. Drop to a command prompt and type SET.   Check for TMP and TEMP environment variables, and if defined that they point to valid drives and directories

2. Clear those directories out and also:

C:\Temp
C:\Windows\System32\Temp

 If TMP or TEMP does not point to them.  

In all the temp directories, you can safely delete all files.  If a file is being used, you will get a lock conflict ("file in use") and you can just skip it.

 When JET performs certain operations (performing a large transaction), it may go to disk for temp storage.   Temp files show up as ~JET0000.TMP

 Hopefully between that and the other things mentioned, that will clear up the problem for you.

  I think to I will lobby Microsoft to somehow log what Access is doing at the time this error is encountered.

Many applications today write to the application event log to give an indication of what was going on internally when something happens.

Jim.

<<Does setting an open recordset - Set rst=dbs.OpenRecordSet("....") - add to the number of tablesIDs?>>

 Yes.

<<Does - Set rst = frm.RecordsetClone - also add to the number of TablesIDs?>>

 Yes.

<<By coding - rst.close and then Set rst=nothing - deduct from the number of TablesIDs? >>

 Yes.

<<Additionally how to keep the number of TablesIDs to a minimum? >>

 Don't use SELECT * and close things where your done with them.

<<I use a lot of SQL in the Diary functions to set object rowsources; sql="SELECT ........" and then someobject.RowSource=sql. Can these be some of the culprits? >>

   Not really.   The only one that counts is the .RowSource   Just setting a string doesn't do anything.   But when you set that rowsource, then every field you reference carries a table ID.

 When you reset it, all the old references go away and the new references now count.

 It's like you closing and opening a recordset in code.

Jim.

Can see why you had problems, especially with the daily lists.  For the weekly, you have at least 100 table ID's, and for the daily, over 200.   Add onto that anything else the app is doing and you can see why it's running out of table ID's

 Diary/calendar to-do's are always tough in Access because it does not have a native grid control.  Typically a grid control has one record source, which you can then filter in various ways and control the layout considerably (i.e. make it look like a calendar).  Lacking that in Access, the tendency is always to use a lot of individual controls so you can have control over the layout.

 If it were me, I would have thought more along the lines of a grid control, like a single sub form in continuous view (or a data sheet) and using color to differentiate the days/weeks, and not worried about making it look like a calendar.  That's more though about knowing Access than anything.

  What your doing is certainly not wrong by any stretch and in another product (ie. VB.NET/Winforms), your setup would not be an issue.

 I know you've done a lot of work, but the only thing that is going to help is a refactoring of the form and eliminating all those separate controls.  At that point, you could probably merge it back into the main CRM app.

<<Any suggestions would be most welcome, including how to jump from the Diary app to the CRM app and display the client double clicked in the Diary.>>

 You have two choices:

1. Two instances of Access running, with windows API code to switch back and forth between the windows (I can help with that - pretty easy to do).

2. Single instance, shut down one DB, and open the other - not very user friendly.

#1 is the better choice by far.

Let's see what Scott thinks to.

Jim.

Rayshka,

 The code you need is in the article here:

http://www.experts-exchange.com/articles/2104/Avoiding-running-multiple-instances-of-an-application.html

 About half way down, look for the "And finally, the complete function:" and the AppAlreadyUp() procedure.

 Look for the

  ' Find the existing instance, switch to it, then close this instance

comment about half-way down in the procedure.

What your going to want to do is mark both apps by adding a Window property to each:

lngReturn = SetProp(Application.hWndAccessApp, "myApp", 1)

at start-up.

Then using the code noted above, you can switch back and forth between the two windows.

Jim.

Rayshka,

There are a number of ways to structure this, but here's one that should do what your need:

1. In the applications at startup, add:

lngReturn = SetProp(Application.hWndAccessApp, "MainApp1", 1)

and for the other:

lngReturn = SetProp(Application.hWndAccessApp, "MainApp2", 1)

 This "marks" the main Access window with a tag that we can find.

2. Now write a generic routine to see if the "mark" can be found:

Function AppIsLoaded(strAppMarkedAs as String) boolean

      AppIsLoaded = False

      lngHWnd = GetWindow(GetDesktopWindow(), GW_HWNDChild)

      Do While lngHWnd > 0
          If GetProp(lngHWnd, strAppMarkedAs) = 1 Then
              AppIsLoaded = True
              lngHWnd = 0
          Else
            lngHWnd = GetWindow(lngHWnd, GW_HWNDNEXT)
          End If
      Loop

End Function

3. Now write a procedure to call when you want the other app started, and switch to it:

Sub SwitchToAppMarkedAs(strAppMarkedAs as String)

   Dim oAccessApp as Object
   Dim bolFound as Booleen

   ' Find if the app is loaded, and if not, start it
   If AppIsLoaded() = False then
       ' Start the application - we can use shell or automation.  I'll use automation
      Set oAccessApp = CreateObject("Access.Application")
      oAccessApp.UserControl = True
      oAccessApp.OpenCurrentDatabase("<path to db>")

      ' Optionally run a macro or do something else here.
      ' oAccessApp.Run "<macro name>"
    End If

   ' Now switch to the app.
      ' Find the existing instance and switch to it
      bolFound = False
     
      Do While bolFound = False

        lngHWnd = GetWindow(GetDesktopWindow(), GW_HWNDChild)

        Do While lngHWnd > 0
            If GetProp(lngHWnd, strAppMarkedAs) = 1 Then
              BringWindowToTop (lngHWnd)
              lngReturn = ShowWindow(lngHWnd, 3)
              bolFound = True
              lngHWnd = 0
           Else
             lngHWnd = GetWindow(lngHWnd, GW_HWNDNEXT)
           End If
        Loop

      DoEvents

      Loop

Exit_Procedure:

    Set oAccessApp = Nothing

End Sub

That should do it for you.   You now should be able to switch back and forth between apps, and if the one your switching to is not started, it will be started.

 One note: as the app starts up, you may not want to set the window property until your startup is complete.  That way, you won't switch to the other until it's ready.

  However you may also want to do it right away so the user can see the app starting instead of sitting there waiting with nothing happening.

Jim.