CODEWITHSUNDEEP
ms-access
Christine
Christine

Reputation: 640

Memory Exceeded Error in MS Access

I have the following procedure that I run (in an MS Access module) on a regular basis to switch the linked table connection strings from Test to Production and vice-versa:

Public Function TableRelink()
    Dim db As Database
    Dim strConnect As String
    Dim rs As Recordset
    Dim tdf As TableDef
    strConnect = "ODBC;DRIVER=SQL Server;SERVER=MYTESTSERVER;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=MyDB;"
    'strConnect = "ODBC;DRIVER=SQL Server;SERVER=MYLIVESERVER;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=MyDB;"
    CurrentDb.TableDefs.Refresh
    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> "" Then
            tdf.Connect = strConnect
            tdf.RefreshLink
        End If
    Next
    MsgBox ("Done!")
End Function

The above has been working for months and months. About a week ago, the following error randomly popped up. Then, after a few minutes, without any intervention on my part, it would allow me to run the procedure again. Today, the error has come back.

Run-time error '3035': System resource exceeded.

It is thrown on this line: tdf.RefreshLink

I did a Google search and found an article out there talking about a hotfix (that wouldn't install on my machine), and another about editing a registry value (which didn't seem fix it). As I type this, the error has stopped popping up and I can again re-link my tables, so at this point, I can't really do any more troubleshooting. I was reading another SO post talking about the lock file, but couldn't really make heads or tails of the accepted answer, and I'm not really convinced it has anything to do with my particular scenario. Does anyone know what might be causing this and/or what can be done to prevent it?

For reference, I'm running Office 365 ProPlus on a Win10 64-bit machine.

Upvotes: 0

Views: 331

Answers (1)

Gustav
Gustav

Reputation: 55921

First, do use your db object:

Public Function TableRelink()
    Dim db As Database
    Dim strConnect As String
    Dim rs As Recordset
    Dim tdf As TableDef

    strConnect = "ODBC;DRIVER=SQL Server;SERVER=MYTESTSERVER;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=MyDB;"
    'strConnect = "ODBC;DRIVER=SQL Server;SERVER=MYLIVESERVER;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=MyDB;"

    Set db = CurrentDb
    db.TableDefs.Refresh
    For Each tdf In db.TableDefs
        If tdf.Connect <> "" Then
            tdf.Connect = strConnect
            tdf.RefreshLink
        End If
    Next
    MsgBox ("Done!")

    Set td = Nothing
    Set db = Nothing

End Function

However, another and a much faster method is to have both sets of tables linked permanently and then rename them for switching the database.

For example, to switch from Production to Test:

Table1 -> Table1_p
Table2 -> Table2_p
...
Table1_t -> Table1
Table2_t -> Table2

Of course, if you modify a table schema, you must relink as usual.

Upvotes: 0

Related Questions