Make Table Problem With Multi Users

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

Not sure where to post this problem, but I'll give it a whirl:

I am trying to develop an application that will be used by less than 100
people.

I have the following tables:

-tblCaseInfo
-tblSurveyors
-tblCaseSurveyors
-tblCaseWitnesses
-tblWitnessCmbo

The relationships are as follows:

tblSurveyors - one to many tblCaseSurveyors - many to one tblCaseInfo
tblCaseInfo - one to many - tblCaseWitnesses

I have a macro called mcrWitnesses that runs a Make Table Query that
collects all the tblCaseSurveyors and makes the table called tblWitnessCmbo,
then the macro runs an append query that adds the tblCaseWitnesses to the
tblWitnessCmbo.

On a form I have coded a command button that runs the mcrWitnesses and opens
a report. The problem is if one user is viewing a report and another user
clicks the command button on the form, the 2nd user gets an macro error
because the first thing the macro does is deletes the table:
tblWitnessCmbo, before running the Make Table query.

Is there another way I could have done this? Or should I let it go as this
application will be used by less than 100 people and the likelihood of two
people accessing this at the exact same time is not that great? Or is there
some what to add VBA code to my command button that gives the user a message
if the tblWitnessCmbo cannot be deleted because it is in use by another user
and that they must wait and try again later? It took me ages to figure out
what I have now. I am overwhelmed at the thought of beginning again.

TIA
S.Jackson
 
Are your users opening/running a single database stored on a server? If
so, your application should be split into a Front-End (containing all
Queries, Reports, Forms, Macros and Modules) and a Back-End (containing all
shared data Tables). In addition, a copy of each Front-End should be
located on each user's local hard drive and have links to the Back-End
database. With this deployment, the Make Table queries used for reports
will be created in the Front-End database opened by each user and will not
cause conflict with another user's attempt to run the same report.
 
Well, I thought about that - Front-End and Back-End. Yes it will be split
into a Front-end and Back-end (at least I hope so), but wouldn't the users
still get a conflict when the macro executes the Delete table command
(delete tblWitnessCmbo)?

S. Jackson
 
If EACH user has their own copy, then there would be no conflicts. If each
user SHARES the frontend, then you won't accomplish anything in this regard
by splitting. However, you should split this application AND give each user
their own copy of the frontend.

A better solution, in my opinion, would be to develop an SQL statement that
would pull this information together "on the fly", without generating tables
(if possible). It seems that a Union query between tblCaseSurveyors and
tblCaseWitnesses. Check online help regarding Union queries ...

Also, I'd suggest you begin the process of learning VBA, SQL, etc etc.
Supporting 100 people on a macro driven database quickly becomes a exercise
in futility ...
 
Shelly,

Unless you specifically alter the Make Table Query's properties to write to
the back-end database, the table will be written to the user's *own* copy of
the front-end. The same principle applies for the Delete Table command in
your macro: if the table exists in the front-end database (which is
deployed to each user's hard drive), the delete will occur in the front-end.

From your previous posts, I believe you have a number of databases under
development and support. My recommendation to you is to *begin* your
development of databases with a Front-End and a Back-End. It takes no more
work to start that way than with a single, monolithic database; but it does
add more work if you start with a single DB and a "we'll split it someday"
plan. A split db with individual front-end deployment will be absolutely
transparent to your users, reduce the potential of corruption and get rid of
the type of problem you currently face.
 
Back
Top