My output is out of order

[bluegno]

I'm trying to write to a file using a combination of DoCmd.TransferText (to a temp file) and DOS commands. Problem is, the lines are getting written out of order!

Here's a snippet (this is actually part of a loop):

strCmd = "cmd /c echo " & strHdr & " >> " & strPath
Shell (strCmd)

DoCmd.TransferText acExportDelim, , tblWhatever, strTmp

strCmd = "cmd /c type " & strTmp & " >> " & strPath
Shell (strCmd)

'Kill strTmp ' this seems to thwart the TransferText

Here's where it gets really bad: if I try to kill the temp file (after appending it to the main file), I lose everything but the headers.

I understand that Shell is asynchronous, so I tried replacing the calls to Shell with CmdWait, where CmdWait is as follows:

Public Sub CmdWait(cmd As String)
   Dim pid As Long
   Dim phnd As Long
   
   pid = Shell(cmd)
   DoEvents
   
   phnd = OpenProcess(SYNCHRONIZE, 0, pid)
   If phnd <> 0 Then
      WaitForSingleObject phnd, INFINITE
      CloseHandle phnd
   End If
End Sub

Supposedly, the OpenProcess/WaitForSingleObject technique forces synchronization, but it did not seem to help in this case.

I am not sure if DoCmd is asynchronous (I couldn't find any info on this). But oddly enough, I do seem to be getting all the data as long as I omit the Kill. It's just that the headers are appearing in random order.

Can anyone tell me what I am doing wrong?

 

[DJkarl]

Access tables have no implicit order to how the records are stored. If you need your export in a certain order create a Select query with the correct order then export that query instead of the table.

 

[bluegno]

DJkarl,

Thanks, but the problem is not with the database. It's with the VBA. My DOS commands (and perhaps DoCmd) are being executed asynchronously, or so it would appear.

This is really stumping me. Any help would be appreciated!

 

[Dennisk]

there is a technique available than will return a value when your async process has completed.

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 Sub 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 Sub

'******************************

'This is the bit where you pass the name of the app you want to 'shell

     Sub Testing()
        ExecCmd "NOTEPAD.EXE"
        MsgBox "Process Finished"
     End Sub

 

[bluegno]

Solved!

Thank you Dennisk! Coincidentally, I ended up implementing the same last night -- and it worked!

Well, it wasn't exactly the same..... I took my code pretty much straight from http://support.microsoft.com/kb/q129796/. The one difference is Microsoft's version is a Function, and it calls WaitForSingleObject only once

      ' Wait for the shelled application to finish:
         ret& = WaitForSingleObject(proc.hProcess, INFINITE)
         Call GetExitCodeProcess(proc.hProcess, ret&)
         Call CloseHandle(proc.hThread)
         Call CloseHandle(proc.hProcess)
         ExecCmd = ret&

whereas Dennisk's ExecCmd is a Sub and it calls WaitForSingleObject in a loop

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

The single (non loop) call seems to work fine for me. From the MSDN library, I see that 258 (102 hex) indicates TIMEOUT. If I were writing a production tool, I would probably not take either of the above approaches (ignore the timeout, or loop infinitely), but would instead report the timeout to the user.

By the way, another version of this solution can be found on the Access Web (mvps.org). It appears to be the same as the Microsoft version, except it allows for a WindowStyle parameter. I don't know exactly how you would use this, except maybe to optionally hide those annoying DOS command windows if you don't need them.

In my case, I definitely don't need to see the command windows. And they are particularly annoying because I'm calling ExecCmd in a loop. But I addressed it by replacing NORMAL_PRIORITY_CLASS with CREATE_NO_WINDOW, which I defined as follows:

Private Const CREATE_NO_WINDOW = &H8000000

I hope this info is helpful to others. The only question that remains for me is why CmdWait didn't work. Maybe the Shell/OpenProcess combo does not work on certain Windows OS's? (I'm using XP)