Report issue - table locked

[SKV]

In order to see analytics for a small group of data from a large dataset (2M + rcds), I planned to run report with following steps

1) Create a temp table with data based on a time range (defined by user)
2) Run queries on this temp table
3) Display queries results on report

I am using a macro to run the above steps.

When I open the report, it already has information in it based on lasttime query. Now if I try to run a macro, I get an error
"The database engine could not lock table xyz because it is already in use by another person or process".
How can I avoid this prob?

 

[Norie]

Why are you creating another table?

Can't you just use a query for the record source of the report?

By the way, creating a table could actually cause a whole load of problems - have you checked the size of your database.

 

[SKV]

Norie - My main table has millions of records =which slows my queries - so I extract data from it into a table (approx 50,000 rcds) to make my queries run faster.

so you mean that if I

Why are you creating another table?

Can't you just use a query for the record source of the report?

By the way, creating a table could actually cause a whole load of problems - have you checked the size of your database.

 

[SKV]

Norie - My main table has millions of records =which slows my queries - so I extract data from it into a table (approx 50,000 rcds) to make my queries run faster.

So if I do it as per your suggestion then I have to put those filters again and again on each query. What you say.

Also can you help me in runing a query based on a value entered on the report form. I tried forms![report name].[text box name] but it doesnot seems to work. Plus I am not seeing my text box on the report view. I set the text box as unbound. Please guide.

Thanks
SKV

 

[boblarson]

When I open the report, it already has information in it based on lasttime query. Now if I try to run a macro, I get an error
"The database engine could not lock table xyz because it is already in use by another person or process".
How can I avoid this prob?

The report has to be closed in order for the query to run, if you are using a make table query. If you just delete the data and then append to it, you can have the report open and do the data change, but you would need to requery the report for the new data to show. I wouldn't open the report until the data has been completely appended.