Report shows data which was prior removed from table 
Author Message
 Report shows data which was prior removed from table

Hi!

I have a problem with Access 97. Code like:
DELETE * FROM ADRESS
INSERT INTO ADRESS VALUES (Name, City) ('Jim', 'Cologne')
is executed correctly. Then a report is opened via VBA code that shows the
contents of the table ADRESS.
What I cannot believe, but its true, the report shows old data (which
should have been removed from the table ADRESS)! If the report is still
open, I look into the table which is recordsource for the report, and the
data is different than shown is the report. The data in the table is really
correct and the report shows data which was in the table before DELETE and
INSERT.

I already tried several things like:
using a view instead of a table as recordsource
do a currentdb.tabledefs.refresh
using a transaction
inserting a doevents or even 100 doevents

Thanks in advance, Robert



Mon, 07 Mar 2005 14:35:32 GMT  
 Report shows data which was prior removed from table
Maybe you have a relationship on that table with referential integrity
enforced? If there are child records, you cannot delete the parent records -
unless you've chosen for 'cascade delete'; apparently you haven't.

--
Regards,
Bas Cost Budde, Nieuwegein, Holland
website at http://www.heuveltop.nl/BasCB/msac_index.html
Please post replies to the newsgroup so everyone can benefit

Quote:

>Hi!

>I have a problem with Access 97. Code like:
>DELETE * FROM ADRESS
>INSERT INTO ADRESS VALUES (Name, City) ('Jim', 'Cologne')
>is executed correctly. Then a report is opened via VBA code that shows the
>contents of the table ADRESS.
>What I cannot believe, but its true, the report shows old data (which
>should have been removed from the table ADRESS)! If the report is still
>open, I look into the table which is recordsource for the report, and the
>data is different than shown is the report. The data in the table is really
>correct and the report shows data which was in the table before DELETE and
>INSERT.

>I already tried several things like:
>using a view instead of a table as recordsource
>do a currentdb.tabledefs.refresh
>using a transaction
>inserting a doevents or even 100 doevents

>Thanks in advance, Robert



Mon, 07 Mar 2005 17:15:47 GMT  
 Report shows data which was prior removed from table
Definitely not. No referential integrity at all. Just a table.

The problem is, that the table has correctly the new data, but the report
shows the old data.
After closing and reopening the report the new data is shown.
I dont really know, but it could be like a transaction or cache problem.

Robert



Quote:
> Maybe you have a relationship on that table with referential integrity
> enforced? If there are child records, you cannot delete the parent
records -
> unless you've chosen for 'cascade delete'; apparently you haven't.



Tue, 08 Mar 2005 01:07:49 GMT  
 Report shows data which was prior removed from table
AWW, I didn't read your post carefully. tSchuldigung.

A report is a stupid, static document. Much unlike a table, a query, a form.
As soon as it is formatted, it is fixed. No change. No reaction.

The behavior is by design! But, you have run a DELETE query. Maybe you are
opening the report too soon after this query. Insert a DoEvents and see if
this helps.

If it doesn't: create a query with the statement, execute it and check its
RecordsAffected property (I guess that will force some synchronisation).
Like

dim qd as querydef
set qd=currentdb.createquerydef("",strSQL)
qd.execute
msgbox qd.recordsaffected
docmd.openreport "yours",acviewpreview
--
Regards,
Bas Cost Budde, Nieuwegein, Holland
website at http://www.heuveltop.nl/BasCB/msac_index.html
Please post replies to the newsgroup so everyone can benefit

Quote:

>The problem is, that the table has correctly the new data, but the report
>shows the old data.
>After closing and reopening the report the new data is shown.
>I dont really know, but it could be like a transaction or cache problem.



Tue, 08 Mar 2005 03:17:46 GMT  
 Report shows data which was prior removed from table
Did you use the DoEvents before you open the Report or after?

If after, try about 3 DoEvents *before* you open the Reports.

Alternatively, you can try using the Idle Method with the dbRefreshCache
option.

HTH
Van T. Dinh


Quote:
> Hi!

> I have a problem with Access 97. Code like:
> DELETE * FROM ADRESS
> INSERT INTO ADRESS VALUES (Name, City) ('Jim', 'Cologne')
> is executed correctly. Then a report is opened via VBA code that shows the
> contents of the table ADRESS.
> What I cannot believe, but its true, the report shows old data (which
> should have been removed from the table ADRESS)! If the report is still
> open, I look into the table which is recordsource for the report, and the
> data is different than shown is the report. The data in the table is
really
> correct and the report shows data which was in the table before DELETE and
> INSERT.

> I already tried several things like:
> using a view instead of a table as recordsource
> do a currentdb.tabledefs.refresh
> using a transaction
> inserting a doevents or even 100 doevents

> Thanks in advance, Robert



Tue, 08 Mar 2005 10:48:54 GMT  
 Report shows data which was prior removed from table
The problem was caused by a cache bug in DAO 3.51. After switching to DAO
3.6 the problem went away without code changing. This is described in
Microsoft Knowledge Base Article - Q245676
ACC2000: Records Newly Inserted into Access Database Are Not Immediately
Available When Reselecting
Cause
Internal to Jet, the seek methods used to locate the inserted records are
occasionally accessing uninitialized memory and picking up incorrect values.
Based on these incorrect values, the attempt to locate the appropriate
record fails.



Quote:
> AWW, I didn't read your post carefully. tSchuldigung.

> A report is a stupid, static document. Much unlike a table, a query, a
form.
> As soon as it is formatted, it is fixed. No change. No reaction.



Wed, 09 Mar 2005 08:53:52 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. What am I missing in Data Report SHOW?

2. What am I missing in Data Report SHOW?

3. Showing parameters pass to data environment in data report header

4. Showing parameters pass to data environment in data report header

5. Report showing sample data not the passed data!

6. Showing parameters pass to data environment in data report header

7. How to Remove a table from a report

8. I am trying to update a record, i am not using data control

9. I am trying to update a record, i am not using data control

10. Crystal Report Pro shows only 130 fields in large table

11. Preserving form data prior to mailmerge

12. showing field data from a table using a treeview control

 

 
Powered by phpBB® Forum Software