Quit/Close External Database..

[cimathers]

Hello all,

I'm having an issue with quitting/closing an external database.



Scenario:

1. DB1 goes through some version checking
2. DB1 is the wrong version
3. DB1 calls DB2
4. DB2 tries to Quit/Close DB1 <-- Fails here
5. DB2 Deletes DB1
6. DB2 Copies new version
7. DB2 opens new version
8. DB1(new version) Quits/Closes DB2
9. User uses new version

I can simulate item 4 by manually openning DB1 and testing the code in DB2 to close it. And, it works fine. There seems to be an issuing when trying to close DB1 after openning DB2 from DB1.

Code is as below..

DB1 code:

Private Sub cmdTest_Click()
 
Dim anapp As Access.Application
 
SetEnvVar "zSSTPATHz", CurrentDb.Name
 
Set anapp = CreateObject("Access.Application")
anapp.OpenCurrentDatabase ("Path to DB2...")
Set anapp = Nothing
 
End Sub

DB2 Code:

Private Sub Form_Load()
 
Dim bCopy As Boolean
 
' check if auto copy is required
If MsgBox("About to copy new version across:" & vbCrLf & "Do you want to carry on?", vbYesNo + vbCritical, "Your version of SST is out of date") = vbNo Then
    Application.Quit
End If
 
' get path to user sst file, close it and delete it
sDestination = GetEnvVar("Destination Path Name...")
CloseTheOpenDB (sDestination)
' wait 3 seconds for lockfile to close
doSleep 10
Kill sDestination
 
' state mde file name and copy new version to user sst path
sMDE = "aTest.mde"
bCopy = doCopy
 
' deal with copy
If bCopy = False Then
    MsgBox "Copying of 'SST' file not carried out." & vbCrLf & "Please copy file manually.", _
            vbCritical + vbOKOnly, "Error: Copying File"
Else
 
    ' set up environment variables for copied sst database
    SetEnvVar "Destination Path Name", "copied"
    SetEnvVar "Source Path Name", CurrentDb.Name
    ' open new instance of DB1
    OpenNewSST
 
End If
Application.Quit
End Sub

Subs and Functions:

Option Compare Database
 
 
Sub doSleep(iSeconds As Integer)
 
WaitUntil = Now + TimeValue("00:00:" & iSeconds)
Do
   DoEvents
Loop Until Now >= WaitUntil
 
End Sub
 
 
Function GetRoleName() As String
 
Dim spText() As String
 
