Solved

# how to close a recordset really?

Posted on 2000-04-28
163 Views
as follow:
*************************************
Function rhigh(maxdate As Date, Edate As Date, stockID As String, n As Integer, resultvalue As Integer, eachfield As String, resultfield As String)
Dim str
Dim rmaxdate As Date
str = "select count(*) as sum ,max(" & eachfield & ") as max ,stockID from " & stockID & " where tdate>#" & maxdate & "# and tdate<#" & Edate & "# group by stockID"
Set RSm = stockDB.Execute(str)
If Not RSm.EOF Then
If RSm("sum") > n Then
str = "select tdate from " & stockID & " where tdate>#" & maxdate & "# and tdate<#" & Edate & "# and " & eachfield & "=" & RSm("max") & " order by tdate"
RSm.Close
If Not RSm.EOF Then
rmaxdate = RSm("tdate")
RSm.Close
str = "select count(*) as count from " & stockID & " where tdate<#" & rmaxdate & "# and tdate>#" & maxdate & "#"
RSm.Open str, stockDB
If RSm("count") >= n Then
str = "update resulthis set " & resultfield & "='" & resultvalue & "' where stockID ='" & stockID & "' and tdate=#" & rmaxdate & "#"
stockDB.Execute str
lhigh maxdate, rmaxdate, stockID, n, resultvalue, eachfield, resultfield
End If
rhigh rmaxdate, Edate, stockID, n, resultvalue, eachfield, resultfield
End If
End If
End If
RSm.Close
End If
End Function

Function newlhigh(maxdate As Date, Edate As Date, stockID As String, n As Integer, resultvalue As Integer, eachfield As String, resultfield As String)
Dim str, rmaxdate
If maxdate < Edate And Edate > startdate Then
str = "select max(" & eachfield & ") as max ,stockID from " & stockID & " where tdate>#" & maxdate & "# and tdate<#" & Edate & "# group by stockID"
Set RSm = stockDB.Execute(str)
str = "select tdate from " & stockID & " where tdate>#" & maxdate & "# and tdate<#" & Edate & "# and " & eachfield & "=" & RSm("max") & " order by tdate"
RSm.Close
If Not RSm.EOF Then
str = "update resulthis set " & resultfield & "='" & resultvalue & "' where stockID ='" & stockID & "' and tdate=#" & RSm("tdate") & "#"
stockDB.Execute str
newlhigh maxdate, RSm("tdate"), stockID, n, resultvalue, eachfield, resultfield
rhigh RSm("tdate"), Edate, stockID, n, resultvalue, eachfield, resultfield
End If
RSm.Close
End If
End Function

Function lhigh(maxdate As Date, Edate As Date, stockID As String, n As Integer, resultvalue As Integer, eachfield As String, resultfield As String)
Dim str, rmaxdate
If maxdate < Edate And Edate > startdate Then
str = "select count(*) as sum ,max(" & eachfield & ") as max ,stockID from " & stockID & " where tdate>#" & maxdate & "# and tdate<#" & Edate & "# group by stockID"
Set RSm = stockDB.Execute(str)
If Not RSm.EOF Then
If RSm("sum") > n Then
str = "select tdate from " & stockID & " where tdate>#" & maxdate & "# and tdate<#" & Edate & "# and " & eachfield & "=" & RSm("max") & " order by tdate"
RSm.Close
If Not RSm.EOF Then
str = "update resulthis set " & resultfield & "='" & resultvalue & "' where stockID ='" & stockID & "' and tdate=#" & RSm("tdate") & "#"
stockDB.Execute str
lhigh maxdate, RSm("tdate"), stockID, n, resultvalue, eachfield, resultfield
rhigh RSm("tdate"), Edate, stockID, n, resultvalue, eachfield, resultfield
End If
End If
End If
RSm.Close
End If
End Function
**************************************
errors appears:
runtime error(80004005),cannot open a table again.

please tell me why to open many table but not to close them really?
how to do ?

0
Question by:senucarthur
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 3

Accepted Solution

myqlG earned 50 total points
ID: 2760366
rs.close
set rs = nothing
0

LVL 2

Expert Comment

ID: 2760368
set RSm = nothing
0

LVL 6

Expert Comment

ID: 2760371
To properly close recordset is to first close it by using .Close Method and then to Set it to Nothing therefore releasing it from memory. You can use more then one recordset which will point to other tables.
0

LVL 6

Expert Comment

ID: 2760390
you using connection alot and executing Update statements on it which is great, but if you don't want it to return recordset do it like this.

Because behind the sence ADO builds a recordset for you, this won't cause you problems but it might be faster for your application to run.

0

LVL 15

Expert Comment

ID: 2761087
You are using recursion, which is not good for cleanup of your variables.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
###### Suggested Courses
Course of the Month8 days, 21 hours left to enroll