p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   multiple users running the same report (http://p2p.wrox.com/showthread.php?t=67629)

rashi April 23rd, 2008 11:12 PM

multiple users running the same report
Hi all,

I have an application built in ms access. It is on a network and multiple users access it at the same time.
I have designed a report where the user has a 1 column data in excel which I import in access (automated and the data is imported to an existing empty table) and then run a query to get data from the database for the excel data as the input parameter. The result is then displayed to the user as a report. Imported data is deleted as soon as the report is closed.
Now, more than 1 user may at the same time, import their own data and run the report. How can I handle this so that every user gets the report with only their data.

Thanks for your help.

mmcdonal April 24th, 2008 11:16 AM

Instead of importing the data to a table, why don't you take it into a disconnected recordset, and name the recordset based on the username. Then open the report using the unique recordset.


Look it up at: http://wrox.books24x7.com

rashi April 24th, 2008 12:12 PM


Can you guide me on how to do this.


mmcdonal April 25th, 2008 07:42 AM

Okay, I used a table to get the data, but you can use whatever method you are using to pull the data locally. I would pull it into your temp table, then pull the recordset, then delete it.

I was successful in getting this to work to open a form, but I am having problems with a report:

FORM On Load Event
Dim rsTable As ADODB.Recordset
Dim sSQLTable As String
Dim sDoc As String
Const cSnap = 2

sSQLTable = "SELECT * FROM MyTable"

Set rsTable = New ADODB.Recordset
rsTable.CursorLocation = adUseClient
rsTable.LockType = adLockBatchOptimistic
rsTable.CursorType = adOpenKeyset
rsTable.Open sSQLTable, CurrentProject.Connection

Set rsTable.ActiveConnection = Nothing

Me.RecordsetType = cSnap

Set Me.Recordset = rsTable

Access 2003 VBA says this is possible using shaped recordsets, but that is a weighty issue and still has problems that may throw errors if you do everything correctly anyway.

Here is my kludge:

Create a copy of your report and then unbind it from the query you used to run it from.
Then create a local table as a template that has all the fields that you will need on your report.

Take the user name from the local computer:

sUser = (Environ$("Username"))

Then, pull in your data to the local table (one column).

Then create a new table for the user to run the report from based on a template table:

Dim sTableName As String

sTableName = sUser & "_TempTable"

DoCmd.CopyObject, sTableName, acTable, "YourTemplateTableName"

Then create an update query string to move the compiled data from the two or more tables you used to run the report from, to your sTableName table. Hint, create the update query on the template table, and then copy the sql with necessary changes to point to the sTableName table, like:

sSQL = "INSERT INTO " & sTableName & " SELECT ..."

Then execute the SQL String:


So now the data should be compiled in the user's copy of the table, and you can go back and delete whatever is in the local table with the single column of data.

On the On Close event of the form, delete the user's table:

Dim sTableName As String

sTableName = Me.RecordSource

DoCmd.DeleteObject acTable, sTableName

Did you get all of that?


Look it up at: http://wrox.books24x7.com

mmcdonal April 25th, 2008 07:44 AM

Sorry, that should be, on the On Close event of the REPORT...


Look it up at: http://wrox.books24x7.com

mmcdonal April 25th, 2008 07:48 AM

Sorry, I just went and checked deleting the recordsource on the On Close event, and that actually fires BEFORE the report is closed, and it won't delete a recordsource while it is being used. As a worst case, create the sTableName variable like this:

Public pTableName As String

pTableName = Me.RecordSource

Then you can pass the name to the next form, or a hidden intermediate form, and have the form open hidden, delete the table, and then close.

I am sure there must be a better way of doing this.


Look it up at: http://wrox.books24x7.com

All times are GMT -4. The time now is 04:09 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.