OpenCurrentDatabase closes on End Sub

Question

Greetings. 

So I am running OpenAccessDatabase command in a module, and it works. The database opens fine. The problem is that when the subroutine ends, the database closes! I had to put this in a module and step through it to figure out why it wasn't working. I stripped it down to simply the code at the end here, and the database opens, then closes.  

I tried using "Exit Sub" as well, just on a whim, but the result is the same. I've tried punchin it, I've tried yellin at it, nothing works!

Any advice would be appreciated and may prevent a nervous breakdown. Thanks. 

Here is the basic code:

Private Sub Button_Click()
Dim AccApp As New Access.Application
AccApp.OpenCurrentDatabase ("C:\FolderName\FileName.accdb")
End Sub

Answer 1

Hi,

I think it's a matter of "scope." Since your variable is declared within the Sub, it's life is limited to the life of the Sub. So, as soon as the Sub is done, the variable gets destroyed - and the app closes.

To keep it persistent, you'll need a wider scope for your variable. For example, you can try declaring it outside of your Sub to make it a module level variable. You may have to declare it as Public to be available to other modules, if you need it to be.

Hope it helps...

Answer 2

Oh!!!! That makes total sense....now :) 

Not in the office now but will try again tomorrow. Many thanks!

Answer 3

Hi,

You're welcome. Hope it works out for you. Let us know how it goes. Cheers!