Code to close a db?

[Dudley]

I'm working on a utility to archive data from my application. The utility creates two copies of the db in an "archive" directory.

I want to compact the first one and save it with all the records as a backup to the operation.
I want to run two action queries in the second one to switch the selected set and then to delete them, and then compact that one as the archive of the records I'm about to delete in the original file.

(I tried 'OpenCurrentDatabase' but ran into problems when I tried to compact it - an error message about not being able to do that to the current db.)

My code is almost working except that the db's are left open after I run the lines that open them and do the things I want them to do. I'm running blind here, having adapted code from snippets I found here on the forum and from Roger Carlson's site (the BackUpWithCompact.mdb). I just don't know how to code them to close once they do their thing.

Could someone point me in the right direction for this?

Here's the code for running these processes:

    AppPath = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE"
    htask = Shell(AppPath & " """ & strDestinationDir & "TempDB_BU.mdb" & """" & " /compact", 1)    'compacts backup file
    If htask <> 0 Then 'continue
        If Not Dir(strDestinationDir & "TempDB_BU.mdb") = "" Then FileCopy (strDestinationDir & "TempDB_BU.mdb"), DBBackupFileName
        MsgBox "Backup File Successfully Created:  " & strDestinationDir & DBBackupFileName
        'Process the Archive file - Switch the selected set, delete selecteds, compact
        htask = Shell(AppPath & " """ & strDestinationDir & "TempDB_Arch.mdb" & """" & " /x macARCHIVE_SwitchSelected")
        If htask <> 0 Then
            htask = Shell(AppPath & " """ & strDestinationDir & "TempDB_Arch.mdb" & """" & " /x macARCHIVE_DeleteSelected")
            If htask <> 0 Then
                htask = Shell(AppPath & " """ & strDestinationDir & "TempDB_Arch.mdb" & """" & " /compact", 1)
                    If htask <> 0 Then FileCopy strDestinationDir & "TempDB_Arch.mdb", DBArchiveFileName
            Else
                GoTo Fail
            End If
        Else
            GoTo Fail
        End If
    Else
Fail:   MsgBox "Archive Process Failed."
        Exit Sub
    End If

Sorry Folks. Sometimes I post my questions way too quickly. All I had to do was put a "Quit" line in the macros to switch the selected set and delete the selected set. It works really nicely now, except for the potential lag between the VB code and the Shell activity. I think I'll try to set up some kind of Do Until loop that checks for the PID (htask value?) and doesn't find it before trying to rename the temporary db's to their permanent names. Actually, it would just be easier to give them their final names right up front, wouldn't it. <<sigh>> Such a flake sometimes. Thanks everybody. -Dudley

 

[Dudley]

I'm still trying to get this to work the way I want.

I've learned about CreateProcess and Shell and Wait and implemented it as below.

The problem I'm having is that CreateProcess doesn't have a way to control the window that I understand. Shell had an argument to specify the window's behavior (vbMinimizedNoFocus), but CreateProcess works that in differently.

I found a site: http://www.codeguru.com/forum/archive/index.php/t-12682.html that suggested:

DanniDin
August 21st, 2000, 10:41 AM
Is there a Value I Can Change in

"CreateProcess vbNullString, "mspaint", 0, 0, false, 0, byval 0, vbNullString, sa, pi"

in Order to Hide the Process Like Shell("mspaint", vbHide) Does ?

Danni.

Lothar Haensler
August 21st, 2000, 10:47 AM
sure, the startupinfo argument (argument 9), has a wShowWindow member.
You can set it to SW_HIDE (check the value from the API declaration add-in)

Which lead me to understand (?) that the control for this is set by "wShowWindow" in the 9th argument of CreateProcess, which is set in a way I don't understand.

I looked up the value of SW_HIDE in the Object Browser, and it's zero (0).

Could anyone help me understand how to make this work? I want the Access window to run unseen by the user when my commands go.

Here is the code from the module for CreateProcess and ExecCMD:

Option Compare Database
Option Explicit

Private Type STARTUPINFO
   cb As Long
   lpReserved As String
   lpDesktop As String
   lpTitle As String
   dwX As Long
   dwY As Long
   dwXSize As Long
   dwYSize As Long
   dwXCountChars As Long
   dwYCountChars As Long
   dwFillAttribute As Long
   dwFlags As Long
   wShowWindow As Integer
   cbReserved2 As Integer
   lpReserved2 As Long
   hStdInput As Long
   hStdOutput As Long
   hStdError As Long
End Type

Private Type PROCESS_INFORMATION
   hProcess As Long
   hThread As Long
   dwProcessID As Long
   dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal _
   hObject As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public Function ExecCmd(cmdline$)

   Dim proc As PROCESS_INFORMATION
   Dim start As STARTUPINFO
   Dim ReturnValue As Integer

   ' Initialize the STARTUPINFO structure:
   start.cb = Len(start)

   ' Start the shelled application:
   ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
      NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

   ' Wait for the shelled application to finish:
   Do
      ReturnValue = WaitForSingleObject(proc.hProcess, 0)
      DoEvents
      Loop Until ReturnValue <> 258

   ReturnValue = CloseHandle(proc.hProcess)

End Function

Here's the relevant code that calls these things:

    AppPath = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE"
'    htask = Shell(AppPath & " """ & DBBackupFileName & """" & " /compact", vbMinimizedNoFocus)    'compacts backup file
    ExecCmd (AppPath & " """ & DBBackupFileName & """" & " /compact")  'compacts backup file

'    If htask <> 0 Then 'continue
        Me!txtBackupFileName = Dir(DBBackupFileName)
        Me!txtBackupFileName.Visible = True
        'Process the Archive file - Switch the selected set, delete selecteds, compact
'        htask = Shell(AppPath & " """ & DBArchiveFileName & """" & " /x macARCHIVE_SwitchSelected", vbMinimizedNoFocus)
'        If htask <> 0 Then
        ExecCmd (AppPath & " """ & DBArchiveFileName & """" & " /x macARCHIVE_SwitchSelected")
'            htask = Shell(AppPath & " """ & DBArchiveFileName & """" & " /x macARCHIVE_DeleteSelected", vbMinimizedNoFocus)
'            If htask <> 0 Then
            ExecCmd (AppPath & " """ & DBArchiveFileName & """" & " /x macARCHIVE_DeleteSelected")
'                htask = Shell(AppPath & " """ & DBArchiveFileName & """" & " /compact", vbMinimizedNoFocus)
'                If htask <> 0 Then
                ExecCmd (AppPath & " """ & DBArchiveFileName & """" & " /compact")

I really appreciate any help anyone can give me on this.

Sincerely,
Dudley

 

[CyberLynx]

Play with the following code........see if you can work it in:

If the External Database was opened using the Following method:

Dim mAcc as Access.Application

Set mAcc = New Access.Application

[COLOR="DarkGreen"]'open the database[/COLOR]
mAcc.OpenCurrentDatabase "[I][COLOR="Red"]full path to your db[/COLOR][/I]"

[COLOR="DarkGreen"]'launch the form[/COLOR]
mAcc.DoCmd.OpenForm "[I][COLOR="Red"]TheFormToOpen[/COLOR][/I]"

[COLOR="DarkGreen"]'now make it visible[/COLOR]
mAcc.Visible = True  [COLOR="DarkGreen"]'put False to make the DB non-visible.[/COLOR]

Then the external Database can be Closed by using:

mAcc.DoCmd.Quit
Set mAcc = Nothing

This will only work if the mAcc.DoCmd.Quit line is within the same procedure as where mAcc was declared. If the call to quit the external Database is to be located within an entirely different procedure then the mAcc object variable will need to be declared as PUBLIC within the Declarations section of either a Form or Database code Module.

.

 

[Dudley]

Re: Code to hide the db window?

Thanks so much for your thoughts CyberLynx. I'm still pushing on my current approach, but will keep your approach in mind as a next try.

I figured out a little more about "start" and tried start.wShowWindow = 0 and SW_HIDE before the "start.cb" line, but it didn't make any difference. So I searched on start.wShowWindow and found a thread that included declaring the window behavior constants and something new to me, the STARTF_USESHOWWINDOW = &H1 (which I don't understand). The effect, though is that now my windows don't appear. The Access splash screen does, though. So I'm almost there. I think I can live with this if I have to, it's a pretty minor issue, but I'm wondering how to get rid of it so my users will see only my windows and notices.

Can anyone help me understand this better?

Here's what's different from the previous code (the code supplied by Microsoft):

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
Public Const SW_HIDE = 0
Public Const SW_MAXIMIZE = 3
Public Const SW_MINIMIZE = 6
Public Const SW_NORMAL = 1
Public Const STARTF_USESHOWWINDOW = &H1

Public Function ExecCmd(cmdline$)

   Dim proc As PROCESS_INFORMATION
   Dim start As STARTUPINFO
   Dim ReturnValue As Integer

   ' Initialize the STARTUPINFO structure:
    start.dwFlags = STARTF_USESHOWWINDOW
    start.cb = Len(start)

   ' Start the shelled application:
   ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
      NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

Thanks! -Dudley

 

[Dudley]

In the interests of completing this line of inquiry, here's what I've come up with:

I cut the module with the Execcmd down to the following by replacing Microsoft's content for Execcmd with the Shell command, and then used the "Wait" code to check to see whether the shell command had finished. This got me back the "vbMinimizedNoFocus" option available with the Shell command and it all seems to work well now.

Option Compare Database
Option Explicit

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal _
   hObject As Long) As Long

Public Function ExecCmd(cmdline$)

   Dim ReturnValue As Integer
   
   ReturnValue = Shell(cmdline$, vbMinimizedNoFocus)
   ' Wait for the shelled application to finish:
   Do
      ReturnValue = WaitForSingleObject(ReturnValue, 0)
      DoEvents
      Loop Until ReturnValue = 0

   ReturnValue = CloseHandle(ReturnValue)

End Function

Then I call Execcmd from my form button:

        ExecCmd (AppPath & " """ & DBArchiveFileName & """" & " /x macARCHIVE_Create")

Cyber_Lynx, I tried your approach, but I couldn't figure out how to keep the window from opening on-screen right when I called it. So I didn't end up using your code, but I sure appreciate your giving me the answer I was originally looking for. I know I'll use it in the future. So many thanks.

Sincerely,