database has been locked by admin

[tweetyng]

hello,
i have a problem using access 2003 and SQLServer. using DOBC. If i'm using the mdb by myself then it's working fine. However, this is a shared mdb by network, so more than 2 people can open the database at the same time. All other forms are working except 1 form after i hit a button then another user can not open the database and got the message "the database has been placed in a state by user "Admin" on machine "ADDD" that prevents it from being opened or locked"...Please help
Code below after hit the button:


Private Sub cmdAdd_Click()

Dim rst As Recordset
Dim wsp As Workspace
Dim ID As Integer

On Error GoTo Err_cmdAdd_Click
DoCmd.Hourglass True

Me.Refresh

Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans

Set rst = CurrentDb.OpenRecordset("TableA")
ID = 1

With rst
For ID = 1 To 10
.AddNew
![ID_NO] = Me.txtID_NO
![TDC] = ID
![OptSelect] = "2"
.Update
Next ID
.Close
End With

Set rst = Nothing
wsp.CommitTrans

Me.Refresh
Me.TabCtlDetail.Visible = True
Me.txtCode.SetFocus
Me.cmdAddViolation.Enabled = False


DoCmd.Hourglass False

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
DoCmd.Hourglass False
MsgBox Err.Description
wsp.Rollback
Resume Exit_cmdAdd_Click

End Sub

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[Hmadyson]

They suggest that each person has a copy of the front end database on their pc, and then there is a backend database with just the data for this very reason. Whenever you make changes to forms or reports or modules in Access, it will lock up the other users. If each person had a copy of a front end database, they would never lock up the database for anyone else since the data is not what is locked, but the objects itself.

Please let me know if I can provide more assistance.