BobRosas
asked on
You tried to lock table while opening it
I'm using Access 2007 frontend and SQL Server 2005 backend.
I have been getting this message periodically for a while now...
"You tried to lock table 'tblMTDaily'while opening it, but the table caonnot be locked because it is
currently in use. Wait a moment, and then try the operation again."
and I have not been able to figure out why. tblMTDaily is created by running qryMTDailyQC
Each time the user had the problem I was unable to recreate it so I just keeping trying to find more places to close my table.
Now I'm finally able to get the same message but only if I run the stripped out code(accde)version. If I run the full version (accdb) I don't get the error. No matter what report I select, if I click on the button cmdPrvDaily_Click I get the message. The users have tried rebooting their pc's but the only way to get rid of the message is to reboot the server. Then they will be fine for awhile.
Please help!
I have been getting this message periodically for a while now...
"You tried to lock table 'tblMTDaily'while opening it, but the table caonnot be locked because it is
currently in use. Wait a moment, and then try the operation again."
and I have not been able to figure out why. tblMTDaily is created by running qryMTDailyQC
Each time the user had the problem I was unable to recreate it so I just keeping trying to find more places to close my table.
Now I'm finally able to get the same message but only if I run the stripped out code(accde)version. If I run the full version (accdb) I don't get the error. No matter what report I select, if I click on the button cmdPrvDaily_Click I get the message. The users have tried rebooting their pc's but the only way to get rid of the message is to reboot the server. Then they will be fine for awhile.
Please help!
Private Sub cmdPrvDaily_Click()
On Error GoTo Err_cmdPrvDaily_Click
DoCmd.Close acReport, "rptDaily"
DoCmd.Close acReport, "rptFDT"
If IsNull(cboQCRptNo) And cboReleaseNo = "" Then
MsgBox "QC Report Number, Release No or Dates are required."
Exit Sub
End If
If IsNull(cboToDate) And Not IsNull(cboFmDate) Then
MsgBox "Enter a 'To Date' AND a 'From Date' or leave them both blank."
Exit Sub
End If
If Not IsNull(cboToDate) And IsNull(cboFmDate) Then
MsgBox "Enter a 'To Date' AND a 'From Date' or leave them both blank."
Exit Sub
End If
If IsNull(cboQCRptNo) Then
'ReleaseNo Only
If IsNull(cboToDate) And IsNull(cboFmDate) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTDailyWR"
txtRcdSrc = "SELECT tblMTDaily.*, tblDaily.Description FROM tblMTDaily LEFT JOIN tblDaily ON tblMTDaily.DailyId = tblDaily.DailyId"
DoCmd.SetWarnings True
Else
'Date Only
If IsNull(cboReleaseNo) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTDailyDate"
txtRcdSrc = "SELECT tblMTDaily.*, tblDaily.Description FROM tblMTDaily LEFT JOIN tblDaily ON tblMTDaily.DailyId = tblDaily.DailyId"
DoCmd.SetWarnings True
Else
'ReleaseNo & Date
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTDailyWRDate"
txtRcdSrc = "SELECT tblMTDaily.*, tblDaily.Description FROM tblMTDaily LEFT JOIN tblDaily ON tblMTDaily.DailyId = tblDaily.DailyId"
DoCmd.SetWarnings True
End If
End If
Else
'QCRptNo Only
If IsNull(cboFmDate) And IsNull(cboToDate) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTDailyQC"
txtRcdSrc = "SELECT tblMTDaily.*, tblDaily.Description FROM tblMTDaily LEFT JOIN tblDaily ON tblMTDaily.DailyId = tblDaily.DailyId"
DoCmd.SetWarnings True
End If
End If
DoCmd.SetWarnings False
If strNoData = "Y" Then
MsgBox "Report is Empty."
Else
Call CalcRoomNo
Call RoomsByVersion
DoCmd.OpenReport "rptDaily", acPreview 'displays report
End If
DoCmd.SetWarnings True
Exit_cmdPrvDaily_Click:
Exit Sub
Err_cmdPrvDaily_Click:
If Err = 3211 Then
rs2.Close 'The database engine could not lock table 'tblMTDaily' because it is already in use by another person or process.
Resume
End If
If Err = 3021 Then
rs2.Close 'No current Record, then make sure to close db or next run gets error.
End If
If Err = 2501 Then 'The open report action was canceled.
Exit Sub
Else
MsgBox Err.Description
Resume Exit_cmdPrvDaily_Click
End If
End Sub
Private Sub CalcRoomNo()
On Error GoTo Err_Report_Load
'Each Test Performed can be done on more than 1 room. The following code cycles through the code
'and saves all room numbers that correspond to a test performed into 1 record, then saves it to a make table.
strTestDescription = ""
strTestRoomDesc = ""
strRoomNo = ""
DoCmd.SetWarnings False
'Date Only
If IsNull(cboReleaseNo) And IsNull(cboQCRptNo) Then
Set qdf = CurrentDb.QueryDefs("qryTestRoomsDate")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate]
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate]
DoCmd.OpenQuery "qryMTTestRoomsDaily"
Else
'QCRptNo Only
If IsNull(cboReleaseNo) Then
Set qdf = CurrentDb.QueryDefs("qryTestRooms")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboQCRptNo]") = [Forms]![frmPrevDailyDFT]![cboQCRptNo]
DoCmd.OpenQuery "qryMTTestRooms"
Else
'ReleaseNo Only
If IsNull(cboFmDate) And IsNull(cboToDate) Then
Set qdf = CurrentDb.QueryDefs("qryTestRoomsRel")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![txtReleaseNo]") = [Forms]![frmPrevDailyDFT]![txtReleaseNo]
DoCmd.OpenQuery "qryMTTestRoomsRel"
Else
'ReleaseNo & Date
Set qdf = CurrentDb.QueryDefs("qryTestRoomsRelDate")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![txtReleaseNo]") = [Forms]![frmPrevDailyDFT]![txtReleaseNo]
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate]
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate]
DoCmd.OpenQuery "qryMTTestRoomsRelDate"
End If
End If
End If
DoCmd.SetWarnings True
Set rs = qdf.OpenRecordset
Set rs2 = CurrentDb.OpenRecordset("tblMTTestRooms", DB_OPEN_DYNASET)
With rs
Do While Not .EOF
strVersionId = rs!VersionId 'this saves off version
strTestDescription = rs!TestDescription 'this saves off test description
strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works
strRoomNo = strRoomNo & rs!RoomNo & ", "
rs.MoveNext
strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works
strVersionIdCur = rs!VersionId
If strTestDescription <> strTestDescriptionCur Or strVersionId <> strVersionIdCur Then
With rs2
.Edit
!RoomNo = Left$(strRoomNo, Len(strRoomNo) - 2) 'deletes trailing comma
.Update
rs2.MoveNext
strRoomNo = ""
End With
Else
End If
Loop
End With
rs.Close
rs2.Close
Err_Report_Load:
If Err = 3021 Then 'No Current Record"
With rs2
.Edit
!RoomNo = Left$(strRoomNo, Len(strRoomNo) - 2) 'deletes trailing comma
.Update
[Forms]![frmPrevDailyDFT]![cboQCRptNo] = !QCReportNo 'w/o this reports by ReleaseNo & Date don't print correctly
End With
rs.Close
rs2.Close
Else
MsgBox Err.Description
End If
End Sub
Private Sub RoomsByVersion()
On Error GoTo Err_RoomsByVersion
'this code creates one field with all the room numbers from this QC No.
txtRoomNoAll = ""
'Date Only
If IsNull(cboReleaseNo) And IsNull(cboQCRptNo) Then
Set qdf = CurrentDb.QueryDefs("qryQCRoomsDate")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate] '1st parameter
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate] '2nd parameter
Else
'QCRptNo Only
If IsNull([Forms]![frmPrevDailyDFT]![cboReleaseNo]) Then
Set qdf = CurrentDb.QueryDefs("qryQCRooms")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboQCRptNo]") = [Forms]![frmPrevDailyDFT]![cboQCRptNo]
Else
'ReleaseNo & Date
Set qdf = CurrentDb.QueryDefs("qryRoomsRelDate")
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboReleaseNo]") = [Forms]![frmPrevDailyDFT]![cboReleaseNo] '1st parameter
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate] '2nd parameter
qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate] '3rd parameter
End If
End If
Set rs = qdf.OpenRecordset
Set rs2 = CurrentDb.OpenRecordset("tblMTDaily", DB_OPEN_DYNASET)
With rs
strVersionId = Nz(rs2!VersionId)
Do While Not .EOF
If strVersionId = rs!VersionId Then
txtRoomNoAll = txtRoomNoAll & rs!RoomNo & ", "
.MoveNext
Else
With rs2
.Edit
!RoomByVrsn = Left$(txtRoomNoAll, Len(txtRoomNoAll) - 2)
.Update
.MoveNext
End With
strVersionId = rs!VersionId
txtRoomNoAll = "" 'reset value to null for each version Id
End If
Loop
With rs2
If txtRoomNoAll = "" Then 'no rooms were entered so skip update
Else
.Edit
!RoomByVrsn = Left$(txtRoomNoAll, Len(txtRoomNoAll) - 2)
.Update
.MoveNext
End If
End With
txtRoomNoAll = ""
End With
rs.Close
rs2.Close
Exit_RoomsByVersion:
Exit Sub
Err_RoomsByVersion:
If Err = 0 Then 'if table is empty code jumps here from "Do while Not .eof" so skip message
'rs2.Close 'with close statement get error "Object invalid or no longer set"
Exit Sub
End If
If Err = 3021 Then 'No Current Record"
With rs2
.Edit
!RoomNo = Left$(strRoomNo, Len(strRoomNo) - 2) 'deletes trailing comma
.Update
[Forms]![frmPrevDailyDFT]![cboQCRptNo] = !QCReportNo ''w/o this reports by ReleaseNo & Date don't print correctly
End With
rs.Close
rs2.Close
Else
MsgBox Err.Description
Resume Exit_RoomsByVersion
End If
End Sub
Have you considered it might be time to reanalize and redesign your app to take advantage of MS SQL Server? Unfortunately, coding against SQL Server in much the same way as you did MS Access is going to lead to this type of problem. Every time you query SQL Server, all you are doing is retrieving all the records to the client and if on top of that you select DB_OPEN_DYNASET you have in effect locked all those rows. Until you are prepared to use modern coding techniques you may be just better of sticking with MS Access as a backend database.
Sorry for the bad news.
Sorry for the bad news.
ASKER
I appreciate your input, even if the news is bad. Since I'm new to SQL Server this is the new version and my first SQL project. I just don't know how to make it efficient. Any specific coding examples on what I should be replacing my existing code with would really help me learn and be able to make changes.
Thanks again..I really do apprecaite it.
Thanks again..I really do apprecaite it.
You will have to start from scratch and re-analyze your whole app. You should no longer use DAO, but rather use ADO (if you still want to use MS Access as a front-end). You should never navigate through the rows at the client level in order to update specific rows, but rather use SQL INSERT/UPDATE/DELETE queries to do this. I could go on and on, but I think you get the picture.
I hope you find someone who can show you a patch or workaround for your current problem, if not you may have to roll-up your sleaves.
Good luck.
I hope you find someone who can show you a patch or workaround for your current problem, if not you may have to roll-up your sleaves.
Good luck.
ASKER
Because of time constraints I will leave this question open for awhlle in hopes someone can at least give me a patch/work around. Starting from scratch isn't going to help at this point if I don't know ADO and SQL well enough to change my code. If you know where I can get any coding examples for furture reference that would be great.
Thanks again.
Thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for your input. In answer to your questions, this database was created and designed in Access 2007.
1. It does compile as an accdb
2. Under Access Options for Current Database I have Compact/Repair set to run on Close. I also did it manually before I created an accde.
Do you think having it compile on close is a problem?
3. I will try importing into a new database and then create a new accde.
4. I will check to make sure they have the latest Service Packs and Hot Fixes for both Office and Windows.
The last time I thought I fixed the problem it worked fine for a week before they got the error again. So I will close this question and repost as needed.
If you know where I can find some simple ADO code examples or even an on-line tutorial on converting that would obviously be of great service to me.
Thanks again.
1. It does compile as an accdb
2. Under Access Options for Current Database I have Compact/Repair set to run on Close. I also did it manually before I created an accde.
Do you think having it compile on close is a problem?
3. I will try importing into a new database and then create a new accde.
4. I will check to make sure they have the latest Service Packs and Hot Fixes for both Office and Windows.
The last time I thought I fixed the problem it worked fine for a week before they got the error again. So I will close this question and repost as needed.
If you know where I can find some simple ADO code examples or even an on-line tutorial on converting that would obviously be of great service to me.
Thanks again.
BobRosas,
There was no rush to close the question, you could have left it open until you made a determination.
This is so you do not accept a question that did not solve the issue.
FYI, you technically have 21 days to close a question.
;-)
JeffCoachman
There was no rush to close the question, you could have left it open until you made a determination.
This is so you do not accept a question that did not solve the issue.
FYI, you technically have 21 days to close a question.
;-)
JeffCoachman
ASKER
21 days...Good to know!
Thanks
Thanks
ASKER
Open in new window