Running an Access/VB report in a multiuser environment

[teriw]

Hello,

I was not sure where best to post this.

We have an Access MDB on a single-user workstation and use 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 several 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

 

[DCrake]

Do not make duplicate posts. It will get seen eventually.

 

[DCrake]

What you need to do is to have a local mdb on each users machine that has linked tables back to the server. These "Side ends" will contain local queries that the user points to and you change the query sql there. The master queries will be held in a master side end that no one has connections to and used to do the development in.

From you vb6 front end you will set up 2 dbs's

Dim sMdb as DAO.Database 'Local side end
Dim lMdb As DAO.Database 'Remote server back end

Set lMdb = OpenDatabase(App.Path & "\Local.Mdb")
Set sMdb = OpenDatabase(\\server path\Server.Mdb")

When you want to common tables you redirect the code to the server mdb. Conversely when you want to write custom queries you point to the local mdb.
This way users will not be tripping up over each other and of course there is less network traffic.

 

[teriw]

Thanks. I have some questions on using this approach.

1. How do we point the the report to use the LOCAL query as its underlying record source? I ask that with the assumption there will be just ONE version of the report that is located on the SERVER mbd only (otherwise we couldn't modify the report layout without visiting every workstation).

2. Our report's underlying query is quite complex. It is based on 8 other queries which are joined together in the final query used for the report. There are probably another 16 queries that "feed" those and only the lowest level queries are actually based on Tables. To use this approach must we replicate all the queries into the LOCAL mdb on every workstation?

3. There are 10 underlying tables involved in our report. It sounds like the LOCAL mdb needs to link each of those tables, but many of those tables are already linked ODBC tables and are not available for linking to the LOCAL mdb.

Thanks,
Teri

 

[DCrake]

My suggestion was a theoretical approach, the complexity of your application suggests that this is not a preferred method.That's the problem with VB and using Access to print out reports in a common back end in a multi user setup.

It is similar to having a mdb font end on a server and everyone loggin into it. Have you actually tried the concurrency test to see what happens?