Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2008, 11:12 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.
 
Old April 24th, 2008, 11:16 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 24th, 2008, 12:12 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mmcdonal,

Can you guide me on how to do this.

Thanks
 
Old April 25th, 2008, 07:42 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 25th, 2008, 07:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old April 25th, 2008, 07:48 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Users logging on feets Access 6 November 14th, 2007 11:11 AM
Multiple Users SpyderSL Access 1 December 20th, 2006 08:40 AM
Record sharing by multiple users demivolt Access 1 August 26th, 2004 08:17 PM
┬┐Record Locks? Concurrency Multiple Users p_nut33 ADO.NET 0 November 18th, 2003 10:02 AM





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