Make Table Problem With Multi Users

[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

 

[Cheryl Fischer]

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.

 

[S Jackson]

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

 

[Scott McDaniel]

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 ...

 

[Cheryl Fischer]

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.

 

[S Jackson]

Well thank you for your response Scott. Yes I have begun the process of
learning VBA and SQL. The database is not macro driven at all - just the
instance I described in my post.

Thanks for your help.
S. Jackson

 

[S Jackson]

Thanks Cheryl. No, I am only working on the one database - still (crazy,
eh?)! We have not deployed it yet and we are meeting next week to discuss
how in the world we are going to do this. Unfortunately, we do not have IT
support so its a massive undertaking for myself and one other person who
have absolutely no training (I love working for the state - heavy sigh).

Thank you for your advice on developing databases with a FrontEnd and
BackEnd right from the beginning. Question: should I split the database I
am currently working on if I am not finished with it? It is about 95%
finished at this point.

Thanks again, Shelly

 

[Cheryl Fischer]

Question: should I split the database I

am currently working on if I am not finished with it? It is about 95%
finished at this point.

At some point, I believe you are going to have to split it - so why not now
while the application is still in development/text mode?


hth,

 

[John Vinson]

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.

100 people concurrently updating is a LOT of people for Access. If
they're reading it's probably ok with good design.

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.

OUCH!!!!

That's going ALL around Robin's barn. You really don't need an
expensive, slow, database-bloating and user-conflicting make-table
query to do this!

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.
Yep.

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?

Well, you've already demonstrated that it's too great; it's happening
and preventing people from running this report.

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.

I would suggest doing away with tblWitnessCmbo entirely, and basing
the Combo Box on a UNION query. This query can pull all the names from
tblCaseSurveyors and from tblCaseWitnesses into a single recordset,
dynamically; the Combo based on this query will show all the names.
There's no need to collect the names into a Table just to see them.

 

[John Vinson]

Just wanted to chime in again to EMPHATICALLY agree with Cheryl's
suggestions about splitting the database. Do it now!

 

[S Jackson]

Thanks everyone!

I didn't know about UNION queries. Off to read up on them!

S. Jackson