How do I trap SQL Server error in Access Project VBA?
-
I have an Access 2003 project that uses SQL Server 2000. I have a VBA procedure that calls a SP and that SP checks to see if a certain condition exists and then uses the Raiserror function to send a message back to Access. This works find, but I want to trap the error after it fires/displays message so I can Exit the Sub that called it. I have tried numerous methods posted on the internet, but none work. Here is a portion of the SP code for the Raiserror that works:
DECLARE @strMsg varchar(100)
SET @strMsg = 'There are no samples that need RunSheets created for this Study, Method and Matrix'
IF (SELECT COUNT(*)
FROM dbo.tblSampleMain
WHERE RunSheet_Created = 0
AND Method_ID = @MethodID
AND Study_Tracker = @StudyTracker
AND Matrix_ID = @MatrixID) < 1
BEGIN
RAISERROR (@strMsg, 18, 1)
RETURN
END
ELSE
BEGIN
TRUNCATE TABLE dbo.tblCreateRunSheet
This works fine, but I can't trap it so the VBA Sub continues, but I want to exit it if this message fires. This is one of the ways I have tried to trap it unsuccessfully:
Dim errX As DAO.Error
If Errors.Count > 1 Then
For Each errX In DAO.Errors
Debug.Print "ODBC Error"
Debug.Print errX.Number
Debug.Print errX.Description
Next errX
Else
Debug.Print "VBA Error"
Debug.Print Err.Number
Debug.Print Err.Description
End If
Any ideas would be appreciated.
-
An access project doesn't Dao I believe. You might be looking in the wrong section.
If Errors.Count > 1 Then
For Each errX In Errors
Debug.Print "Error"
Debug.Print errX.Number
Debug.Print errX.Description
Next errX
end if
In dao (Access97) I write commonly
if err.number<>0 then
msgbox errors(0).description 'mostly the odbc error
end if
-
The only time SQL server reports an error back to Access is when the severity level is 19 or higher and the procedure is terminated. It appears that in your case, you want the procedure to continue. In that case declare an output parameter of type integer, and optionally an output parameter to display a message. Rather than using Raiserror, set the output parameters to @@ERROR and your custom error message. @@ERROR will be 0 if the last SQL statement completed successfully. Once the procedure returns, your VBA code can check to see if the output parameter is > 0.
Good Luck
-
I just reread your post and realize I misunderstood what you were trying to accomplish. I have never used DAO to connect to SQL server from an Access project. I use ADO so I can't tell you how to do it in DAO, but in ADO it would look like something like this:
Private Sub Test()
Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "dbo.Test" 'This is the name of the stored procedure
Set par = .CreateParameter("@Return", adInteger, adParamReturnValue)
.Parameters.Append par
.Execute
If .Parameters("@Return") > 0 Then
'message to the user
Exit Sub
Else
'some code here
End If
End With
Set par = Nothing
Set cmd = Nothing
End Sub
In your stored procedure, rather than the Raiserror statement, just set return to a number greater than 0 anytime you want your vba code to display a message and exit.
I know it isn't a DAO solution, but I hope it helps
-
I do use ADO. I just saw that DAO code on a website and thought I would try it. The problem is some procedures do pass the info to Access through Access's error channel and other procedures pass the message, but not through the error channel. Here is an example and the results that work:
ALTER PROCEDURE dbo.usp_DisplayResults
AS
DECLARE @strMsg varchar(100)
SET @strMsg = 'All results have been sent for this study!'
IF (SELECT COUNT(*)
FROM dbo.tempResults) < 1
BEGIN
RAISERROR (@strMsg, 18, 1)
SELECT @strMsg AS Message
RETURN
END
ELSE
SELECT * FROM dbo.tempResults
And here is an example that sends the message but not through the error handler which is the same in both calling subs:
IF (SELECT COUNT(*)
FROM dbo.tblRunSheets
WHERE Sequence_Created = 0
AND Method_ID = @MethodID
AND Study_Tracker = @StudyTracker
AND RunSheet_ID = @RunSheetID) < 1
BEGIN
RAISERROR (@strMsg, 18, 1)
RETURN
END
ELSE
IF (SELECT DISTINCT Cleanup_Analyst
FROM dbo.tblRunSheets
WHERE Method_ID = @MethodID
AND Study_Tracker = @StudyTracker
AND RunSheet_ID = @RunSheetID) IS NULL
BEGIN
SET @strMsg = 'Cleanup has not been performed on this RunSheet. Please perform Cleanup before Sequencing'
RAISERROR (@strMsg, 18, 1)
RETURN
END
ELSE
BEGIN
As you can see it passed the message, but not through this error handler:
If Err.Number = 8008 Then
Resume Next
Else
MsgBox ("Error # " & Err.Number & ": " & Err.Description)
Resume exit_cmdEmail_click
End If
-
I still don't understand why you need to do this using errors. I appears that you want to check for certain conditions throughout your stored procedure, and send a message back if the condition is true. I will assume you want your VBA code to do something different depending on which message is returned. The easiest way to do this is by using an output parameter for the message and send back a different RETURN value depending on the message:
ALTER PROCEDURE dbo.usp_DisplayResults
(
@strMsg varchar(100) OUTPUT
)
AS
SET @strMsg = ‘’ --set this initially to an empty string
IF (SELECT COUNT(*)
FROM dbo.tempResults) < 1
BEGIN
Set @strMessage = 'All results have been sent for this study!'
RETURN 1
END
ELSE
SELECT * FROM dbo.tempResults
IF (SELECT COUNT(*)
FROM dbo.tblRunSheets
WHERE Sequence_Created = 0
AND Method_ID = @MethodID
AND Study_Tracker = @StudyTracker
AND RunSheet_ID = @RunSheetID) < 1
BEGIN
Set @strMessage = 'All results have been sent for this study!'
RETURN 1
END
ELSE
IF (SELECT DISTINCT Cleanup_Analyst
FROM dbo.tblRunSheets
WHERE Method_ID = @MethodID
AND Study_Tracker = @StudyTracker
AND RunSheet_ID = @RunSheetID) IS NULL
BEGIN
SET @strMsg = 'Cleanup has not been performed on this RunSheet. Please perform
Cleanup before Sequencing'
RETURN 2
END
ELSE
BEGIN
………
RETURN 0
In your sub check the return value and display the message.
Dim strMessage as string
strMessage = cmd.parameters(“@strMsg”)
Select Case cmd.parameters(“@RETURN”)
Case 1
Debug.print strMessage
‘Take some action
Case 2
Debug.print strMessage
‘Take some other action
……
End Select
-
I did some more research on trapping error messages in ADO. First, by Default RAISERROR assigns the number 50000 to user defined error messages that are not in the SysMessages table. If you want to assign a custom error number, you need to add it to SysMessages using the sp_AddMessage system stored procedure (Check Help files for details). Then when you want to raise the error, pass the Message ID rather than text:
RAISERROR(50001, 16, 1)
To Trap this in your code, you need to use the ADO error object, not VBA's. The example I used is posted below:
Public Function Test()
On Error Resume Next
Dim cmd As New ADODB.Command
Dim ADOErr As ADODB.Error
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.z_Test"
.CommandType = adCmdStoredProc
.Execute
If .ActiveConnection.Errors.Count > 0 Then
For Each ADOErr In .ActiveConnection.Errors
Debug.Print ADOErr.NativeError & " " & ADOErr.Description
Next
End If
End With
Set cmd = Nothing
End Function
You can use the same approach with a recordset and connection object, just check the .ActiveConnection.Errors.Count property of the recordset.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply