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:

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!
Jeff HeilmanBusiness Systems MgrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does this happen on a single tablet only, or does it happen on more than one tablet?

Anything odd about the Dept values, like single quotes, backslashes, etc?

I see that your user must validate the data prior to you sending it to the query - at that point, the data is correct, I assume?

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.
This sounds like corruption, but it's hard to say. If simply copying over a new file fixes things, then something on the tablet is causing issues. I've not seen an environment like yours (Access on a wireless connection) that works reliably, but if you're using pass-through queries for ALL data operations you might be able to get away with it. If you have linked tables, or anything of that nature, then all bets are off - Access just won't work reliably in that manner.

If it is corruption, then have you performed maintenance on the file? Make a backup, and then do this:

1. Compact the database
2. Compile the code
3. Compact again

Many find that Decompiling helps as well. To do that, create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that, then run the 3 steps above again.

Finally, moving everything to a new, blank database can also cure a lot of ills.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
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.
BitsqueezerCommented:
Hi,

I would try to use Debug.Print in the frontend code to print all of the parameters into the VBA immediate window (or alternatively use a message box if you want to see it on a user computer with a runtime installed).

Next you should also do the same with the SP on the server: Use a log table to write the parameter names and their values into such log table before the "BEFORE TRY" command, so it will be written without a rollback. You should also do this in general in the CATCH block to have a possibility to see which parameters were used and also this log table should log errors which may be occured in the CATCH block. currently you do nothing but a rollback and return -1, currently you do not read out that value in the frontend so you never see if an error occured. As the CATCH block has no further error handling this is the same as if you would use "On Error Resume Next" in VBA.

In the end you can check if any error occured and if the parameters are filled on the frontend and also received at the backend and if they have the right value.

You should also start a SQL Server Profiler session where you can see the communication between frontend and backend and if any errors occured which maybe are surpressed.

The SP code could be shortened on that way:
	DECLARE @intReturnValue AS int = 0;

	SET NOCOUNT ON;
	
	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRANSACTION
			INSERT INTO dbo.tblQMSAuditMaster (Plant, Dept, Area, Shift, Auditor, Series, UnitType)
			VALUES (@strPlant, @strDept, @strArea, @strShift, @strAuditor, @strSeries, @strUnitType);
			
			SELECT @intReturnValue = SCOPE_IDENTITY();

			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 @intReturnValue;

Open in new window


There is no need to check the MAX value as the transaction already makes sure that no one else could insert something into the table in between your transaction.
Here is a possibility to read out the return value of the SP:

SET NOCOUNT ON;
DECLARE @intReturnValue AS int;
SET @intReturnValue = 0;
EXECUTE dbo.AuditInsert @strPlant = N'{1}', @strDept = N'{2}', @strArea = N'{3}',
						@strShift = N'{4}', @strAuditor = N'{5}', @strSeries = N'{6}', @strUnitType = N'{7}'
SELECT 'Return_Value' = @intReturnValue;

Open in new window


If the PT query is set to return records you can now get the return value which is an ID if everything is OK or -1 if not so you have both with one value.

Cheers,

Christian
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

aikimarkCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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.
Vadim RappCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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
Vadim RappCommented:
@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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@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.
Jeff HeilmanBusiness Systems MgrAuthor Commented:
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.
Jeff HeilmanBusiness Systems MgrAuthor Commented:
Thanks for the help, between the decompile/recompile and bitsqueezers recommendations the problem has gone away completely.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.