|
Subject:
|
multiple users running the same report
|
|
Posted By:
|
rashi
|
Post Date:
|
4/23/2008 11:12:53 PM
|
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.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/24/2008 11:16:14 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.
mmcdonal
Look it up at: http://wrox.books24x7.com
|
|
Reply By:
|
rashi
|
Reply Date:
|
4/24/2008 12:12:51 PM
|
mmcdonal,
Can you guide me on how to do this.
Thanks
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/25/2008 7:42:30 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:
DoCmd.RunSQL sSQL
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?
mmcdonal
Look it up at: http://wrox.books24x7.com
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/25/2008 7:44:33 AM
|
Sorry, that should be, on the On Close event of the REPORT...
mmcdonal
Look it up at: http://wrox.books24x7.com
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/25/2008 7:48:23 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.
mmcdonal
Look it up at: http://wrox.books24x7.com
|
