-
Aug 14th, 2011, 09:33 PM
#1
Thread Starter
Lively Member
[RESOLVED] Program only function partially on other PC
Thanks for visiting my thread, this problem has been bugging me for week, hopefully I can find a solution here.
I have one schedule job "StockIn.job" which will execute "StockIn.bat" everyday at 2pm.
StockIn.bat
- it will run the AutoStockIn.exe to generate StockIn.csv
- it will copy and rename StockIn.csv to path C:\auto\
Code:
c:\AutoStockIn.exe
set timestamp=%DATE:/=-%-%TIME:~0,2%%TIME:~3,2%
copy c:\StockIn.csv c:\auto\%timestamp%.csv
AutoStockIn.exe
- it will read data from two database and export data into stockIn.csv
- i tested on my PC and it functioning properly.
Problem
- the program only partially success when I run in server
- it able to read database 1 and export into stockIn.csv
- it seem by pass reading database 2 and export data from database 2 into stockin.csv (no error message pop out).
Code:
Dim rsCMB As Recordset, rsCCM As Recordset, rsAPP2 As Recordset, rsAPP2_R As Recordset, rsMBM As Recordset
Dim rsCMB_M As Recordset, rsAPP1_M As Recordset, rsAPP2_M As Recordset, rsAPP2_RM As Recordset
Dim frmPath As String, toPath As String
strEmpty = " "
strComma = ","
strFileName = "C:\StockIn.csv"
frmMain.MousePointer = vbHourglass
Me.Refresh
ConnectDatabase
'database 1
Set rsCMB = dbLQW.OpenRecordset("select * from CMB where status = 1 and export = NULL and format(end_time,'yyyy/mm/dd') > Format('2011/06/29','yyyy/mm/dd')")
Set rsCCM = dbLQW.OpenRecordset("select * from PPB where status = 1 and export = NULL and format(end_time,'yyyy/mm/dd') > Format('2011/06/29','yyyy/mm/dd')")
Set rsAPP2 = dbLQW.OpenRecordset("select * from APP2 where status = 1 and export = NULL and format(end_time,'yyyy/mm/dd') > Format('2011/06/29','yyyy/mm/dd')")
Set rsAPP2_R = dbLQW.OpenRecordset("SELECT * From ReAPP2 WHERE export = NULL and status = 1 and format(end_time,'yyyy/mm/dd') > Format('2011/06/29','yyyy/mm/dd')")
Set rsMBM = dbLQW.OpenRecordset("SELECT * From MBM WHERE status = 1 and export = NULL and format(end_time,'yyyy/mm/dd') > Format('2011/06/29','yyyy/mm/dd')")
'database 2
Set rsCMB_M = dbLQH.OpenRecordset("select * from CMB where flag = 1 and export = NULL and format(end_date,'yyyy/mm/dd') > '2011/05/02'")
Set rsAPP1_M = dbLQH.OpenRecordset("select * from APP1 where flag = 1 and export = NULL and format(end_date,'yyyy/mm/dd') > '2011/05/02'")
Set rsAPP2_M = dbLQH.OpenRecordset("select * from APP2 where flag = 1 and export = NULL and format(end_date,'yyyy/mm/dd') > '2011/05/02'")
Set rsAPP2_RM = dbLQH.OpenRecordset("SELECT * From Rework WHERE export = NULL and A_Flag = '1'and format(end_date,'yyyy/mm/dd') > '2011/05/02'")
If rsCMB.BOF And rsCMB.EOF And rsCCM.BOF And rsCCM.EOF And rsAPP2.EOF And rsAPP2.BOF And rsMBM.EOF And rsMBM.BOF And rsCMB_M.EOF And rsCMB_M.BOF And rsAPP1_M.EOF And rsAPP1_M.BOF And rsAPP2_M.EOF And rsAPP2_M.BOF And rsAPP2_RM.EOF And rsAPP2_RM.BOF Then
End
Else
Open strFileName For Output As #1 'to open new file
Do Until rsCMB.EOF
exportcsv = "C10" & strComma & "020" & strComma & "0" & strComma & strEmpty & strComma & rsCMB!LotNo & strComma & rsCMB!output_qty & strComma & "Y" & strComma & rsCMB!product_type
rsCMB.Edit
rsCMB!export = "1"
rsCMB!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsCMB.Update
rsCMB.MoveNext
Print #1, exportcsv
Loop
Do Until rsCCM.EOF
'DoEvents
exportcsv = "C10" & strComma & "041" & strComma & "0" & strComma & strEmpty & strComma & rsCCM!LotNo & strComma & rsCCM!output_qty & strComma & "Y" & strComma & rsCCM!product_type
rsCCM.Edit
rsCCM!export = "1"
rsCCM!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsCCM.Update
rsCCM.MoveNext
Print #1, exportcsv
Loop
Do Until rsAPP2.EOF
'DoEvents
exportcsv = "C10" & strComma & "055" & strComma & "0" & strComma & strEmpty & strComma & rsAPP2!LotNo & strComma & rsAPP2!output_qty & strComma & "Y" & strComma & rsAPP2!product_type
rsAPP2.Edit
rsAPP2!export = "1"
rsAPP2!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsAPP2.Update
rsAPP2.MoveNext
Print #1, exportcsv
Loop
Do Until rsAPP2_R.EOF
'DoEvents
exportcsv = "C10" & strComma & "055" & strComma & "0" & strComma & strEmpty & strComma & Mid(rsAPP2_R!LotNo, 1, 10) & strComma & rsAPP2_R!output_qty & strComma & "Y" & strComma & rsAPP2_R!product_type
rsAPP2_R.Edit
rsAPP2_R!export = "1"
rsAPP2_R!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsAPP2_R.Update
rsAPP2_R.MoveNext
Print #1, exportcsv
Loop
Do Until rsMBM.EOF
'DoEvents
exportcsv = "C10" & strComma & "080" & strComma & "0" & strComma & strEmpty & strComma & rsMBM!LotNo & strComma & rsMBM!balance_qty & strComma & "Y" & strComma & rsMBM!product_type
rsMBM.Edit
rsMBM!export = "1"
rsMBM!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsMBM.Update
rsMBM.MoveNext
Print #1, exportcsv
Loop
' 2nd database
Do Until rsCMB_M.EOF
exportcsv = "C10" & strComma & "020" & strComma & "0" & strComma & strEmpty & strComma & rsCMB_M!LotNo & strComma & rsCMB_M!output_qty & strComma & "Y" & strComma & rsCMB_M!product_type
rsCMB_M.Edit
rsCMB_M!export = "1"
rsCMB_M!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsCMB_M.Update
rsCMB_M.MoveNext
Print #1, exportcsv
Loop
Do Until rsAPP1_M.EOF
'DoEvents
exportcsv = "C10" & strComma & "055" & strComma & "0" & strComma & strEmpty & strComma & rsAPP1_M!LotNo & strComma & rsAPP1_M!output_qty & strComma & "Y" & strComma & rsAPP1_M!product_type
rsAPP1_M.Edit
rsAPP1_M!export = "1"
rsAPP1_M!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsAPP1_M.Update
rsAPP1_M.MoveNext
Print #1, exportcsv
Loop
Do Until rsAPP2_M.EOF
'DoEvents
exportcsv = "C10" & strComma & "080" & strComma & "0" & strComma & strEmpty & strComma & rsAPP2_M!LotNo & strComma & rsAPP2_M!output_qty & strComma & "Y" & strComma & rsAPP2_M!product_type
rsAPP2_M.Edit
rsAPP2_M!export = "1"
rsAPP2_M!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsAPP2_M.Update
rsAPP2_M.MoveNext
Print #1, exportcsv
Loop
Do Until rsAPP2_RM.EOF
'DoEvents
exportcsv = "C10" & strComma & "080" & strComma & "0" & strComma & strEmpty & strComma & Mid(rsAPP2_RM!LotNo, 1, 10) & strComma & rsAPP2_RM!a_output_qty & strComma & "Y" & strComma & rsAPP2_RM!product_type
rsAPP2_RM.Edit
rsAPP2_RM!export = "1"
rsAPP2_RM!Export_Date = Format(Now, "MM/dd/yyyy HH:mm")
rsAPP2_RM.Update
rsAPP2_RM.MoveNext
Print #1, exportcsv
Loop
' 2nd database end
Close #1
End If
End
-
Aug 14th, 2011, 11:13 PM
#2
Thread Starter
Lively Member
Re: Program only function partially on other PC
i checked the schedule log file, it shows exit code (1), it seem return some error when running the job.
i tested the program by manual run the .exe or manual run .job
sometimes the program do fully run with extracting data from both database and create stockIn.csv, but most of the time, it by pass the 2nd database.
-
Aug 15th, 2011, 04:45 PM
#3
Re: Program only function partially on other PC
i can not see where your code is failing, but in several places your format function, of dates, appears to be incorrect as you are only using single quotes, why this is not causing an error i can not tell, i assume there is some sort of error handling, set somewhere, to ignore errors
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 15th, 2011, 06:33 PM
#4
Thread Starter
Lively Member
Re: Program only function partially on other PC
Originally Posted by westconn1
i can not see where your code is failing, but in several places your format function, of dates, appears to be incorrect as you are only using single quotes, why this is not causing an error i can not tell, i assume there is some sort of error handling, set somewhere, to ignore errors
hihi, thanks for reply.
I will modify this part and test again
-----------------------------------------------
I amended the recordset query for 2nd database. Then I manual run .job
It seem work properly by pulling data from both database and create csv
I will observe again at 2pm schedule run today to confirm if it's really work properly, thanks again westconn1 for spotting my bug
Code:
'database 2
Set rsCMB_M = dbLQH.OpenRecordset("select * from CMB where flag = 1 and export = NULL and format(end_date,'yyyy/mm/dd') > Format('2011/05/02','yyyy/mm/dd')")
Set rsAPP1_M = dbLQH.OpenRecordset("select * from APP1 where flag = 1 and export = NULL and format(end_date,'yyyy/mm/dd') > Format('2011/05/02','yyyy/mm/dd')")
Set rsAPP2_M = dbLQH.OpenRecordset("select * from APP2 where flag = 1 and export = NULL and format(end_date,'yyyy/mm/dd') > Format('2011/05/02','yyyy/mm/dd')")
Set rsAPP2_RM = dbLQH.OpenRecordset("SELECT * From Rework WHERE export = NULL and A_Flag = '1'and format(end_date,'yyyy/mm/dd') > Format('2011/05/02','yyyy/mm/dd')")
Last edited by vandoren; Aug 15th, 2011 at 08:36 PM.
-
Aug 15th, 2011, 11:47 PM
#5
Re: Program only function partially on other PC
One observation, it's not the reason for your problem, the use of the 'End' statement should be avoided since it just ends the program without destroying any referenced objects (like your Recordsets). You should (technically) close and destroy all your objects and then use the Unload statement to unload all open Forms to properly terminate the Application.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|