vba_confused
asked on
How to reuse a recordset in vba
Hi,
I'm using a sql statement to extract data on to a recordset in VBA.
However, I'm looping this process. I need to run the same process six times.
It looks like the sql statement executes and extracts the data correctly the first time, but when I loop through the second time, it's not allowing me to do so. It looks like data is not being put in to the recordset the second time around.
Could someone help me to figure out how I can do this?
I've attached the code below.
Please HELP ASAP!
I'm using a sql statement to extract data on to a recordset in VBA.
However, I'm looping this process. I need to run the same process six times.
It looks like the sql statement executes and extracts the data correctly the first time, but when I loop through the second time, it's not allowing me to do so. It looks like data is not being put in to the recordset the second time around.
Could someone help me to figure out how I can do this?
I've attached the code below.
Please HELP ASAP!
For Each numratio In Range("renewalratiocount")
'if x = 6 then arm product
x = x + 1
If x = 6 Then
producttype = "arm"
term = 5
Else
producttype = "fixed"
term = x
End If
If numratio.Value <> 0 Then
' Create the RenewRsMin table on the server
Set MyCommand = New ADODB.Command
Set MyCommand.ActiveConnection = MyDatabase
MyCommand.CommandText = "spRenewRsMin" ' <- name of procedure
MyCommand.CommandType = adCmdStoredProc
' pass the values to the stored procedure
With MyCommand
.Parameters.Append .CreateParameter("vol", adInteger, adParamInput, , vol)
.Parameters.Append .CreateParameter("ppp", adInteger, adParamInput, , ppp)
.Parameters.Append .CreateParameter("liquidation", adInteger, adParamInput, , liquidation)
.Parameters.Append .CreateParameter("ratio", adInteger, adParamInput, , numratio.Value)
.Parameters.Append .CreateParameter("producttype", adVarChar, adParamInput, 10, producttype)
.Parameters.Append .CreateParameter("term", adInteger, adParamInput, , term)
End With
MyCommand.Execute , , adAsyncExecute
' Close and re-open the connection to refresh.
MyDatabase.Close
MyDatabase.Open
' print the min file on to the RemRs.min file
sqlmin = "select code,term,princ,strike,amort,maturity,firexer,cpr,smm,pipcode,pipiss,mbscpn,servicing,volat,minsprd,perration,trancost,solvepre,issdt,mbsmat,cpr2,smm2,fundingdatemin from renewrsmin"
Set rstempdata = MyDatabase.Execute(sqlmin)
blnColumnHeaders = False
sterm = Right(Str(term), 1)
If producttype = "arm" Then
minfile = Worksheets("sheet1").Range("renewminfilename").Value + "a" + sterm
Else: minfile = Worksheets("sheet1").Range("renewminfilename").Value + "f" + sterm
End If
Open Worksheets("sheet1").Range("crvdirectory").Value + "\" + minfile + ".min" For Output As #1
' Set the Delimiter for the output file
strDelimeter = " "
strtext = " "
Do While Not rstempdata.EOF
For Each fld In rstempdata.Fields
strtext = strtext & fld.Value & strDelimeter
Next
'get rid of the extra delimeter
strtext = Left(strtext, Len(strtext) - Len(strDelimeter))
strtext = strtext & vbNewLine & " "
rstempdata.MoveNext
Loop
'removes the extra line at the bottom of the file
strtext = Left(strtext, Len(strtext) - Len(vbNewLine))
Print #1, strtext
Close #1
rstempdata.Close
Set rstempdata = Nothing
'attach the mortgagenumber
sqlmin = "select id from renewrsmin"
Set tempdata = MyDatabase.Execute(sqlmin)
If producttype = "arm" Then
minfile = "renewOutputa5"
Else: minfile = "renewoutputf" + Right(sterm, 1)
End If
y = 1
tempdata.MoveFirst
Do While Not tempdata.EOF
For Each fld In tempdata.Fields
y = y + 1
Worksheets(minfile).Select
If y = 2 Then ActiveSheet.Range("A2:AX65536").Clear
ActiveSheet.Range("A" & y).Select
ActiveCell.Value = fld.Value
Next
tempdata.MoveNext
Loop
tempdata.Close
Set tempdata = Nothing
End If
Worksheets("sheet1").Select
'.Delete AffectRecords
MyDatabase.Close
MyDatabase.Open
Next
MyDatabase.Close
Your code is a bit of a mess .... where exactly are you filling the recordset?
ASKER
Starting in line 39 to 41. where I execute the sql statement and set the recordset called rstempdata.
Your assistance is greatly appreciated.
Your assistance is greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It was just a problem within the stored procedure. a numeric variable was being assigned as a varchar.
Otherwise, looks like the code works
Otherwise, looks like the code works
So why the C grade? If my answer wasn't the answer just don't accept it and instead delete the question. A "C" grade is a slap in the face.