Table opened exclusively by another user

[awhitsel]

I have an Access database installed at another facility being tested by two users at the same time.

One of the users is getting an error "The expression On Click you entered as the event property setting produced the following error: The table is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically."

Is there any way that both users can access the same table?

The default open mode is set for shared, and the default record locking is set for no locks.

 

[WildHare]

Change the default Record locking to "Edited Record". This will help but not guarantee that both users won't grab the same record.

In A2K, record-locking is 'supported' but I haven't tested it. In <A2K, 'record' locking is actually 'page' locking, where a 2K page of data is given to a user, even if there are 'other' records on the page.

The bottom line is, if you set it up correctly, >1 users can edit in a table at one time. You MAY have to implement record locking /arbitration code though. There are numerous examples of this floating around.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[awhitsel]

I had one of the users do that and here is his response:

&quot;I checked the Advanced Tab on Tools Options on both computers. They are set to default open mode is shared and default record locking is edited record. Open databases using record level locking is checked. Under DDE operations, Enable DDE refresh is checked. The error message still shows.&quot;

Any other suggestions?

 

[WildHare]

Going back and looking at your orig. post, I think the message you are getting indicates a DESIGN operation as opposed to a DATA operation. Exactly what is scheduled to happen on that CLICK event you mention?

or it is already open through the user interface and cannot be manipulated programmatically.&quot;

You can generate this same error by opening a form based on a table or query, and then trying to Edit the DESIGN of the same table.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[awhitsel]

The users are using a form that is using a temporary table as a record source.

When the user closes this form, a query runs that will transfer the data from this temporary table to a permanent table, and then clear out the data from the temporary table.

Both users are using this form at the same time.

Hope this helps.

 

[awhitsel]

Here is the code related to that form:

Private Sub cmdSaveRecord_Click()

Dim DateCheck As Date
Dim Today As Date

On Error GoTo Err_cmdSaveRecord_Click
If IsNull(txtDate) Then
MsgBox &quot;You must enter a date for this record.&quot;, vbCritical, &quot;Unable to save record&quot;
Forms!frmEnvChecksTemp!txtDate.SetFocus
Exit Sub
End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, &quot;frmEnvChecksTemp&quot;

DoCmd.OpenQuery &quot;qryAppendToENVCHK&quot;, acViewNormal
DoCmd.Close acQuery, &quot;qryAppendToENVCHK&quot;, acSaveNo
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub

 

[WildHare]

Let's see if we can clean this up a little.

1) Get rid of this line:
DoCmd.Close acForm, &quot;frmEnvChecksTemp&quot;

2) Replace the following lines:

DoCmd.OpenQuery &quot;qryAppendToENVCHK&quot;, acViewNormal
DoCmd.Close acQuery, &quot;qryAppendToENVCHK&quot;, acSaveNo
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70


With this:

Docmd.OpenQuery &quot;qryAppendToENVCHK&quot;

With an action query, there's no need to do anything but &quot;run&quot; it. Since you are shoving the results into another table, it doesn't 'open' a datasheet or anything like that.

Could find no reason for your refresh either.

HOWEVER, your problem is related to the fact that BOTH users are using the same temporary table, and BOTH users then try to CLEAR it out when they're done. This will not work. If You and I are both adding to X, and then I finish first and delete all the records in X, would you be pleased?..Do you see what Access is doing? It's making sure that one user doesn't stomp on another one.

If you place a COPY of the temporary table in each user's front end, you'll be fine.


Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[mac318]

Somewhere back when, I read that once a table is created with data sharing disabled, it will remain that way, even if, data sharing is set to true later. Future tables created will share data; however, the orginal tables will remain as set upon creation.

mac

 

[awhitsel]

JimAtTheFAA:

In one of your previous entries for this problem, you stated the following:

&quot;HOWEVER, your problem is related to the fact that BOTH users are using the same temporary table, and BOTH users then try to CLEAR it out when they're done. This will not work. If You and I are both adding to X, and then I finish first and delete all the records in X, would you be pleased?..Do you see what Access is doing? It's making sure that one user doesn't stomp on another one.&quot;

Do that mean that there is nothing that can be done to resolve this conflict? Please advise.

 

[WildHare]

If this is indeed your case, one solution (which I have used in the dim past) is to create the TEMPORARY table in the user's specific front end file, as opposed to the Back end. Then if UserA creates the table in HIS database, it doesn't stop USER B from creating one in HIS database.

Note that this solution is built around a Front End / Back End setup with each USER having a copy of the FRONT END on his PC.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com