Results 1 to 5 of 5

Thread: [RESOLVED] Program only function partially on other PC

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    Resolved [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

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    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.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    Re: Program only function partially on other PC

    Quote Originally Posted by westconn1 View Post
    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.

  5. #5
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    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
  •  



Click Here to Expand Forum to Full Width