Solved

ADO recordset requery

Posted on 2000-04-27
3
1,598 Views
Last Modified: 2013-11-25
I have an procedure that needs a recordset to select a different set of records in each iteration of a loop.  I am currently performing this by using the open method at the start of the loop and closing it at the end.  Unfortunately this is proving to be somewhat slow so I was wondering if there was any other way of doing this such as requery.  I am also limited to using a forward only recordset so FindFirst etc will not work.
0
Comment
Question by:justinhemphill
3 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 2755902
you mentioned requery, is it not working for you?
0
 
LVL 1

Accepted Solution

by:
lochiano earned 100 total points
ID: 2755981
The method that we are using is to place the records into an array via a DLL Com object and disconnect from the database after the records have been read.  This way all of the programs what are running have access to the same table sets without going back to the database.  Each table has it's own class, dll-Com object.




sample code from the class  (real field names have been changed}:



Public Sub GetRow(ByRef row As Long)
   '  This method places the contents of a row derived in a LoadData method call
   '  into the object to return it to the calling program
   '  No validationdone to assure that ALL columns are in the array, as it is
   '  the responsibility of the program.  We are assuming a SQL to populate the
   '  arras as follows : select * from TABLE where ...............
   '  All columns are assumed to be included.  The results will not be valid if this
   ' not the case.
   
   On Error Resume Next
        mvar(Field1) = GetDataField(maryRecords(row, 0))
        mvar(Field2) = GetDataField(maryRecords(row, 1))
 .....
    On Error GoTo 0

End Sub





Public Function RetrieveRow(row As Long, Optional Delimiter As Variant) As String

   Dim strTemp       As String
   Dim X             As Long
   
   '  Set the delimiter to vbTab if not passed
   If IsMissing(Delimiter) Then
      Delimiter = vbTab
   End If
   
   strTemp = ""
   For X = 0 To mvarArrayColCount - 1
      strTemp = strTemp & maryRecords(row, X)
      If X < mvarArrayColCount - 1 Then
         strTemp = strTemp & Delimiter
      End If
   Next
   RetrieveRow = strTemp
   
End Function





Public Property Let {Field1}(ByVal vData As Boolean)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.InsertMode = 5
    mvar{Field1} = vData
End Property

Public Property Get {Field1}() As Boolean
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.InsertMode
    {Field1} = mvar{Field1}
End Property






Public Function Find(strWhat As String) As Long
......


strSQL = "select * from (table name) where (PrincID) = '" & strWhat & "'"
   Dim rsAnchor_Detail     As ADODB.Recordset
   Set  = cnn.Execute(strSQL)
   If Not .EOF Then
      Dim RecCount      As Long
      On Error GoTo FindError
      rs.MoveFirst
      While Not rs.EOF
         RecCount = RecCount + 1
         rs.MoveNext
      Wend
      mvarRecordCount = RecCount
      rs.MoveFirst
      If RecCount > 1 Then
         Dim X, y   As Long
         mvarArrayColCount = rs.Fields.Count
         ReDim maryRecords(RecCount, mvarArrayColCount)
         rs.MoveFirst
         While Not rs.EOF
            For X = 0 To mvarArrayColCount - 1
               maryRecords(y, X) = GetDataField(rsAnchor_Detail.Fields(X))
            Next
            y = y + 1
            rs.MoveNext
         Wend
.......
0
 
LVL 1

Expert Comment

by:ca_heckler
ID: 2756262
Keeping the original schema (open/close recordset on the loop), one way to optimize things is to create a Connection object *before* the loop, open the connection, and use it inside the loop, without closing. Something like this:


   Dim oConnection As ADODB.Connection
   Dim oRS As ADODB.Recordset

   Set oConnection = CreateObject("ADODB.Connection")
   oConnection.Open tConnectionString

   Set oRS = CreateObject("ADODB.Recordset")
   Set oRS.ActiveConnection = oConnection

   while ( ... condition ... )
   
      oRS.Open tQuery

      ' ... do something

      oRS.Close

   loop


   Set oRS = Nothing
   Set oConnection = Nothing


Sorry if you already do this now ...

Claudio A. Heckler

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now