troubleshooting Question

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

Avatar of Jeff Heilman
Jeff HeilmanFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server 2008Microsoft SQL ServerVBADell
11 Comments2 Solutions333 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 11 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 11 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004