[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

recordset close  not in right place

Posted on 2012-08-27
4
Medium Priority
?
329 Views
Last Modified: 2012-08-27
exce vba 2010...


I have the following working ok...but it seems to me the recordset , opening and closing is just not quite right.


This code was converted from vba 2003  dao



' Green Data
Dim db As New ADODB.Connection
Dim rec As New ADODB.Recordset


Dim xsearch As String
Dim rng As Range, cell As Range
Dim str As String
Dim str2 As String
Dim falpha As String
   
    str = wrange
       str2 = str

If IsNumeric(Mid(str2, 2, 1)) Then
   falpha = Left(str2, 1)
Else
     falpha = Left(str2, 2)
End If

str = falpha
   

Set rng = Range(str & "2" & ":" & str & Cells(65536, str).End(xlUp).Row)
For Each cell In rng
   
       xsearch = "SELECT tblGreenMasterSkus.fldGreenAlt FROM tblGreenMasterSkus Where tblGreenMasterSkus.fldGreenSku =""" & cell.Value & """;"
 
         
 
 db.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:\Program Files\Crs Enterprise\EnterpriseDatabases\TextFile_Setup.accdb"

rec.Open sQuery, db, adOpenKeyset, adLockOptimistic
 
 
 
 
While Not rec.EOF
  If cell.Offset(0, 8).Value <> "" Then
  cell.Offset(0, 8).Value = cell.Offset(0, 8).Value & " : " & rec.Fields("fldGreenAlt")
  Else
   '
  cell.Offset(0, 8).Value = rec.Fields("fldGreenAlt")
   cell.Offset(0, 7).Value = "Y"
 
 
  End If
  rec.MoveNext
 
 
  Wend

rec.Close
db.Close


  Next cell
 On Error Resume Next
 
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
End Sub
0
Comment
Question by:Fordraiders
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38336514
You don't need to open/close the connection each time you open the recordset, so you can leave the connection open through your entire loop.
The recordset though, needs to be opened and closed each time you want to refresh the data.

Something like:

' Green Data
Dim db As New ADODB.Connection
Dim rec As New ADODB.Recordset


Dim xsearch As String
Dim rng As Range, cell As Range
Dim str As String
Dim str2 As String
Dim falpha As String
    
    str = wrange
       str2 = str

If IsNumeric(Mid(str2, 2, 1)) Then
   falpha = Left(str2, 1)
Else
     falpha = Left(str2, 2)
End If

str = falpha

 db.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:\Program Files\Crs Enterprise\EnterpriseDatabases\TextFile_Setup.accdb"

Set rng = Range(str & "2" & ":" & str & Cells(65536, str).End(xlUp).Row)
For Each cell In rng
   
       xsearch = "SELECT tblGreenMasterSkus.fldGreenAlt FROM tblGreenMasterSkus Where tblGreenMasterSkus.fldGreenSku =""" & cell.Value & """;"

rec.Open sQuery, db, adOpenKeyset, adLockOptimistic
 
 
While Not rec.EOF
  If cell.Offset(0, 8).Value <> "" Then
  cell.Offset(0, 8).Value = cell.Offset(0, 8).Value & " : " & rec.Fields("fldGreenAlt")
  Else
   '
  cell.Offset(0, 8).Value = rec.Fields("fldGreenAlt")
   cell.Offset(0, 7).Value = "Y"
  
  
  End If
  rec.MoveNext
  
  
  Wend

rec.Close


  Next cell
 On Error Resume Next <-- You can remove this because you're testing for open state before closing.
 
if rec.State=adodb.Open then
    rec.Close
end if
if db.State=adodb.Open then
    db.Close
end if
Set rec = Nothing
Set db = Nothing
End Sub

Open in new window


I also added an if statement so that you close the connection and recordset only when they are open, avoiding unwanted errors.

Let us know if it works for you.

*EDIT* Removed double "only"...
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 38338054
ERROR here ADOB error?

if rec.State=adodb.Open then  <------  method or data member notfound
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38338200
Sorry about that... It should be:

if rec.State=adodb.adStateOpen
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 38338877
Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question