Solved

ADO recordset requery

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Delphi Yen format 3 45
Dev express lookupcombo 3 34
Set email body to html using vbscript 6 49
VBA: copy range dynamically based on config sheet v2 3 48
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…

809 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