Log in

View Full Version : VBA opens Access which opens a Second Access causes Freeze (?BUG?).



n00b33
08-12-2015, 03:47 PM
We have Windows 7 Pro with Microsoft Office Plus 10.

In Excel:
Sub try()
Dim oDB As Access.Application
Dim my_path$
my_path = "C:\Database14.accdb"
Set oDB = GetObject(my_path)
Set oDB = Nothing
End Sub

Database14.accdb has "Form1" selected as the Startup Property "Display Form"
option. Form1 has the following Load() event.
Private Sub Form_Load()
Dim SomeApp As Access.Application '(1)
'Dim SomeApp As Excel.Application '(2)

MsgBox "loading"
Set SomeApp = CreateObject("Access.Application") '(3)
'Set SomeApp = CreateObject("Excel.Application") '(4)
MsgBox "loaded"
Set SomeApp = Nothing
End Sub

When try() is executed:
-This works (does not freeze) if (1) and (3) are commented, (2) and (4) are
uncommented, and Database14.accdb is either already open or not.
-This works (does not freeze) on (3) if (1) and (3) are uncommented, (2) and
(4) are commented, and Database14.accdb is already open.
-This hangs/deadlocks/freezes on (3) if (1) and (3) are uncommented, (2) and
(4) are commented, and Database14.accdb is not already open. Why does it
freeze?

The closest search was regarding Dynamic Data Exchange (DDE) Deadlocking
https://support.microsoft.com/en-us/kb/118468
"DDE Deadlocking
Consider a situation in which a Visual Basic macro calls an application that
is waiting for a second application to get some data. If the macro does not
give control to the second application, the result is a deadlock. In DDE
conversations between multiple applications, using DoEvents removes the
possibility of this type of deadlocking."

I put DoEvents in several places (non-of-which made logical sense to me),
but with no luck. Also, the DDE Deadlocking doesn't explain why I can get
the code to work if if (1) and (3) are commented and (2) and (4) are
uncommented.

A workaround is to replace try()'s GetObject with
CreateObject/opencurrentdatabase.

If "Set oDB = GetObject(my_path)" is replaced with "Set oDB =
GetObject(my_path, "Access.Application")", then it does not freeze but will
always create a new instance (which is not desirable). VBA opens Access which opens a Second Access causes Freeze (?BUG?). As stated, we already
have a workaround, so this post's question is to understand *why* it is
freezing rather than figuring out some alternative solution.

Why does this hang/deadlock/freeze on (3) if (1) and (3) are uncommented , (2)
and (4) are commented, and Database14.accdb is not already open?

Thanks in advance.