setting the form recordset in vba (for Cachesize purposes)

[belovedcej]

I have a start, but believe I am misunderstanding something, because it keeps telling me "not a valid recordset object" once it reaches the step of setting the form's recordset to my created one.

purpose - trying to speed up a very slow form which is bound and loads about 400 records at once. If I could limit the cachesize, I believe performance would greatly improve.

Could someone advice me as to my errors? Thanks! (BTW, my source is a stored procedure rather than a view, for performance reasons again.)

    Dim rst As New ADODB.Recordset
    Dim con As New ADODB.Connection
    
    con = CurrentProject.BaseConnectionString
    con.Open
    
    rst.CacheSize = 10
    rst.Open "dbo.CSF_Form_Claims_SP", con
    Set Form_frmClaims.Recordset = rst
    rst.Close
    con.Close

 

[genomon]

First of all, double check the project reference library & make sure ADO has higher precedence than any DAO.

Never saw this before:
"Set Form_frmClaims.Recordset = rst"

Here is a generic sample that works for me though:

Const conConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\DMI\DMI.mdb"
Dim conDMI As ADODB.Connection
Dim rsDMI As ADODB.Recordset

    Set conDMI = New ADODB.Connection
    With conDMI
        .CursorLocation = adUseClient
        .ConnectionString = conConnect
        .Open
    End With

    Set rsDMI = New ADODB.Recordset

    strSQL = "SELECT * FROM tblInvoiceHeaderXref ORDER BY FLD_INDEX"
    With rsDMI
        Set .ActiveConnection = conDMI
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        .Source = strSQL
        .Open
        '.MoveFirst  or whatever here
        .Close
    End With

		Set rsDMI = Nothing
		conDMI.Close
    Set conDMI = Nothing

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[belovedcej]

Thanks for that.

It's too much for now. If I understand that correctly, it would require me to add additional code as I navigate to load each record. That looks like it loads only the first record. That would be a rather major overhaul to what I already have.

I think all I really needed was this:

Me.Recordset.Cachesize = 10

I was nervous about that because when I hit the period after recordset, I did not get the drop down list of possible properties. But I'm not getting any errors and it compiles, so I'm guessing it's working.

 

[genomon]

That's cool! Just FYI: the line
'.MoveFirst or whatever here
Would be replaced by a loop with a movenext at the end to get thru the recordset, performing whatever operations are needed. But not what you needed to do here.....

I have great faith in fools; self-confidence my friends call it.
-Poe