Solved

ADO recordset requery

Posted on 2000-04-27
3
1,608 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

832 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