SQL permissions error handler? (1 Viewer)

[Tango]

I have an Access form based on a SQL server table. The table has server side permissions set to prevent view (Select) unless in a security group.

So far so good, everything works as intended. HOWEVER, I am trying to trap the sql server select error and just display the msgbox I already created.

The msgbox displays but the sql select error still appears as well. Any pointers on blocking the sql error since the "error" is already being handeled in VBA?

 

[mdlueck]

HOWEVER, I am trying to trap the sql server select error and just display the msgbox I already created.

In your standard error handler, perhaps handle the paticular error code you are expecting from SQL Server and branch down a custom path if that error is received by Access/VBA:

Sample code:

Err_dbutils_RefreshLocalTmpTbl:
[B]  'OK to expect 3325 type errors since an IF might prevent the SELECT from executing
  If Err.Number = 3325 Then
    dbutils_RefreshLocalTmpTbl = True[/B]
  Else
    'further error handling here for other error types
    Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()")
    dbutils_RefreshLocalTmpTbl = False
  End If
  Resume Exit_dbutils_RefreshLocalTmpTbl

In this case, the Stored Procedure had an IF statement in it, and thus would possibly return no RecordSet.

 

[Tango]

I guess the problem is that SQL errors are executed before the VBA error handler is even looked at. So regardless of what I tell it to do with an error in VBA it gets ignored by the SQL error.

 

[mdlueck]

Rrrrr????

In my case, SQL Server ends the Stored Procedure with a 3325 error. So in SQL Server's mind it is ending in error / not happy.

My VBA technique is to ignore that one error code coming back form SQL Server.

Is that not exactly what you are asking for?

 

[mdlueck]

Oh, and as a P.S. also assummed that the adoRS / adoCMD objects are destroyed as part of function cleanup. You are not trying to re-use again a database object which has been involved in an error, are you? I found that such is not possible.

The only object which exists across multiple uses is the adoConnection object. All others I create / cleanup in the context of the class method that needs them, or occasionally I have to devine an adoRS object at class level, one method makes an instance of that special name, calls submethods, and at completion of those submethods that main method destroys the object it created.

 

[Tango]

Here are the errors I am trying to supress. Everything works like i want it to I just dont want the user to see these errors.

 

[mdlueck]

About your second pic... that looks like an Access Macro. This is the "Modules & VBA", thus I was assuming you are dealing with VBA code and not Macro code.

The only thing I have ever used Macros for is an autoexec macro to open the default form. In my mind, you do not have such granular control with Macros... to ignore raised errors.

 

[Tango]

The 2nd pic is indead a macro but that is just an issue with me still needing to convert it to VBA. Simple enough since its a simple "openform" one line macro connected to a button. The 1st pic is the "devil" I can't get rid of.

 

[mdlueck]

The 1st pic is the "devil" I can't get rid of.

Copy/Paste the code that runs the query which raises the error... complete code including your error handler. Please remember to use code tags.

 

[Tango]

Its not a query per se, It is a form with the record source defined as a linked table in SQL. The SQL server is set to deny view (select) access to that table unless members are in a certain security group.

Essentially the entire setup is designed to keep people outside of that security group from being able to "see" the form. So I WANT it to error out I just dont want it to display an error.

The only code involved is the docmd.openform that launches the form and within the form itself I have the onerror event set to display the msgbox "You do not have the authority to perform this action. Contact Supervision for assitance."

 

[Tango]

MUWahahahahha,

I found the solution. I changed the open form to VBA instead of a macro then inserted the error handler on both the onopen of the form itself as well as on the onclick event that opens the form.

The errors are now gone.

Thanks for the help. You got me thinking in the right direction for sure.

 

[mdlueck]

I think it is.... still possible to mask/handle the error....

So, "issues a query upon a linked table." And I suppose you are not querying to an FE temp table, but having the linked table object be the record source for the form. Ja?

That gets messy with GUI behavior mixed with VBA behavior. Should still be possible to achieve, but I do not work with GUI linkages enough to be knowledgeable as to where to wire in the error catching / suppression logic. Perhaps in the Form's On Error event? Ma ei tea...