Run time error "table opened exclusively"

[Steven]

Note: I am using names like Form2 ; Query1 in this example for convenience.

I have a Form "Form2" whose RecordSource is "Query1". Now I want to update
records in the underlying table "Table1". "Table1" has a field UserNum which
identifies the user as records are appended. Users will only see their own
records in "Form2"

Now what happens in Form2 is that when the user clicks on "Command1" the
code is to:

Me.RecordSource = ""

DoCmd.RunSQL "Delete Table1.* FROM Table1 WHERE (((Table1.UserNum)= " &
UserIDNum & "));" 'UserIDNum is a public variable of the user when they
access the system.

After the Delete then another query is run on Table1 to appended it with a
new set of records. As the records are appended the UserNum field in Table1
is populated with the UserIdNum variable value.

Then I reset the recordsource on Form2 with:

Me.RecordSource = "Query1"


Note: I learned that I had to release "Query1" ie (Me.RecordSource = "")
above before I could do the Delete or I would received an error that the
operation could not be performed because the "Table1" was in use exclusively.

Ok: So when I set Me.RecordSource to "" and then do the operation and then
reset the recordsourse to "Query1", then it worked fine.

Now here is the problem I cannot figure out. If a second user logs in and
opens Form2 ... then the second user and the first user cannot do this at the
same time becuase I get the same Run Time Error 3008.

Question: How do I handle this? "Table1" is a table that I want to be able
to delete records and append records by multiple users and "Table1" will also
be the RecordsSource of "Form2" through "Query1"


Thank you,

Steven

 

[Piet Linden]

Note:  I am using names like Form2 ; Query1 in this example for convenience.

I have a Form "Form2" whose RecordSource is "Query1".  Now I want to update
records in the underlying table "Table1".  "Table1" has a field UserNumwhich
identifies the user as records are appended.  Users will only see theirown
records in "Form2"

Now what happens in Form2 is that when the user clicks on "Command1" the
code is to:

    Me.RecordSource = ""

    DoCmd.RunSQL "Delete Table1.* FROM Table1 WHERE (((Table1.UserNum)= " &
UserIDNum & "));"    'UserIDNum is a public variable of the user whenthey
access the system.

After the Delete then another query is run on Table1 to appended it with a
new set of records.  As the records are appended the UserNum field in Table1
is populated with the UserIdNum variable value.

Then I reset the recordsource on Form2 with:

    Me.RecordSource = "Query1"

Note:  I learned that I had to release "Query1" ie (Me.RecordSource ="")
above before I could do the Delete or I would received an error that the
operation could not be performed because the "Table1" was in use exclusively.

Ok:  So when I set Me.RecordSource to "" and then do the operation and then
reset the recordsourse to "Query1", then it worked fine.

Now here is the problem I cannot figure out.  If a second user logs in and
opens Form2 ... then the second user and the first user cannot do this atthe
same time becuase I get the same Run Time Error 3008.

Question:  How do I handle this?  "Table1" is a table that I want to be able
to delete records and append records by multiple users and "Table1" will also
be the RecordsSource of "Form2" through "Query1"

Thank you,

Steven

Do the two users have their own front ends? If not, there's your
problem. Split the front end from the back, and link to the data
tables. Then distribute the front ends to all the users. If they're
sharing the same front end, you're headed for a LOT of trouble.

 

[Steven]

Piet,

Yes, they are sharing the same front end. I will do what you say. Thank
you very much for your help.

Steven