Post #350,087
11/3/11 12:32:07 PM
|
MS Access question
I am trying to make Access multi-user. But I cannot figure out how to generate "answer" tables without another user blowing them away.
example: Query to show all employees with a given salary.
If user 1 selects the query and enters 1000 and User 2 enters 2000, who ever hits ENTER last is the one with the correct answer table (No, my queries are not that simple. I cannot just display the query results because the queries are too complex for Access to handle - I need to use intermediate temporary tables)
No, I am not a Access programmer. Self taught for this project, using the hit or miss approach.
This is so very simple in Paradox. Too bad Paradox is not allowed at this client.
TIA,
Joe
|
Post #350,099
11/3/11 4:32:21 PM
|
No personal experience.
Hi Joe,
I hope things are going well with you and yours.
Unfortunately, I can't really offer any help other than this StackOverflow page I found. I hope it sets you on a correct path to getting the answer.
http://stackoverflow...multi-user-access
Good luck!
Cheers,
Scott.
|
Post #350,102
11/3/11 5:48:41 PM
|
Access isn't really multiuser
You can have multiple ppl accessing single tables...but it doesn't handle concurrent use well.
Sure, understanding today's complex world of the future is a little like having bees live in your head. But...there they are.
|
Post #350,132
11/4/11 7:28:11 AM
|
Doesn't it have SOME piss poor locking?
I seem to recall when our Access database use hit 3 or 4 people, we then had to push the tables out to SQL server and rewrite the access (fake sql->real sql) code.
But this was years ago, and someone else's project.
|
Post #350,134
11/4/11 10:00:46 AM
|
That's still a good use for it
If you put Access on the desktop with read access to SQL Server tables, it's great for letting people do their own ad-hoc reporting.
You can give write access, too, but you start getting back into the concurrency issues ... which are still not as bad as just sharing the .mdb.
--
Drew
|
Post #350,103
11/3/11 5:56:21 PM
|
Re: MS Access question
A couple of ideas:
1) add a user field to the records in the temp table.
2) name the temp tables after the user
3) separate your front end .mdb(code, queries, reports, etc) from the back end (shared tables) and create the temp tables in each user's front end. Then use linked tables to tie the two together.
4) put the temp tables in a separate .mdb organized by one of the above methods.
3 and possibly 4 are probably your best bets. You'll want to account for the need for occasional db compaction wherever those temp tables end up too.
|
Post #350,112
11/3/11 9:16:14 PM
|
3 is the way
But do you really need the results as tables? It is not that the users can take off with them as they would with Excel files unless the contents are exported. A view/query resultset would not have the problems you describe and the results can be exported as well.
Of course, Access across a network is always fun. Better not have a T1 in there...
|
Post #350,122
11/3/11 10:24:35 PM
|
Yep
But like you said, once you get to the point where the application requires splitting the MDB up, you're using the wrong tool for the job.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
|
Post #350,106
11/3/11 7:48:42 PM
|
Haven't played with Access in a long time
But it sounds like you're generating tables rather than result sets.
A query to get 1000 would (hopefully) generate a result set (that looks a lot like a table, but it temporary and only tied to that user).
|
Post #350,123
11/3/11 10:35:51 PM
|
Using temp tables
If your using a recent version of MS Access you can use SQL temp tables if you setup your queries as stored procedures. See http://support.microsoft.com/kb/287728. Each user should get their own set of temp tables, but I have never tried it myself.
Jay
|
Post #350,133
11/4/11 7:46:06 AM
|
Additional information
Thanks all for the suggestions. Will try everything/anything.
The app is a tool to provide information about the mainframe system.
The user enters the name of a program. The app needs to show all the Procs and JCL that call the Program. The result would look like this:
PrgA ProcA JCLA
PrgA ProcA JCLB
PrgA ProcB JCLA
prgA ProcC JCLD
PrgA no proc JCLE
PrgA ProcF No JCL
Looks easy enough, but sometime procs call procs, so I need to be recursive in my queries. Sometimes there is no proc, the JCL executes the program directly. Other times I have orphaned procs with no JCL.
So Q1 -> Q2 -> Q3
and Q4 -> Q5 -> Q6
Q7 Merges Q3 & Q6
Q8 -> Q9
Q10 merges Q7 & Q9
Display result
As long as each individual query is not too complex this works. However, once the queries start getting complex, Access regurgitates with "TILT!" (or something like that).
That's why I need to create intermediate tables attached to a specific user.
This application was initially written for a group of 6 user, using in occasionally. Now it has been deployed to the entire development staff (100 users) who are in it all day long. Yeah, is should be rewritten in something else, but due to budget issues that probably won't happen. The corp has looked at other various off-the-shelf products to replace this app, but nothing offers the flexibility that I have in mine, so the users keep rejecting the canned programs.
Most likely choice for a rewrite would be to migrate it to Mainframe Linux and rewrite it in Oracle. (Can only use tools authorized by corp!)
|
