Recordset update delay

[petevick]

I wonder if anyone has the answer....
I change a recordset via text fields in a form, I then display a VB report based on that recordset, the report does not show the chages made, if I introduce a half second delay before showing the report then the changes are displayed. I was already using an adoc1.recordset.update prior to showing the report. Is there a way to get the changes written back a bit quicker?.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[CCLINT]

Use the same connection for the report, as used for the recordset.

Set Adodc2.Recordset.ActiveConnection = Adodc1.Recordset.ActiveConnection

Or, use JRO to refresh the cache.

 

[petevick]

Thanks for for the reply CCLINT, I'll try them later, I think in my situation the JRO approach might be the best bet, could that used as in...

JRO.RefreshCache adoc1.Recordset.ActiveConnection

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[petevick]

I've tried the following piece of code, but without success, can anyone see the problem ??

    Dim jro As jro.JetEngine
    Set jro = New jro.JetEngine
   
    If Adoc1.Recordset.RecordCount > 0 Then Adoc1.Recordset.Update

    jro.RefreshCache Adoc1.Recordset.ActiveConnection

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[CCLINT]

Refreash instead the connection being used for the reports...

 

[petevick]

I'm already doing that CCLINT, I'm also SETting the reports data scource and refreshing prior to showing. Seems the only way is the half second delay. All the info on RefreshCache seems to indicate that it should work...ah um.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[CCLINT]

Seems to have always worked for me.
There are some other methods, such as reducing the cache refresh time which I have covered it several times in this forum.

Set the ActiveConnections to use the same connection and see what that brings.

 

[petevick]

Nope, already tried setting the ActiveConnections. It must be something I'm not doing/have done that is preventing JRO from not working, I'll keep working on it.....thanks for your help on this subject CCLINT.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[CCLINT]

Please post the ConnectionString, CursorLocation and Cursor type values.

Also, is the report's connection being made AFTER the Update?

If not, then you will need to refresh this first (naturally)

 

[petevick]

Its occurred to me that maybe my problem is down to the fact that I am using adodc controls, not adodb objects, I'm using two different adodc controls for the form and the report, could that be it???

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[CCLINT]

1. That's why I said to also refresh the cache of the REPORT's connection.

2. If you are setting the report's ADODC rowsource, BEFORE the update, then you will need to refresh prior to previewing it.

3. Best is to use the SAME recordset object for both, and just remove any property settings you have made in CODE and the connection and RoeSource properties in the property window. Then you can use this:

Set AdodcRPT.Recordset = AdodcFORM.Recordset

Then they are using the exact same recordset!

 

[petevick]

But the report is showing a single record of the forms recordset, so, after updating the forms adodc control, I set the adodc record source that I use for the report using an SQL statement as in;

    With rptAdodc
        .ConnectionString = ConStr ' global for all adodc's
        .RecordSource = prtSQL ' SQL string
        .Refresh
        .Recordset.MoveFirst
    End With

The forms adodc is updated and the cache is refreshed using

jroDB.RefreshCache frmAdodc.Recordset.ActiveConnection

all prior to setting the reports adodc properties and showing the report.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[petevick]

Well, for the time being, I have figured out the following work around, its not elegant, but it works...

    With Adoc1.Recordset
        If .RecordCount > 0 Then
            .Update
            bm = .Bookmark
            .Requery
            .Bookmark = bm
        End If
    End With

Its certainly quicker than the half second delay I was using originally. I will eventually dump all the adodc controls in favour of one common adodb object connection and record set objects, as you rightly suggest CCLINT.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk