Hello,
We currently have an Access MDB on a single-user workstation using a VB6 front end to run a report. The database has 200,000+ records which are searched by license plate number. We need to move the MDB to the network so that multiple users can run the report. The VB front end on each workstation will now point to the network-based MDB.
The report's Record Source is the name of a query (which is based on several tables/queries itself). A VB form prompts the user for a list of license plate numbers to subset the records on the report. This filtering is carried out by changing the underlying query's sql property at runtime to include the necessary WHERE clause. Docmd.OpenReport is then used to run the report. I should note that we do not use OpenReport's Where clause as it suggests the filtering takes place in the report and the underlying query would first need to return every record which has proven too time consuming.
Everything has worked fine. The big difference now is that several users must be able to open the same report CONCURRENTLY and with different filtering criteria. We are not sure how to accomplish this. If we continue the same approach and change the underlying query at runtime, it would seem that concurrent users would overlay each other's changes to the query's SQL. To prevent this we thought to copy the underlying query at runtime, assign a temp name, set it's SQL as necessary, and then run the report using that query instead. But we don't see how to redirect the report to use the new query name. Even if we could by changing the report's Record Source at runtime it would seem, here again, that concurrent users would overlay each other's changes involving that property. Our next thought was to construct the necessary SQL string in VB and open a recordset but we don't see a way to redirect the existing report to use that recordset. We feel that we must be overlooking the solution. Or does Access not support what we are trying to do. Any thoughts on how to accomplish this would surely be appreciated!
BTW, we are running Access XP.
Thanks,
Teri
We currently have an Access MDB on a single-user workstation using a VB6 front end to run a report. The database has 200,000+ records which are searched by license plate number. We need to move the MDB to the network so that multiple users can run the report. The VB front end on each workstation will now point to the network-based MDB.
The report's Record Source is the name of a query (which is based on several tables/queries itself). A VB form prompts the user for a list of license plate numbers to subset the records on the report. This filtering is carried out by changing the underlying query's sql property at runtime to include the necessary WHERE clause. Docmd.OpenReport is then used to run the report. I should note that we do not use OpenReport's Where clause as it suggests the filtering takes place in the report and the underlying query would first need to return every record which has proven too time consuming.
Everything has worked fine. The big difference now is that several users must be able to open the same report CONCURRENTLY and with different filtering criteria. We are not sure how to accomplish this. If we continue the same approach and change the underlying query at runtime, it would seem that concurrent users would overlay each other's changes to the query's SQL. To prevent this we thought to copy the underlying query at runtime, assign a temp name, set it's SQL as necessary, and then run the report using that query instead. But we don't see how to redirect the report to use the new query name. Even if we could by changing the report's Record Source at runtime it would seem, here again, that concurrent users would overlay each other's changes involving that property. Our next thought was to construct the necessary SQL string in VB and open a recordset but we don't see a way to redirect the existing report to use that recordset. We feel that we must be overlooking the solution. Or does Access not support what we are trying to do. Any thoughts on how to accomplish this would surely be appreciated!
BTW, we are running Access XP.
Thanks,
Teri