locking problems (1 Viewer)

[knothed]

I run a macro that copies a table and runs a query and opens a report both on this temporary table. In the on close event of the report I would like to delete this temporary table but access tells me it can not lock the table because it is in use by someone else. I am currently the only user of this table. Any ideas where the error in my thinking occurs?

Thanks in advance

 

[Pat Hartman]

The table is being used by the report. That is why you cannot delete it. Move the code to a standalone function and run the function from the macro. I believe you will have to actually print the report rather than use print preview otherwise you'll still have the same problem. The print preview will be locking the table when the macro trys to delete it.

One alternative is to use a query as the recordsource for the report rather than a temp table. That way you don't have to worry about getting rid of the table. The recordset created by the query is not persistant and will be deleted when the query is closed.

A second alternative if you must use the temp table is to delete the table (if it exists) before you start, rather than at the end. This way you won't run into conflicts if you try to delete the temp table while it is still being used by the report.

 

[knothed]

Thanks Pat for you help but perhaps I didn't explain my problem very well. In our multiuser enviroment if any user has one of the many forms linked to this table open the query approach does not work because the table can not be locked. That's why I decided to do the temp table arrangment and I need the print preview option because all these reports have running sums in the footers which users need to be able to see...any other ideas?
Thanks again

 

[Richie]

Why don't you put the running sum on a form and let users see that?