DataReport does not pick up updated values

I have an application that uses ADO to connect to an Access database and there are a number of forms which are used to update the database. The application also has a reporting subsystem which uses a DataEnviromnent and a number of DataReport objects. The problem is, my reports do not reflect the changes that are made in the updating forms until the application i closed and restarted. I create an ADO connection before each SQL statement in the forms and close it and set it to Nothing imediately afterwards to ensure that my udpates are being flushed to the database but I don't seem to have the same level of control over the DataEnvironments connection. I'm assuming that the problem is cache related, so I'm trying the flash the connections cache by using the JetEngine.refreshCache method. This works until I unload the form which calls the reports and then go back in to it at which point I get a runtime error "3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context" as I try to refresh the cache.

The relevant bits of code are as follows :
the load procedure of the entire application:
Private Sub Form_Load()
Dim db As String

I am absolutely stuck with this - I have a suspicion that if I were to get rid of the data environment altogether and use ADO recordsets then my problem would go away, but if there is an easy way to get it to work without doing all that work, I'd prefer to do it.

Usually when things like this happen, it means that your form/report is loading before you think it is. Put a breakpoint on the form_load of the report. I bet you will find the report is loaded before you make any changes to the data. Consequently locking in the data at the time of the report.

Introduction
While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library. This article will introduce you to the SC library a…

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…

Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

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…