We help IT Professionals succeed at work.
Get Started

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

331 Views
Last Modified: 2016-11-23
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:

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

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"
        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

Open in new window


Thank you for your help.  Have a great day!
Comment
Watch Question
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This problem has been solved!
Unlock 2 Answers and 11 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE