Testing Permissions without Errors

[Stang70Fastback]

Hello all. I'll cut to the chase. I have an Access front end and SQL Server back end. I use DSN-less connections to link up all the tables. If the user doesn't have the rights, then I get this error:

and then it displays a small login window with the server info, etc...

I want to avoid all of that. Is there a simple piece of VBA code I can use to simply test whether or not I can connect to my SQL database WITHOUT throwing all these errors? Even if I can't differentiate between the server being DOWN or the user not having permissions, I'd love to just be able to start things off with a generic, "Access cannot reach the server, or you do not have access to this database. Please contact your administrator," error message which would then cleanly quit Access.

I just don't know how to do it without simply trying to call a normal ODBC connection which will then throw that error. All I want is a way to test whether or not they have permissions to access the database. The flip side of the coin might be, can I suppress these errors somehow?

 

[jdraw]

The person with most knowledge on DSNLess connections is Doug Steele.
See this for info
Others my know about SQL Server permissions. Google may be your friend.

 

[Rx_]

Your question was interesting. Our site uses Citrix. If the user doesn't have a Citrix account, then the Access DB can't be touched. So we just go with DSN-Less connection on a master that is cloned for each Citrix user that logs in.

This is an older DSN-Less example. The Error code looks valid. Please let others know if it is useful:
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Several other sites also had answers that might make a difference. Examples:

' In VBA code use explicit type declarations only instead of implicit declarations. So please replace use DAO. In front of any database type declarations in order to explicitly create DAO objects. For example replace the following:

Dim dbs As Database
Dim rstAppend As Recordset
By
Dim dbs As DAO.Database
Dim rstAppend As DAO.Recordset

Switching from TCP/IP to Named Pipes in the Client Configuration for the SQL Server ODBC Driver allowed one user to trap errors. (Rx_ sounds interesting)

Again, this is old code for older versions. The article is longer. Could you try this and let us know if it works for your situation?
http://support.microsoft.com/kb/124901
Create the following function in a module:

 Function Login_Error (UserID, Password)

         On Error GoTo Error_Trap2

         Dim myws As WorkSpace, connstr As String
         Dim mydb As Database

         connstr = "ODBC;DSN=opus;UID=" & UserID & ";PWD=" & _
         Password & ";LANGUAGE=us_english;DATABASE=pubs"

         Set myws = DBEngine.Workspaces(0)
         Set mydb = myws.OpenDatabase("", False, False, connstr)

         mydb.Close

         Exit Function

      Error_Trap2:
         MsgBox "An error has occurred."
         MsgBox Error
         Exit Function

      End Function

Type the following line in the Debug window (or Immediate window in version 2.0), and then press ENTER:
?Login_Error("myuser","wrongpassword")

Note that you do not reach the error trap unless you cancel the login attempt, at which point the error message is "Operation canceled by user."

Probably the answer you didn't want to hear.
In general, this answer seemed to come up often. I remember using this back in Access 97 for a large project but our front-end was VB. Here is a summary of the answer:

Unfortunately VBA cannot trap any ODBC error ahead of the error dialogue. But maybe in this case a simple solution could be to use the Form_Error event on the form where you call the "connect" function instead of the "connect" function in code. If the form contains an access to the recordset using the SQLNCLI driver then the Form_Error event can trap some of the errors VBA is not able to catch.

You asked for a Simple Code way. Sorry, don't seem to have one for you.
But, if you want to get a little more complex, this article looks like it would do the trick.
Since you only need to look at the opening, it might not be as complex as this article describes.
http://www.accessmvp.com/TomvanStiphout/OdbcErrors.htm