spText() = Split(sDestination, "\")
GetRoleName = spText(5)
 
End Function
 
 
Sub CloseTheOpenDB(sDBPath As String)
 
Dim myObj As Object
 
Set myObj = GetObject(sDBPath)
myObj.Quit acQuitSaveNone
Set myObj = Nothing
 
End Sub
 
 
Function doCopy() As Boolean
 
Dim sTemp As String
 
PUID = Environ("username")
sRoot = "\\..." & PUID
sPersonal = sRoot & GetRoleName & "\"
sGroup = sRoot & "..."
sSource = sGroup & "Path details..." & sMDE
 
On Error GoTo EH_doCopy
FileCopy sSource, sDestination
 
doCopy = True
 
Exit Function
 
EH_doCopy:
' copy has failed
MsgBox "Error Num: " & Err.Number & vbCrLf & Err.Description
doCopy = False
 
End Function
 
 
Sub OpenNewSST()
 
Dim anapp As Access.Application
 
Set anapp = CreateObject("Access.Application")
anapp.OpenCurrentDatabase (sDestination)
Set anapp = Nothing
 
End Sub

 

[DJkarl]

Instead of using an automation object in DB1 to open DB2 use the Shell command

Shell "FilePathAndName"

 

[JohnLee]

Hi,

I have two databases one is my main database and the other is my Questionnaire database, I have a button on my main switchboard that calls a macro named mcrOpenQuesDB and the macro calls this code:

[COLOR=blue]Function[/COLOR] mcrOpenQuesDB()
[COLOR=blue]On Error Resume Next[/COLOR]
[COLOR=#0000ff][/COLOR] 
DoCmd.Echo [COLOR=blue]False[/COLOR], "Running Open Questionnaire  Program"
DoCmd.Hourglass [COLOR=blue]True[/COLOR] [COLOR=green]'Turn on the Hour Glass
[/COLOR]DoCmd.SetWarnings [COLOR=blue]False[/COLOR] [COLOR=green]'Turn Off the Warnings[/COLOR]
 
[COLOR=green]'Open Questionnaire Database
[/COLOR]intX = Shell("C:\Program Files\Microsoft Office\Office\msaccess.exe G:\Questionnaires.mdb", vbMaximizedFocus)
 
DoCmd.Echo [COLOR=blue]True[/COLOR], ""
DoCmd.Hourglass [COLOR=blue]False[/COLOR] [COLOR=green]'Turn Off the Hour Glass
[/COLOR]DoCmd.SetWarnings [COLOR=blue]True[/COLOR] [COLOR=green]'Turn on the Warnings[/COLOR]
 
[COLOR=green]'Close this Database
[/COLOR]DoCmd.Quit
 
[COLOR=blue]End Function[/COLOR]

So on clicking the button on my main form the macro is called and it in turn calls the code. I tried placing the code directly in the main switchboard button, but there were problems which I could not resolve which is why I have it set up in this way and it works every time.

I have similar setup in my Questionnaire Database for when I want to move back to my main database.

Hope this helps.

John

 

[boblarson]

Instead of using an automation object in DB1 to open DB2 use the Shell command

Shell "FilePathAndName"

Sorry DJkarl, but that is an even worse idea than what they are doing now as that guarantees that it can't shut down the database.

The Db1 code needs to be modified as thus:

Private Sub cmdTest_Click()
 
Dim anapp As Access.Application
 
SetEnvVar "zSSTPATHz", CurrentDb.Name
 
Set anapp = CreateObject("Access.Application")
anapp.OpenCurrentDatabase ("Path to DB2...")
 
[B][COLOR=red]DoEvents[/COLOR][/B]
[B][COLOR=#ff0000]anapp.CloseCurrentDatabase[/COLOR][/B]
[B][COLOR=red]anapp.Quit[/COLOR][/B]
 
Set anapp = Nothing
 
 
 
End Sub

But you may need to use something to pause before doing that to give it enough time for Db2 to run its code.

 

[cimathers]

DJKarl and JohnLee, cheers for the hint and for the slight change in direction required.

Unfortunately, the 'Shell' command was blocked on my system but using the API version worked.

Here is the code I used:

Option Compare Database

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
                    ByVal hwnd As Long, _
                    ByVal lpOperation As String, _
                    ByVal lpFile As String, _
                    ByVal lpParameters As String, _
                    ByVal lpDirectory As String, _
                    ByVal nShowCmd As Long) As Long
 
Private Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2
 

Private Sub cmdTest_Click()
 
Dim sPath As String

sPath = "Path to DB2..."
ShellExecute Me.hwnd, "Open", sPath, vbNullString, "C:\", SW_HIDE
 
End Sub

Thank you for the help.

 

[boblarson]

Oops, I had somehow misread the steps you were wanting.

 

[cimathers]

No worries. Once I had passed that part evreything else fell into place. I can now automatically update out versioned MDE files. Just need to implement it into the correct database now.

Again, cheers. One happy chappy sat here.

 

[boblarson]

No worries. Once I had passed that part evreything else fell into place. I can now automatically update out versioned MDE files. Just need to implement it into the correct database now.

Boy, you could have saved yourself a load of trouble if you had just downloaded my free Auto Update Enabling Tool. It would implement all of this for you without much work on your part.

 

[cimathers]

Boy, you could have saved yourself a load of trouble if you had just downloaded my free Auto Update Enabling Tool. It would implement all of this for you without much work on your part.

Must admit, did have a look. But, I am unable to create and run batch files do to system restrictions. Pesky critters.