Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:43 PM.


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