pjotter
05-25-2011, 03:54 AM
Might be a hard question...
I'm calling data from tables in a database using Queries... This works perfectly with the following code:
Sub GetSubPart(PartCode As String)
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
Dim ii, iiLineNr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Objecten aanmaken voor connectie met database
Set CN = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Aanmaken van een tijdelijke ODBC-koppeling met de Isah-7 database
Call Open_ODBC_ISAH
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
ThisWorkbook.Activate
Sheets("SubParts").Select
Rows("4:9999").Select
Selection.ClearContents
'Query starten over database connectie
cSql = "Select t1.* " & _
"From part t1, BillOfMat t2 " & _
"Where t2.PartCode =""" & PartCode & """ " & _
"and t1.PartCode = t2.subPartCode "
rs.Open cSql, CN, 0, , 1
ii = 65
iiLineNr = 4
Do While Not rs.EOF
WriteCell GetColumn(ii) & CStr(iiLineNr), rs("PartCode").Value
WriteCell GetColumn(ii + 1) & CStr(iiLineNr), rs("MemoGrpId").Value
'WriteCell GetColumn(ii + 2) & CStr(iiLineNr), rs("MachGrpCode").Value
'WriteCell GetColumn(ii + 3) & CStr(iiLineNr), rs("BooPartDescription").Value
'WriteCell GetColumn(ii + 4) & CStr(iiLineNr), rs("MachCycleTime").Value
'WriteCell GetColumn(ii + 5) & CStr(iiLineNr), rs("MachSetupTime").Value
'WriteCell GetColumn(ii + 6) & CStr(iiLineNr), rs("Info").Value
'WriteCell GetColumn(ii + 7) & CStr(iiLineNr), "'" & rs("ProducedQty").Value
'WriteCell GetColumn(ii + 8) & CStr(iiLineNr), "'" & rs("ProdHeaderOrdNr").Value
'WriteCell GetColumn(ii + 9) & CStr(iiLineNr), "'" & rs("PartCode").Value
'WriteCell GetColumn(ii + 10) & CStr(iiLineNr), "'" & rs("StartDate").Value
'WriteCell GetColumn(ii + 11) & CStr(iiLineNr), "'" & rs("EndDate").Value
'WriteCell GetColumn(ii + 12) & CStr(iiLineNr), "'" & rs("StandCapacity").Value
iiLineNr = iiLineNr + 1
rs.MoveNext
Loop
'Afsluiten odbc connectie
Call Close_ODBC_ISAH
'Objecten opruimen
Set CN = Nothing
Set rs = Nothing
Range("A4").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Now I try to do the same, but I wanna use a stored procedure for it, which creates a combined table of multiple queries. I tried putting it the following way:
Sub GetPrijzen(PartCode As String)
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
Dim ii, iiLineNr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Objecten aanmaken voor connectie met database
Set CN = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Aanmaken van een tijdelijke ODBC-koppeling met de Isah-7 database
Call Open_ODBC_ISAH
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
ThisWorkbook.Activate
Sheets("Prijzen").Select
Rows("4:9999").Select
Selection.ClearContents
'Query starten over database connectie
cSql = "Declare @date DateTime " & _
"Select @date = current_date() " & _
"Exec IP_rpt_R0108 @PartCode = """ & PartCode & """, @QtyInCalcUnit =1000," & _
"@RevDate = ""25-05-2011"", @AllParts =0, @ReportRevDate= @Date, @IsahUserCode ='mc'," & _
"@LogProgramCode =0"
rs.Open cSql, CN, 0, , 1
ii = 65
iiLineNr = 4
Do While Not rs.EOF
WriteCell GetColumn(ii) & CStr(iiLineNr), rs("PartCode").Value
WriteCell GetColumn(ii + 1) & CStr(iiLineNr), rs("LineNr").Value
WriteCell GetColumn(ii + 2) & CStr(iiLineNr), rs("Code").Value
WriteCell GetColumn(ii + 3) & CStr(iiLineNr), rs("Description").Value
WriteCell GetColumn(ii + 4) & CStr(iiLineNr), rs("Info").Value
WriteCell GetColumn(ii + 5) & CStr(iiLineNr), rs("VarCost").Value
WriteCell GetColumn(ii + 6) & CStr(iiLineNr), rs("FixedCost").Value
WriteCell GetColumn(ii + 7) & CStr(iiLineNr), rs("TotalBurdenCost").Value
WriteCell GetColumn(ii + 8) & CStr(iiLineNr), rs("TotalCostIncBurden").Value
'WriteCell GetColumn(ii + 9) & CStr(iiLineNr), "'" & rs("PartCode").Value
'WriteCell GetColumn(ii + 10) & CStr(iiLineNr), "'" & rs("StartDate").Value
'WriteCell GetColumn(ii + 11) & CStr(iiLineNr), "'" & rs("EndDate").Value
'WriteCell GetColumn(ii + 12) & CStr(iiLineNr), "'" & rs("StandCapacity").Value
iiLineNr = iiLineNr + 1
rs.MoveNext
Loop
'Afsluiten odbc connectie
Call Close_ODBC_ISAH
'Objecten opruimen
Set CN = Nothing
Set rs = Nothing
Range("A4").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Now the code walks perfectly until the red marked text. It gives the following error there Error 3704: Operation is not allowed when the object is closed.
When I execute the following code in ASE isql, I get exactly the data I want to retreive:
"Declare @date DateTime " & _
"Select @date = current_date() " & _
"Exec IP_rpt_R0108 @PartCode = """ & PartCode & """, @QtyInCalcUnit =1000," & _
"@RevDate = ""25-05-2011"", @AllParts =0, @ReportRevDate= @Date, @IsahUserCode ='mc'," & _
"@LogProgramCode =0"
Since I can call the line: "rs.Open cSql, CN, 0, , 1"
I am figuring something is wrong within the stored Procedure and I close the Recordset there somehow. Or if I wanna call the stored procedure like this I should change some settings...
While I am just in the learning curve of this all and I cannot really find any help with this specific problem on here or other sites, I am asking here in the hope someone has some experience with this.
I'm calling data from tables in a database using Queries... This works perfectly with the following code:
Sub GetSubPart(PartCode As String)
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
Dim ii, iiLineNr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Objecten aanmaken voor connectie met database
Set CN = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Aanmaken van een tijdelijke ODBC-koppeling met de Isah-7 database
Call Open_ODBC_ISAH
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
ThisWorkbook.Activate
Sheets("SubParts").Select
Rows("4:9999").Select
Selection.ClearContents
'Query starten over database connectie
cSql = "Select t1.* " & _
"From part t1, BillOfMat t2 " & _
"Where t2.PartCode =""" & PartCode & """ " & _
"and t1.PartCode = t2.subPartCode "
rs.Open cSql, CN, 0, , 1
ii = 65
iiLineNr = 4
Do While Not rs.EOF
WriteCell GetColumn(ii) & CStr(iiLineNr), rs("PartCode").Value
WriteCell GetColumn(ii + 1) & CStr(iiLineNr), rs("MemoGrpId").Value
'WriteCell GetColumn(ii + 2) & CStr(iiLineNr), rs("MachGrpCode").Value
'WriteCell GetColumn(ii + 3) & CStr(iiLineNr), rs("BooPartDescription").Value
'WriteCell GetColumn(ii + 4) & CStr(iiLineNr), rs("MachCycleTime").Value
'WriteCell GetColumn(ii + 5) & CStr(iiLineNr), rs("MachSetupTime").Value
'WriteCell GetColumn(ii + 6) & CStr(iiLineNr), rs("Info").Value
'WriteCell GetColumn(ii + 7) & CStr(iiLineNr), "'" & rs("ProducedQty").Value
'WriteCell GetColumn(ii + 8) & CStr(iiLineNr), "'" & rs("ProdHeaderOrdNr").Value
'WriteCell GetColumn(ii + 9) & CStr(iiLineNr), "'" & rs("PartCode").Value
'WriteCell GetColumn(ii + 10) & CStr(iiLineNr), "'" & rs("StartDate").Value
'WriteCell GetColumn(ii + 11) & CStr(iiLineNr), "'" & rs("EndDate").Value
'WriteCell GetColumn(ii + 12) & CStr(iiLineNr), "'" & rs("StandCapacity").Value
iiLineNr = iiLineNr + 1
rs.MoveNext
Loop
'Afsluiten odbc connectie
Call Close_ODBC_ISAH
'Objecten opruimen
Set CN = Nothing
Set rs = Nothing
Range("A4").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Now I try to do the same, but I wanna use a stored procedure for it, which creates a combined table of multiple queries. I tried putting it the following way:
Sub GetPrijzen(PartCode As String)
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
Dim ii, iiLineNr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Objecten aanmaken voor connectie met database
Set CN = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Aanmaken van een tijdelijke ODBC-koppeling met de Isah-7 database
Call Open_ODBC_ISAH
'Vullen tabblad met een lijst van openstaande werkkaarten voor de betreffende afdeling
ThisWorkbook.Activate
Sheets("Prijzen").Select
Rows("4:9999").Select
Selection.ClearContents
'Query starten over database connectie
cSql = "Declare @date DateTime " & _
"Select @date = current_date() " & _
"Exec IP_rpt_R0108 @PartCode = """ & PartCode & """, @QtyInCalcUnit =1000," & _
"@RevDate = ""25-05-2011"", @AllParts =0, @ReportRevDate= @Date, @IsahUserCode ='mc'," & _
"@LogProgramCode =0"
rs.Open cSql, CN, 0, , 1
ii = 65
iiLineNr = 4
Do While Not rs.EOF
WriteCell GetColumn(ii) & CStr(iiLineNr), rs("PartCode").Value
WriteCell GetColumn(ii + 1) & CStr(iiLineNr), rs("LineNr").Value
WriteCell GetColumn(ii + 2) & CStr(iiLineNr), rs("Code").Value
WriteCell GetColumn(ii + 3) & CStr(iiLineNr), rs("Description").Value
WriteCell GetColumn(ii + 4) & CStr(iiLineNr), rs("Info").Value
WriteCell GetColumn(ii + 5) & CStr(iiLineNr), rs("VarCost").Value
WriteCell GetColumn(ii + 6) & CStr(iiLineNr), rs("FixedCost").Value
WriteCell GetColumn(ii + 7) & CStr(iiLineNr), rs("TotalBurdenCost").Value
WriteCell GetColumn(ii + 8) & CStr(iiLineNr), rs("TotalCostIncBurden").Value
'WriteCell GetColumn(ii + 9) & CStr(iiLineNr), "'" & rs("PartCode").Value
'WriteCell GetColumn(ii + 10) & CStr(iiLineNr), "'" & rs("StartDate").Value
'WriteCell GetColumn(ii + 11) & CStr(iiLineNr), "'" & rs("EndDate").Value
'WriteCell GetColumn(ii + 12) & CStr(iiLineNr), "'" & rs("StandCapacity").Value
iiLineNr = iiLineNr + 1
rs.MoveNext
Loop
'Afsluiten odbc connectie
Call Close_ODBC_ISAH
'Objecten opruimen
Set CN = Nothing
Set rs = Nothing
Range("A4").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Now the code walks perfectly until the red marked text. It gives the following error there Error 3704: Operation is not allowed when the object is closed.
When I execute the following code in ASE isql, I get exactly the data I want to retreive:
"Declare @date DateTime " & _
"Select @date = current_date() " & _
"Exec IP_rpt_R0108 @PartCode = """ & PartCode & """, @QtyInCalcUnit =1000," & _
"@RevDate = ""25-05-2011"", @AllParts =0, @ReportRevDate= @Date, @IsahUserCode ='mc'," & _
"@LogProgramCode =0"
Since I can call the line: "rs.Open cSql, CN, 0, , 1"
I am figuring something is wrong within the stored Procedure and I close the Recordset there somehow. Or if I wanna call the stored procedure like this I should change some settings...
While I am just in the learning curve of this all and I cannot really find any help with this specific problem on here or other sites, I am asking here in the hope someone has some experience with this.