C
carrie schmid
I cannot find anything to close the shell. I need the shell to close after
r010 is completed, after personnel is completed, etc. It using Oracle,
which will not allow for simultaneous sessions. Any help would be
appreciated. thanks!
Carrie
Private Sub Command72_Click()
On Error GoTo Err_Command72_Click
DoCmd.SetWarnings False
' Set variables for program
Dim stAppName As String
Dim DiscovererPath As String
Dim UserName As String
Dim Password As String
Dim ConnectName As String
Dim TableName As String
Dim TableRS As Recordset
Dim MyDb As Database
Dim FileName As String
Dim RetVal
Dim fs As Object
Dim epath As String
Dim test As String
' Start adding records to database from Excel files
TableName = "WingInfo"
Set MyDb = CurrentDb
Set TableRS = MyDb.OpenRecordset(TableName, dbOpenSnapshot)
With TableRS
If Not .EOF Then
.MoveFirst
DiscovererPath = !DiscovererPath
UserName = !UserName
Password = !Password
ConnectName = !ConnectName
epath = !epath
End If
End With
' Set up filename of Excel file downloaded from the ARMS database
r010:
FileName = [epath] & "\r010.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\r010.dis /export xls " & [epath] &
"\r010.xls /sheet all /batch"
Call Shell(stAppName, 1)
r010wait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo personnel
Else: GoTo r010wait
End If
personnel:
FileName = [epath] & "\importpersonnel.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\importpersonnel.dis /export xls " &
[epath] & "\importpersonnel.xls /sheet all /batch"
Call Shell(stAppName, 1)
personnelwait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo aodate
Else: GoTo personnelwait
End If
aodate:
FileName = [epath] & "\aodate.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\aodate.dis /export xls " & [epath] &
"\aodate.xls /sheet all /batch"
Call Shell(stAppName, 1)
aodatewait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo fly
Else: GoTo aodatewait
End If
fly:
FileName = [epath] & "\fly.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\fly.DIS /export xls" & [epath] &
"\fly.xls /sheet all /batch"
Call Shell(stAppName, 1)
flywait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
MyDb.Close
GoTo gnd
Else
GoTo flywait
End If
gnd:
FileName = "C:\smart\gnd.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\gnd.DIS /export xls c:\smart\gnd.xls
/sheet all /batch"
Call Shell(stAppName, 1)
gndwait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo dupcurrency
Else: GoTo gndwait
dupcurrency:
FileName = [epath] & "\dupcurrency.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\dupcurrency.DIS /export xls" &
[epath] & "\dupcurrency.xls /sheet all /batch"
Call Shell(stAppName, 1)
dupcurrencywait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo dupfly
Else: GoTo dupcurrencywait
dupfly:
FileName = "C:\smart\dupfly.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\dupfly.DIS /export xls
c:\smart\dupfly.xls /sheet all /batch"
Call Shell(stAppName, 1)
dupflywait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo g260
Else: GoTo dupflywait
g260:
FileName = "C:\smart\g260.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\g260.DIS /export xls
c:\smart\g260.xls /sheet all /batch"
Call Shell(stAppName, 1)
g260wait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo hours
Else: GoTo g260wait
hours:
FileName = "C:\smart\hours.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\hours.DIS /export xls
c:\smart\hours.xls /sheet all /batch"
Call Shell(stAppName, 1)
hourswait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo simmax
Else: GoTo hourswait
simmax:
FileName = "C:\smart\simmax.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\simmax.DIS /export xls
c:\smart\simmax.xls /sheet all /batch"
Call Shell(stAppName, 1)
simmaxwait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo pexper5
Else: GoTo simmaxwait
pexper5:
FileName = "C:\smart\pexper5.csv"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\pexper5.DIS /export csv
c:\smart\pexper5.csv "
Call Shell(stAppName, 1)
pexper5wait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo pextng4
Else: GoTo pexper5wait
pextng4:
FileName = "C:\smart\pextng4.csv"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\pextng4.DIS /export csv
c:\smart\pextng4.csv "
Call Shell(stAppName, 1)
' AppActivate stAppName
Exit_Command72_Click:
DoCmd.Beep
TableRS.Close
MyDb.Close
MsgBox "Please be patient...this may take a few minutes. Exit Oracle
Discoverer when download is complete. Select OK to continue..."
If RetVal <> 0 Then
MsgBox "Download complete. Press any key to continue..."
End If
DoCmd.SetWarnings True
Exit Sub
Err_Command72_Click:
TableRS.Close
MyDb.Close
DoCmd.SetWarnings True
DoCmd.Beep
MsgBox "Make sure you entered a Password in Step 1 and your User Login
info is correct!"
Resume Exit_Command72_Click
End If
End If
End If
End If
End If
End If
End If
End Sub
r010 is completed, after personnel is completed, etc. It using Oracle,
which will not allow for simultaneous sessions. Any help would be
appreciated. thanks!
Carrie
Private Sub Command72_Click()
On Error GoTo Err_Command72_Click
DoCmd.SetWarnings False
' Set variables for program
Dim stAppName As String
Dim DiscovererPath As String
Dim UserName As String
Dim Password As String
Dim ConnectName As String
Dim TableName As String
Dim TableRS As Recordset
Dim MyDb As Database
Dim FileName As String
Dim RetVal
Dim fs As Object
Dim epath As String
Dim test As String
' Start adding records to database from Excel files
TableName = "WingInfo"
Set MyDb = CurrentDb
Set TableRS = MyDb.OpenRecordset(TableName, dbOpenSnapshot)
With TableRS
If Not .EOF Then
.MoveFirst
DiscovererPath = !DiscovererPath
UserName = !UserName
Password = !Password
ConnectName = !ConnectName
epath = !epath
End If
End With
' Set up filename of Excel file downloaded from the ARMS database
r010:
FileName = [epath] & "\r010.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\r010.dis /export xls " & [epath] &
"\r010.xls /sheet all /batch"
Call Shell(stAppName, 1)
r010wait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo personnel
Else: GoTo r010wait
End If
personnel:
FileName = [epath] & "\importpersonnel.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\importpersonnel.dis /export xls " &
[epath] & "\importpersonnel.xls /sheet all /batch"
Call Shell(stAppName, 1)
personnelwait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo aodate
Else: GoTo personnelwait
End If
aodate:
FileName = [epath] & "\aodate.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\aodate.dis /export xls " & [epath] &
"\aodate.xls /sheet all /batch"
Call Shell(stAppName, 1)
aodatewait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo fly
Else: GoTo aodatewait
End If
fly:
FileName = [epath] & "\fly.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\fly.DIS /export xls" & [epath] &
"\fly.xls /sheet all /batch"
Call Shell(stAppName, 1)
flywait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
MyDb.Close
GoTo gnd
Else
GoTo flywait
End If
gnd:
FileName = "C:\smart\gnd.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\gnd.DIS /export xls c:\smart\gnd.xls
/sheet all /batch"
Call Shell(stAppName, 1)
gndwait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo dupcurrency
Else: GoTo gndwait
dupcurrency:
FileName = [epath] & "\dupcurrency.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\dupcurrency.DIS /export xls" &
[epath] & "\dupcurrency.xls /sheet all /batch"
Call Shell(stAppName, 1)
dupcurrencywait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo dupfly
Else: GoTo dupcurrencywait
dupfly:
FileName = "C:\smart\dupfly.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\dupfly.DIS /export xls
c:\smart\dupfly.xls /sheet all /batch"
Call Shell(stAppName, 1)
dupflywait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo g260
Else: GoTo dupflywait
g260:
FileName = "C:\smart\g260.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\g260.DIS /export xls
c:\smart\g260.xls /sheet all /batch"
Call Shell(stAppName, 1)
g260wait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo hours
Else: GoTo g260wait
hours:
FileName = "C:\smart\hours.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\hours.DIS /export xls
c:\smart\hours.xls /sheet all /batch"
Call Shell(stAppName, 1)
hourswait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo simmax
Else: GoTo hourswait
simmax:
FileName = "C:\smart\simmax.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\simmax.DIS /export xls
c:\smart\simmax.xls /sheet all /batch"
Call Shell(stAppName, 1)
simmaxwait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo pexper5
Else: GoTo simmaxwait
pexper5:
FileName = "C:\smart\pexper5.csv"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\pexper5.DIS /export csv
c:\smart\pexper5.csv "
Call Shell(stAppName, 1)
pexper5wait:
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
GoTo pextng4
Else: GoTo pexper5wait
pextng4:
FileName = "C:\smart\pextng4.csv"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FileName) Then
fs.DeleteFile FileName
End If
stAppName = [DiscovererPath] & " /connect " & [UserName] & "/" & [Password]
& "@" & [ConnectName] & " /open c:\smart\pextng4.DIS /export csv
c:\smart\pextng4.csv "
Call Shell(stAppName, 1)
' AppActivate stAppName
Exit_Command72_Click:
DoCmd.Beep
TableRS.Close
MyDb.Close
MsgBox "Please be patient...this may take a few minutes. Exit Oracle
Discoverer when download is complete. Select OK to continue..."
If RetVal <> 0 Then
MsgBox "Download complete. Press any key to continue..."
End If
DoCmd.SetWarnings True
Exit Sub
Err_Command72_Click:
TableRS.Close
MyDb.Close
DoCmd.SetWarnings True
DoCmd.Beep
MsgBox "Make sure you entered a Password in Step 1 and your User Login
info is correct!"
Resume Exit_Command72_Click
End If
End If
End If
End If
End If
End If
End If
End Sub