Close another Access DB

[craigachan]

From time to time I have needed to open a second Access database to achieve the function I want. Example: to place a form on a second monitor (extended desktop). I don't know how to do this using a single instance of Access and am open to suggestions if you know how to open a form on a different monitor.

Anyways, back to my question. I now need to know how I can use VBA to close the second instance of Access.

Example:

AccDB1 opens AccDB2. I want to close AccDB2 from AccDB1 button.

Thanks for you help.

 

[boblarson]

If you create a global variable like:

Public objAcc As Object

And then set it to be the second database:

Function openAcc(strPath)

Set objAcc = CreateObject("Access.Application")
 
objAcc.OpenCurrentDatabase (strPath)
objAcc.Visible = True

End Function
 
 
Function closeAcc()
    objAcc.CloseCurrentDatabase
    objAcc.Application.Quit
    
    Set objAcc = Nothing

End Function

Then you can call the openAcc by using

openAcc "C:\Temp\SomeDatabase.accdb"

and then when you want to close it

closeAcc

 

[craigachan]

Thanks Bob, you're a great source of information.

I have a question about your code. When opening, the AccDB2 is declared in the OpenAcc(path to AccDB2). I'm clear about this.


When closing, how do I know that I won't close AccDB1. I guess I'm hung up about 'closeCurrentDatabase', Where in the code does it stated that it will be AccDB2?

Thanks in advance for your clarification.

 

[boblarson]

Thanks Bob, you're a great source of information.

I have a question about your code. When opening, the AccDB2 is declared in the OpenAcc(path to AccDB2). I'm clear about this.


When closing, how do I know that I won't close AccDB1. I guess I'm hung up about 'closeCurrentDatabase', Where in the code does it stated that it will be AccDB2?

Thanks in advance for your clarification.

Because the database 2 is bound to objAcc, not your first.

 

[craigachan]

Thanks for your explaination. I'll give it a try.

 

[craigachan]

Thanks again. It worked great. But then the next thing I run into is how do I get past this code if the user does not open AccDB2. How do I check to see if AccDB2 is open at all and ignore this close code if it is not?

 

[DCrake]

To test if the db is open do a Dir() in the known folder for ldb file. This is created when the db is opened. If it is there it's open, conversely, if not found it is closed.

 

[boblarson]

A simpler way is just to handle the error which occurs if you try to close it when it isn't open. The ldb file can be there and the database not be opened (if a user without correct permissions is the last out it can't delete it or if the database was closed with CTRL + ALT + DEL).

 

[craigachan]

As an amature programmer I trying to follow what this means, but am at a loss, sadly. Bob, Can you give me example code for this?

 

[boblarson]

On second thought just encapsulate the close code with this:

If Not objAcc Is Nothing Then

So the full function would be:

Function closeAcc()
[COLOR=red][B]If Not objAcc Is Nothing Then[/B][/COLOR]
    objAcc.CloseCurrentDatabase
    objAcc.Application.Quit
    
    Set objAcc = Nothing
[B][COLOR=red]End If[/COLOR][/B]
End Function

 

[craigachan]

Bob, you're awesome. You make everything look so simple. It worked great! Thanks again.

 

[craigachan]

Bob, Everything works good in the full version of ACC but now I'm having problems with Runtime. I get 429: ActiveX Can't create opbject. I tried to look up possible fixes but all of them seem be old fixes from 2-3 years ago. Any ideas for me?

I just got Acc 2010, but have not installed it yet. I'm running Acc 2007. Would this help fixing the problem?

 

[boblarson]

Not sure about that as I don't ever use the runtime. But, I think it may have to do with the fact that the RUNTIME is a restricted version and can't create things in Access. So by doing CreateObject("Access.Application") it would provide a workaround to someone not having Access (full-version) to create a database and I think MS might not like that.

 

[craigachan]

Thanks,

But didnt we use CreateObject("Access.Application") as you said. So are you saying that this should work? Or what else can I do?

 

[boblarson]

Thanks,

But didnt we use CreateObject("Access.Application") as you said. So are you saying that this should work? Or what else can I do?

Yes, we did use that. And I'm saying that in the Runtime version it may be that you can't do that for an Access Application.

As for what else you can do, I do not know. There is no other way I can think of to interact with another Access database the way you need to.

 

[craigachan]

Bob,

I'm able to open AccDB2 using Shell() or Launch(). Is there then a way to close AccDB2 once it is open?

 

[boblarson]

Bob,

I'm able to open AccDB2 using Shell() or Launch(). Is there then a way to close AccDB2 once it is open?

NOPE, that's why I used the other code.

 

[boblarson]

I guess there MAY be a way if you can get the 2nd database's Window Handle, but I have no clue about that.

 

[craigachan]

I'm not a programmer, but I do think about things alot. Here's just a thought.

You know the way you can close an application by right click -> Close on the bottom toolbar. Is this something that we can use? I'm not sure what what is involved with this code but perhaps something similar to that?

 

[craigachan]

Another thought.

I'm able to open a word application and then close it with GetObject(Word.appplication) and wordapp.quit. Is this something that we can use?