MS Access 2016 program on some PCs has errors with Out Of Stack Space and Security Warnings.

Question

The program runs fine on my machine. When I try to run it on my friends' machines (either the .ACCDE or .ACCDB version) it won't load and pops Out Of Stack Space errors and the Security Notice instead. 

So, here's the set up:

---

The program was written in Access 2016. It is a Front End/Back End design. It's not a very big program 16 tables, 41 forms and 51 code modules. 

I use the FMS Access Analyzer to help make sure my code is clean so the quality of the program is good to very good.

PRIOR versions of the program ran fine on all machines. I made several changes, improvements and moved it to the \Documents folder. Now we are having problems.

---

Machine 'A' (Development PC): New Win 10, 8GB RAM, Full MS Access (not runtime). 

Machine 'B': Newish laptop 2GB RAM, lots of disk, Access 2016 Runtime. It ran prior versions of the program fine but now is blowing errors.

Machine 'C': Newish desktop 8GB RAM lots of free disk, full Access (not runtime). It also ran prior versions of the program fine but now is blowing errors.

---

Initally, the opening form would pop an error that the On Load event caused an Out Of Stack Space event. User says, 

"Still happens after a fresh reboot.  It does NOT happen with other .accde files." Both A and B machines are showing the same errors.

I made many changes but could not cure the Out Of Stack Space error. Finally, I went to an Autoexec Macro instead of a startup form. The autoexec macro that caused Error 3709 and aborted the macro. Machine B had CPU 49%, Mem 60%. The micro sd drive had 5.79GB used and 113GB free.

I deleted the macro. Went back to startup Form, still no luck. 

I asked if he got a MS Security error, he said, "Yes, Microsoft Access Security Notice.  Figuring just a general warning since it let's me go ahead and open the file. The directory where we have the program (C:Documents\Condor) was already a Trusted Location on my work machine."

---

So, does this sound like a Security error? 

Is it a problem to have the program in the \Documents folder?

Answer 1

Okay, the problem is solved. Thank you for your time and advice!

The tables were no longer linked and some code in the startup form literally blew its Stack looking for a data column.

Feel free to test this yourself and post it wherever it might do some good. And, remember, an Out Of Stack space error can be caused by unlinked tables.

So this is what I put in the Startup form's On Open event:

'‘ Procedure: RefreshTableLinks_MultiDB

'‘ Purpose: Refresh table links to one or more backends

'‘ 2018 by Kent Hartland. Feel free to use it

'‘—————————————————————————-

Public Function RefreshTableLinks_MultiDB() As Boolean

On Error GoTo PROC_LINK_ERR

Dim db As DAO.Database

Dim tdf As DAO.TableDef

Dim rst As DAO.Recordset

Dim strConDB1 As String

Dim strConGroupDatDB2 As String

Dim strTblName As String

'170     pstrDbPwd1 = "(Your first db password) UNCOMMENT this line if needed"

'170     pstrDbPwd2 = "(Your second db password) UNCOMMENT this line if needed"

180     Set db = CurrentDb

190     strSQL = "SELECT * FROM tbl_(Whatever);" ' tbl_(Whatever) is the last table in the tables list that should always have data in it (VERIFY THIS!) or create a dummy table for this with a record in it. So, record count not greater than 0 means it is empty or not linked

200             Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

210             With rst

220                 If .RecordCount > 0 Then

                        RefreshTableLinks = False ' no need to refresh links

230                     GoTo PROC_EXIT

240                 Else

                        RefreshTableLinks = True 'need to refresh links

250                     GoTo PROC_LINK_ERR

260                 End If

261             .Close

262             End With

PROC_RELINK:

On Error GoTo PROC_ERR ' notice that, inside the Relink procedure, we switch error handlers

263         MsgBox ("Cannot find (DB1). Click OK and use the File Explorer to locate '(DB1 name)'")

264         strConDB1 = GetOpenFile(pstrDBDrive, "Locate '(DB1 name)'") ' or add ",PASSWORD=" & pstrDBPwd1 if the database has a password

265         MsgBox ("Now click OK and use the File Explorer to locate '(DB2 name)'")

266         strConDB2 = GetOpenFile(pstrDBDrive, "Locate '(DB2 name)'")  ' or add ",PASSWORD=" & pstrDBPwd2 if the database has a password

267         For Each tdf In db.TableDefs

270               strTblName = tdf.Name

280            If Len(tdf.Connect) > 0 Then

290               Select Case strTblName 'make sure it is not a System table or local table...

                       Case "db1_tbl_1", "db1_tbl_2", "db1_tbl_5", 'etc, all the table on DB1

300                       tdf.Connect = ";DATABASE=" & strConDB1

310                      tdf.RefreshLink

320                   Case "db2_tbl_1", "db2_tbl_2", 'etc - all the tables on the second database

330                       tdf.Connect = ";DATABASE=" & strConDB2

340                       tdf.RefreshLink

350               End Select

360         End If

370                 On Error Resume Next

380         Next tdf

390         RefreshTableLinks_MultiDB = False '"false" meaning there is no more need to relink the tables now

400         GoTo PROC_EXIT

PROC_LINK_ERR:

410           Err = 0

420           GoTo Proc_ReLink

PROC_EXIT:

430         Exit Function

PROC_ERR:

440           pstrErrResult = FuncLogErr(Err.Number, Err.Description & " Line Number: " & Erl, "RefreshTableLinks_MultiDB")

End Function

Answer 2

We're still getting instant Out Of Stack errors with the stripped-down database. His References all check out okay.

Answer 3

Have not verufied the same builds nor checked references on the other two machines. 

To your first msg:

It compiles without errors. Yes, compiled, compacted,etc. repeatedly. All machines running 32 bit Access. All code has error handling but it doesn't get that far. Error blows before the start up form loads. I have created bland new db and imported all objects, no luck. 

I am in the process of trying to load a db with startup  form disabled, etc. to get something that will load then work backward and see where it breaks. 

Thank you very much for your time. As soon as I have more to report I will let you know.

Answer 4

Two more point:

• Have you made sure to update all the machine to ensure they are all running the exact same build?
• Have you verified your References on the problematic PCs?

Answer 5

I know you've answered so of this indéfectible above, but I'm just going to ask again to be 100% sure.

• Your code compiles without errors?
• Have you decompiled, compacted, compiled and compacted your DB?
• Are all computers running the same Office/Access bitness?
• So ask of your procedures include error handling?
• What happens if you debug the error? 
  • What does Access report as being the issue?
• You've created a trusted location for the FE on each computer?
• Have you tried creating a new blank database and importing everything to see if the problem remains?
• Disable all your code and try the DB, what happens? 
  • Then activate a bit at a time and figure out the source of the issue.