Link to home
Start Free TrialLog in
Avatar of Jeff Heilman
Jeff HeilmanFlag for United States of America

asked on

MS Access pass through query and SQL stored procedure stops working properly after a day or two.


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:

/****** Object:  StoredProcedure [dbo].[AuditInsert]    Script Date: 12/16/2015 10:27:24 ******/
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)
DECLARE @FirstAuditMasterID int, @AuditMasterID int



			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;
	-- Test whether the transaction is uncommittable.
		RETURN -1;

Open in new window

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}'

Open in new window

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"
        Exit Sub
    End If
    If IsNull(Me!UnitType) Or Me!UnitType = "" Then
      MsgBox "You must select a Unit Type.", vbOKOnly, "Required Data"
        Exit Sub
    End If
    If IsNull(Me!Series) Or Me!Series = "" Then
      MsgBox "You must select a Series.", vbOKOnly, "Required Data"
        Exit Sub
    End If
    If IsNull(Me!Area) Or Me!Area = "" Then
      MsgBox "You must select an Audit area.", vbOKOnly, "Required Data"
        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!fsubAudits.Form.Filter = "[AuditMasterID] = " & txtAuditMasterID & ""
   Me!fsubAudits.Form.FilterOn = True
'Hide main form controls and open fsubDescLookup
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

Open in new window

Thank you for your help.  Have a great day!
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I cannot see anything which would cause this to work properly for several days and then start not working properly.  Is this solely operating with a SQL Server BE, or do you have an Access BE somewhere as well, perhaps on a network?

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.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.

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, 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.

   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.

Avatar of Jeff Heilman


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.
Thanks for the help, between the decompile/recompile and bitsqueezers recommendations the problem has gone away completely.