Jeff Heilman
asked on
MS Access pass through query and SQL stored procedure stops working properly after a day or two.
Hello!
I have a very strange problem I have never run into before. I am using Access 2013 front end and SQL Server 2008 on the back. The application is run on Dell Venue tablets that are running Windows 8.1.
This code works perfectly for a day or two and then for some reason it stops working properly. All of the fields are written correctly to dbo.tblQMSAuditMaster except for the Dept field, which will start to select a random value from the Dept list box on the form. The Dept list box is populated by a simple select query.
The weird thing is if you delete the .accdr file on the tablet and reinstall it, it will work perfectly for a day or two before it starts to act up again. If this is a coincidence or actually fixing the problem I do not know.
I've had several people far smarter than myself that I know go over this code and none of us can figure out what is going on. I really hope some of you have ideas that I can try. I have read that network congestion can sometimes hose things up but could find no examples that are similar to this.
Here is the code for the sproc:
The code for the pass-through query:
And finally the code for cmdStartAudit button:
Thank you for your help. Have a great day!
I have a very strange problem I have never run into before. I am using Access 2013 front end and SQL Server 2008 on the back. The application is run on Dell Venue tablets that are running Windows 8.1.
This code works perfectly for a day or two and then for some reason it stops working properly. All of the fields are written correctly to dbo.tblQMSAuditMaster except for the Dept field, which will start to select a random value from the Dept list box on the form. The Dept list box is populated by a simple select query.
The weird thing is if you delete the .accdr file on the tablet and reinstall it, it will work perfectly for a day or two before it starts to act up again. If this is a coincidence or actually fixing the problem I do not know.
I've had several people far smarter than myself that I know go over this code and none of us can figure out what is going on. I really hope some of you have ideas that I can try. I have read that network congestion can sometimes hose things up but could find no examples that are similar to this.
Here is the code for the sproc:
USE [DB1XXX]
GO
/****** Object: StoredProcedure [dbo].[AuditInsert] Script Date: 12/16/2015 10:27:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AuditInsert] (
@strPlant AS varchar(50),
@strDept AS varchar(50),
@strArea AS varchar(50),
@strShift AS int,
@strAuditor AS varchar(50),
@strSeries AS varchar(50),
@strUnitType AS varchar(20)
)
AS
BEGIN
DECLARE @FirstAuditMasterID int, @AuditMasterID int
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
SELECT @FirstAuditMasterID = MAX(AuditMasterID) FROM tblQMSAuditMaster
INSERT INTO dbo.tblQMSAuditMaster (Plant, Dept, Area, Shift, Auditor, Series, UnitType)
VALUES (@strPlant, @strDept, @strArea, @strShift, @strAuditor, @strSeries, @strUnitType);
SELECT @AuditMasterID = MAX(AuditMasterID) FROM tblQMSAuditMaster
SELECT @FirstAuditMasterID = @FirstAuditMasterID + 1
IF @AuditMasterID <> @FirstAuditMasterID
SELECT @AuditMasterID = @FirstAuditMasterID
Select @AuditMasterID as ScopeID;
INSERT INTO dbo.tblQMSAudits (Series, UnitType, IPoint, Task, AuditMasterID, InspectID, MatrixID)
SELECT @strSeries, UnitType, IPoint, Inspect, @AuditMasterID, InspectID, MatrixID
FROM dbo.tblQMSAuditMatrix
WHERE UnitType = @strUnitType AND IPoint = @strArea;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
RETURN -1;
END CATCH;
RETURN 0;
END
The code for the pass-through query:
EXECUTE dbo.AuditInsert @strPlant = N'{1}', @strDept = N'{2}', @strArea = N'{3}', @strShift = N'{4}', @strAuditor = N'{5}', @strSeries = N'{6}', @strUnitType = N'{7}'
And finally the code for cmdStartAudit button:
Private Sub cmdStartAudit_Click()
If IsNull(Me!Dept) Or Me!Dept = "" Then
MsgBox "You must select a department.", vbOKOnly, "Required Data"
Me!Dept.SetFocus
Exit Sub
End If
If IsNull(Me!UnitType) Or Me!UnitType = "" Then
MsgBox "You must select a Unit Type.", vbOKOnly, "Required Data"
Me!UnitType.SetFocus
Exit Sub
End If
If IsNull(Me!Series) Or Me!Series = "" Then
MsgBox "You must select a Series.", vbOKOnly, "Required Data"
Me!Series.SetFocus
Exit Sub
End If
If IsNull(Me!Area) Or Me!Area = "" Then
MsgBox "You must select an Audit area.", vbOKOnly, "Required Data"
Me!Dept.SetFocus
Exit Sub
End If
Dim strMessage As String
strMessage = "Please verify the criteria selected." & vbCrLf
strMessage = strMessage & "Plant: " & Me.txtPlant & vbCrLf
strMessage = strMessage & "Dept: " & Me.Dept & vbCrLf
strMessage = strMessage & "Area: " & Me.Area & vbCrLf
strMessage = strMessage & "Shift: " & Me.txtShift & vbCrLf
strMessage = strMessage & "Auditor: " & Me.txtAuditor & vbCrLf
strMessage = strMessage & "Series: " & Me.Series & vbCrLf
strMessage = strMessage & "UnitType: " & Me.UnitType & vbCrLf
If MsgBox(strMessage, vbYesNo + vbQuestion, "Verify Data") = vbNo Then
Exit Sub
End If
Dim strSQL As String
With CurrentDb.QueryDefs("qryPT")
strSQL = .SQL
.SQL = Replace(.SQL, "{1}", Me.txtPlant)
.SQL = Replace(.SQL, "{2}", Me.Dept)
.SQL = Replace(.SQL, "{3}", Me.Area)
.SQL = Replace(.SQL, "{4}", Me.txtShift)
.SQL = Replace(.SQL, "{5}", Me.txtAuditor)
.SQL = Replace(.SQL, "{6}", Me.Series)
.SQL = Replace(.SQL, "{7}", Me.UnitType)
End With
‘Return new record number
Dim rs As DAO.Recordset
With CurrentDb
Set rs = .OpenRecordset("qryPT")
Me.txtReturnValue = rs.Fields("ScopeID")
End With
With CurrentDb.QueryDefs("qryPT")
.SQL = strSQL ' restore for the next execution
End With
‘Go to new record
Me.Filter = "AuditMasterID = " & txtReturnValue & ""
Me.FilterOn = True
Me.Requery
Me!fsubAudits.Form.Filter = "[AuditMasterID] = " & txtAuditMasterID & ""
Me!fsubAudits.Form.FilterOn = True
'Me!fsubAudits.Requery
'Hide main form controls and open fsubDescLookup
Me.cmdInfo.SetFocus
Forms!frmSTF.Dept.Visible = False
Forms!frmSTF.cmdPrevious.Enabled = False
Forms!frmSTF.cmdNext.Enabled = False
Forms!frmSTF.Area.Visible = False
Forms!frmSTF.UnitType.Visible = False
Forms!frmSTF.Series.Visible = False
Forms!frmSTF.fsubDescLookup.Visible = True
Me!Dept.Enabled = False
Me!UnitType.Enabled = False
Me!Series.Enabled = False
Me!Area.Enabled = False
Me!cmdStartAudit.Enabled = False
End Sub
Thank you for your help. Have a great day!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What does the querydef look like after the sp stops working? If there is a failure in the VBA code prior to the restoration of the querydef's SQL, it will 'break' future executions.
Along the lines of aikimark's last post.
I remembered that a trick I use is to have a single query that does the pass-thru/returns records process. So instead of actually modifying the .SQL property of your query, I would copy the SQL string into a variable, run the replace processes against the variable, and then set the SQL property of a generic pass-through query to the value of the variable. Then call the generic pass-thru query rather than your original.
This allows you to keep the original SQL without modifying it, or the risk of it not being saved back correctly.
I remembered that a trick I use is to have a single query that does the pass-thru/returns records process. So instead of actually modifying the .SQL property of your query, I would copy the SQL string into a variable, run the replace processes against the variable, and then set the SQL property of a generic pass-through query to the value of the variable. Then call the generic pass-thru query rather than your original.
This allows you to keep the original SQL without modifying it, or the risk of it not being saved back correctly.
Things like this may happen if Access is allowed to save back the form, which it occasionally does. Specifically, it may do it when it applies filters, it then can save the file with the filter in place, and that interferes with subsequent operations. Your code does use filtering, so it's likely that that's what's going on.
In order to avoid it, adjust permissions of the Access file, so users have read-only access; and/or set read-only attribute on the file. Monitor file's last modified date and ensure that it does not change.
In order to avoid it, adjust permissions of the Access file, so users have read-only access; and/or set read-only attribute on the file. Monitor file's last modified date and ensure that it does not change.
Vadim,
Access will not generally "save back" the form unless you are doing concurrent editing and make changes to the forms code module while the form is open.
Dale
Access will not generally "save back" the form unless you are doing concurrent editing and make changes to the forms code module while the form is open.
Dale
@Dale, it's been my experience that it will, even compiled file. Maybe different version, but it's a possibility to consider. In any case, the first step should be to check last modified date of the file, and if it has changed, go from there.
@Dale,
Not quite what your thinking. He's not talking about data, but form properties.
Access forms save a lot of things when you close (like filtering, ordering, etc) unless you explicitly tell it not to.
Jim.
Not quite what your thinking. He's not talking about data, but form properties.
Access forms save a lot of things when you close (like filtering, ordering, etc) unless you explicitly tell it not to.
Jim.
ASKER
Thanks for your input Scott. It happens on multiple tablets, The only thing weird about the Dept values I suppose is a hyphen in some of the values, 3500-1, etc. There are linked tables but all the data written is by sprocs.
I actually ended up doing a decompile, recompile, and compact last night after doing some more research after I made this post because I noticed the file size was 4 times what it should be. So far so good today, but the day is only 5 hours old. It would be fantastic if that was the problem. If that don't work I'll move on to copying everything to a new blank db.
I appreciate everyone's input on this, I'll continue to work my way down the list.
I actually ended up doing a decompile, recompile, and compact last night after doing some more research after I made this post because I noticed the file size was 4 times what it should be. So far so good today, but the day is only 5 hours old. It would be fantastic if that was the problem. If that don't work I'll move on to copying everything to a new blank db.
I appreciate everyone's input on this, I'll continue to work my way down the list.
ASKER
Thanks for the help, between the decompile/recompile and bitsqueezers recommendations the problem has gone away completely.
I note some inconsistency in the way you are referring to items in your form in the following lines:
.SQL = Replace(.SQL, "{1}", Me.txtPlant)
.SQL = Replace(.SQL, "{2}", Me.Dept)
.SQL = Replace(.SQL, "{3}", Me.Area)
.SQL = Replace(.SQL, "{4}", Me.txtShift)
.SQL = Replace(.SQL, "{5}", Me.txtAuditor)
.SQL = Replace(.SQL, "{6}", Me.Series)
.SQL = Replace(.SQL, "{7}", Me.UnitType)
In some areas, you are referring to what appear to be textbox controls (txtPlant, txtShift). Why are your references to Dept, Area, Series, and UnitType different? If these other items are referring to fields and not controls you should use the ! to distinguish them (me!Dept, me!Area, ...). Otherwise, you might want to consider using a naming convention for those controls as well.