TRICKY ROADBLOCK: multi-user/linked table report issue

[Guest]

Environment: Database is on server [not front or back end - the entire
database resides there]. Users simply run local copies from their
workstations.

Rpt requirements: this is all being done in R1's Open Event Prodcedure...

a. TO RUN REPORT1 [R1]: User1 [U1] selects a Product1 [P1] from a list which
runs query1 [Q1] to collect data which creates (makes) a table [T1] in
currentDB[DB1]. Then, function1 [F1] opens T1 and does complex summations
[yes complex, not just 1+1=2..think of a limitless outlined level plan
structure [with values at all the children levels which must be rolled up to
its respective parent, which in turn those parents are rolled up to other
parents, and so on...] and writes back to T1 with those rolled-up summations.
Then Q2 is based on T1, and R1's record source is Q2.

b. U2,U3,U4... must be able to concurrently run R1, choosing P2,P3,P4...

PROBLEM: in code on R1's OPEN event, I am currently creating a DB2 on each
Ux's C:\ drive; transferring T1 from DB1 into DB2 and then linking T1 in DB1
to T1 in Db2. This DOES allow multiple users to run their own reports on
different products and runs just fine...IF...

BIG IF: on U2,U3,U4,Ux's... local copy of DB1, I have to MANUALLY run the
"make-table" query Q1 on their machine. When doing this, Access doesn't seem
to care if the "linked table" T1 exists or not - it simply overwrites the
table's data with the new Px data, then runs F1, writes back to T1, creates
DBx, links T1 and runs R1 just fine. However, if I try to run Q1 as a sql
string in code [DoCmd.RunSql] or use [DB1.Execute] Access throws an error
stating ["T1 already exists"].

well, I can just simply delete the persistent and linked T1 b/c I get
another Access gripe that the table is in use by another Ux. What are my
alternatives? design and or code? Please respond at your earliest
convenience: (e-mail address removed) or (e-mail address removed) or reply here
of course!

 

[SA]

Well, you've found why you normally split a db into front end / back end in
most multi user environments. i.e. one user overwriting anothers data.

But more to the point. If you are doing this in code (i.e. the make table
query) and the table already exists, you will get an error thrown. You need
to delete/drop the existing table before you can create it again. This will
occur if the same user runs the scenario twice as well. You can add a
little error handling to your routine so that if the error occurs where the
table exists, in your error handler, run code to drop the table and then
resume from where the error occured to recreate it.