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.
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!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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
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.
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.
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:
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:
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
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;
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;
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
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.
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.
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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?
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.