Access 2007 - QUIT cmd closes all databases with automation

[beachldy]

From one Access 2007 database, I'm using automation to open another Access 2007 database. I want the first database to close after the 2nd one opens. I cannot use followhyperlink or Shell command to open the 2nd database because it is password protected. Because it's automation, I cannot drop the app or set it as nothing or the 2nd instance will close out. So, meanwhile, when I try to close the 1st database and use the cmd "QUIT", it closes BOTH databases or only the 2nd database, which isn't what I need. HELP! I'm pulling my hair out since Access 2007 came out....Simple things have changed.

See code below:
------------------------------------------
Function GetDB()
Dim DBPWD1 As String
On Error Resume Next
DoCmd.Hourglass True
DBPWD1 = "12345"
Dim dbs As Database
Dim dbs2 As Database

dbpath = "C:\RBHS\RBHS.mde"

Static Acc As Access.Application
strdbname = dbpath

Set Acc = New Access.Application
Acc.Visible = True

Set dbs = Acc.DBEngine.OpenDatabase(strdbname, False, False, ";PWD=" & DBPWD1 & "")
Acc.OpenCurrentDatabase strdbname
Acc.RunCommand acCmdAppMaximize

'THE ITEMS BELOW ARE COMMENTED OUT BECAUSE THEY CAUSE THE
'2nd DB TO CLOSE
'dbs.close
'Set dbs = Nothing
'Acc.Quit
'Set Acc = Nothing

'THE BELOW DOES NOT WORK BECAUSE IT CLOSES ALL INSTANCES,
'BOTH DATABASE 1 AND DATABASE 2
strdb = "DatabaseOne"
Set AppAccess = GetObject(strDB, "Access.Application")
AppAccess.Quit

'BELOW DOENS'T WORK EITHER
DoCmd.quit

End Function
---------------------------------------

 

[boblarson]

Actually, my info is off.

You need to use the Access object and not the db2 object. The db2 object only deals with the internal workings of the database and not the Access shell. The Access object deals with the outside parts.

So, you need to open db2 using the Access object, not the DAO object. Then you can release it by using

Acc.UserControl = True

 

[beachldy]

So you're saying to do this?:

strdb = "DatabaseOne.mde"
Set AppAccess = GetObject(strDB, "Access.Application")
AppAccess.UserControl = True
AppAccess.Quit

(which would mean the command .userControl sort of sets the focus back to the 1st database?)

 

[boblarson]

So you're saying to do this?:

strdb = "DatabaseOne.mde"
Set AppAccess = GetObject(strDB, "Access.Application")
AppAccess.UserControl = True
AppAccess.Quit

(which would mean the command .userControl sort of sets the focus back to the 1st database?)

Are you wanting the 2nd database to remain open when closing the first?

 

[boblarson]

If so, then you need to use this:

Dim DBPWD1 As String
Dim dbPath As String
Dim accApp As Access.Application

DBPWD1 = "12345"


dbpath = "C:\RBHS\RBHS.mde"


Set accApp = New Access.Application

'opens the database
appAcc.OpenCurrentDatabase strdbname, False, DBPWD1

' releases the database
appAcc.UserControl = True

' closes the application this code is in.
Application.Quit

 

[boblarson]

NO, I'm wanting to close out the 1st database entirely when they open the 2nd instance.

That's what my code just above your post here does.

 

[beachldy]

Cool...I'll try that in a second.....

 

[boblarson]

Oh, and just so you know -

It is very RARE that you should use

On Error Resume Next

You should instead have an error handler which does report back errors. Resume Next just leaves things open to have nightmarish things occur and you (or your users) not know about them.

 

[beachldy]

OMG, Bob, you are amazing!!!!!! It surely worked like a charm!!! I adjusted some code to fit my variables. Some new commands I must learn, huh with 2007?

 

[boblarson]

Glad to see it worked for you.

 

[beachldy]

Another question for you, what's the best method in 2007 to send emails? I've been using Redemption.dll but many emails don't get through...(another 2007 problem, lol)

 

[boblarson]

I think you'll need to start another thread on that one. I haven't done much email coding in a long time (been stuck here using Lotus Notes - argh, sputter, gasp).