System resource exceeded – what resource could that be

microsoft accessodbc

I'm trying to run an asp.net page that connects to an access database via an ODBC Data Source. I'm using the Microsoft Access ODBC Driver. Everything was working fine until a recent power outage caused us to have to reboot the server. The problem is, whenever I try to open a connection (not run a query or anything, just open the connection), I get the rather cryptic error message System resource exceeded. The server runs Windows Server 2003 (standard, service pack 2). it has 7.99 gigs of hard drive space free. it seems to have about 600 megs of ram free. I obviously can't think of any other applications trying to connect to this DSN.

Best Answer

The three most likely causes for the "System Resource Exceeded" error on an ODBC Data Source are:

Memory allocation failure- to resolve this particular issue, you can:
- check what memory limit is set for your SQL/ODBC database application and Virtual Memory; while the server itself may have plenty of RAM available, the VM or application may be hitting a ceiling.
- if the server itself is in fact utilizing almost all of available memory, you can add more memory to the system.
- enable/utilize AWE Memory on Windows Server (see https://www.sqlservercentral.com/Forums/FindPost32620.aspx); standard 32-bit addresses can map a maximum of 4 GB of memory- enabling AWE will extend that up to 8GB or more.
A disk space (folder or file size limit) issue - remediation actions include:
- check the available disk space for temp files in the server settings.
- check if Windows Server/NT event log is full; if it is you may want to prune it and update the log settings.
Connection method: your current setup might be creating new connections instead of reusing existing ones, thus maxing out the number of connections. To resolve:
- turn on "Connection Pooling" in the ODBC Administrator for the driver being used. Refer to https://support.microsoft.com/en-us/help/164221/info-how-to-enable-connection-pooling-in-an-odbc-application

On a related note, but only when a query is performed- MS Access 2010 handles looping through large tables differently than Access 2003. See https://social.msdn.microsoft.com/Forums/office/en-US/eda4991f-ac30-4e98-87ba-8d528ae7cfde/system-resources-exceeded-ms-access-2010?forum=accessdev. There are patches/service packs available for Office and Windows Server that may remedy some of these issues as noted in this KB post: https://support.microsoft.com/en-us/help/2726928/-system-resource-exceeded-error-message-when-you-perform-a-query-in-ac

Related Solutions

Windows – create an oracle ODBC driver without installing the Oracle Client

Use the oracle instant client. You just need to unzip and if you want to use tnsnames.ora set the TNS_ADMIN environment variable.

Using remote MS Access database which connects to remote SQL server

You can eliminate the need for the local DSN on each machine by using a bit of code in the Access database. You do of course still need the appropriate ODBC driver installed but I expect the one you need is probably standard on Windows anyway.

A local DSN will be required to initially create the database.

The following in what I use for dynamically relinking to a MySQL database, so you will need to edit it accordingly. The code is called from the AutoExec macro or can be run manually or from a form.

Note that this is not my code but I've been using it for a very long time and don't recall where I originally got it. All I've done is edit it to suit my requirements.

Option Compare Database

Public Function ReLinkTables()

Dim dbPUBS As DAO.Database
Dim tdfPUBS As DAO.TableDef
Dim strTable As String
Dim strConnect As String
Dim InFile As Integer


' Set the following variables tosuit your DB connection
Dim Server As String
Dim Database As String
Dim User As String
Dim Password As String

On Error GoTo 0
Set dbPUBS = Nothing
Set dbPUBS = CurrentDb
strConnect = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=" & Server & ";" _
    & "DATABASE=" & Database & ";" _
    & "UID=" & User & ";" _
    & "PWD=" & Password & ";" _
    & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

' Refresh Access linked tables
For Each tdfPUBS In dbPUBS.tabledefs
    ' Only attempt to refresh link on tables that already
    ' have a connect string (linked tables only)
    If Len(tdfPUBS.Connect) > 0 Then
        strTable = tdfPUBS.Name

        ' Set the tables connection string
        tdfPUBS.Connect = strConnect

        ' and refresh the link
        tdfPUBS.RefreshLink
    End If
Next

' Refresh Connect String for all Pass-Through Queries
'strMsg = "Refreshing links for all Pass Through Queries."
'DoCmd.Echo True, strMsg
'For Each qdfPUBS In dbPUBS.QueryDefs
    'If Len(tdfPUBS.Connect) > 0 Then
        'qdfPUBS.Connect = strConnect
    'End If
'Next

End Function

Best Answer

Related Solutions

Windows – create an oracle ODBC driver without installing the Oracle Client

Using remote MS Access database which connects to remote SQL server

Related Topic