I've inherited a mess of an Access project, and am trying to make the least
change possible to fix the problem.  The problem is that multiple users of
this (unsplit) Access 2002 MDB corrupt each other's data in repeatable ways.

I've determined that several of the data-entry forms have "subforms" which
contain data from shared tables.  Thus, any user entering data in a subform
will write over any other users' data in that same subform.

Seems like a dumb design to me.  (I'm a VB/Access newbie, but have 20+ yrs
programming databases, web, assembly, C++, Java, etc.)  I have to believe
there is some Best Practice to avoid this problem, but darned if I can find
it discussed anywhere, although I've "only" looked in the on-line Access
help, microsoft's site, these forums, and elsewhere on the web.  :-/

My thoughts about possible solutions are:

1)  Back the sub-form with a "temp" table (local to the user, not in the
shared database).  Indeed some of the tables in question list themselves as
"temporary tables" but that doesn't seem to help.  I can't tell if such a
"table type" even exists.

2)  Put these "local subform" tables in the FE and delete them from the BE.  
I haven't been successful with this.  Access doesn't seem to like me deleting
and renaming/copying/pasting tables to accomplish this.

3)  Use a different mechanism in the form to create the "subform" (either
from existing data or as the user enters new rows) -- something other than a
table.  Dunno what that would be, but I know it's NOT a dynaset.

4)  Leave the subform tables global and shared, but hack a unique per-user
column into each of them.  That way they can all R/W to the same "temp" table
for the same form, but they won't be stomping all over each others' rows.  
This would require uniquely identifying each user somehow (a new feature).  
It also begs the question of when to clean up orphaned data in those temp
tables.  And it seems to require the most code changing.

= = = = = = =  Some code snippets:  = = = = = = =

The following appears to be setup for one of these forms, deleting all the
rows in one of these temp tables, and filling it with default information
(events that this client is registered for):

Public Sub subPopulateRegistrationListInfo(frm As Form)
   DoCmd.SetWarnings (False)
   DoCmd.OpenQuery "qRegisteredEvents_del"
   DoCmd.OpenQuery "qRegisteredEvents"
   DoCmd.OpenQuery "qRegisteredEventswithinForm_temp_del"
   If frm.txtFormID = "" Or IsNull(frm.txtFormID) Or IsEmpty(frm.txtFormID)
Then
       'do nothing
   Else
       DoCmd.OpenQuery "qRegisteredEventswithinForm"
   End If    
   DoCmd.SetWarnings (True)
   With frm
       .lstRegisteredEvents.RowSource = "tblRegisteredEventswithinForm_temp"
       .lstRegisteredEvents.Requery        
       .lstAvailableEvents.RowSource = "qAvailableEvents_2"
       .lstAvailableEvents.Requery
   End With
End Sub
_ _ _ _ _ _ _ _ _ _

The following appears to traverse one of these subform temp tables,
comparing the original (built from a query when the form was first displayed)
with the final version (after user adds, deletes and mods) to determine how
to update the "real" shared table:

   Set dbs = CurrentDb
   Set qdf = dbs.CreateQueryDef("")
   With qdf
       .SQL = "SELECT tblRegisteredEventswithinForm_temp.EventID " _
       & " FROM tblRegisteredEventswithinForm_temp " _
       & " WHERE (((tblRegisteredEventswithinForm_temp.EventID) Not In
(SELECT RegisteredEvents.EventID " _
       & " FROM RegisteredEvents " _
       & " WHERE (((RegisteredEvents.RegistrationID)= " & lngRegistrationID
& " ));)));"
       Set rst = .OpenRecordset()
       If rst.RecordCount >= 0 Then
           'delete registration

Unfortunately "tblRegisteredEventswithinForm_temp" is just as global as
"RegisteredEvents" is.
_ _ _ _ _ _ _ _ _ _

This appears to fill the temp table on form load:

Private Sub txtSearchField_LostFocus()
   With Me
       ' added  AND ((Events.Terminate)=False))
       .lstAvailableEvents.RowSource = "Select Events.EventID, " _
           & "Events.[Event Abreviation], Events.EventName " _
           & "FROM Events LEFT JOIN tblRegisteredEvents_temp ON " _
           & "Events.EventID = tblRegisteredEvents_temp.EventID " _
           & "WHERE (((Events.[Event Abreviation]) Like """ &
.txtSearchField & "*"")" _
           & " AND ((tblRegisteredEvents_temp.EventID) Is Null)) AND
((Events.Terminate)=False));"    
       .txtSearchField.SetFocus
   End With
End Sub
_ _ _ _ _ _ _ _ _ _

So, to recap, what I have uses shared MDB tables to back a "subform" in a
form, causing multiple users to overwrite each others' data.

My critical question is:  how can I most easily and safely (i.e. least
amount of rewriting) fix this.

A question out of curiousity is:  how SHOULD this have been done?

Thank muchly.

P.S.  Is there any way to find out which queries refer to a table -- even
something crude like "find"???