jeffcook_lcog
asked on
Hide ODBC popup error message in Access 2007?
My app uses an Access front-end with a SQL Server 2005 back end. I have an init() VBA function in Access that runs when the main form is opened up. That function tries to query the SQL database using the SQL Native ODBC client driver and, if that fails, then tries to use the SQL MDAC ODBC client driver. All of my users are running Windows XP and have one or both of these client drivers installed.
When using Access 2003, this failover works great - the initial ODBC error (3151 - connection to <DB> failed) generated was trapped and no error was generated and the failover to the MDAC driver was transparent to the end user.
Now, some of the users are using Access 2007 and while the init() function is still trapping the 3151 error - Access first displays the ODBC error message "connection to <DB> failed", forcing the user to press the OK button to acknowledge the error before the error handling continues and properly fails over to the MDAC driver.
Everything still works fine but I don't want my users to have to press OK to acknowledge this error. Any suggestions on how to avoid displaying the initial ODBC error in Access 2007?
When using Access 2003, this failover works great - the initial ODBC error (3151 - connection to <DB> failed) generated was trapped and no error was generated and the failover to the MDAC driver was transparent to the end user.
Now, some of the users are using Access 2007 and while the init() function is still trapping the 3151 error - Access first displays the ODBC error message "connection to <DB> failed", forcing the user to press the OK button to acknowledge the error before the error handling continues and properly fails over to the MDAC driver.
Everything still works fine but I don't want my users to have to press OK to acknowledge this error. Any suggestions on how to avoid displaying the initial ODBC error in Access 2007?
Public Sub Init() 'Called by OPEN FORM event
On Error GoTo Check_Err
Dim rs As DAO.Recordset
Set dbCurrent = CurrentDb()
' First try to connect using SQL Native Client driver
Native_Fail = False
FMRdsn = "LCOG_FMR_ODBC_NATIVE"
Set rs = dbCurrent.OpenRecordset(FMRdsn, dbOpenDynaset, dbSeeChanges) 'If driver unavailable, generates error 3151
Set rs = Nothing 'close recordset
Exit Sub
Native_No:
' If native driver unavailable, try to connect using MDAC ODBC driver
FMRdsn = "LCOG_FMR_MDAC_ODBC"
Set rs = dbCurrent.OpenRecordset(FMRdsn, dbOpenDynaset, dbSeeChanges) 'If driver unavailable, generates error 3151
Set rs = Nothing 'close recordset
Exit Sub
Check_Err:
Select Case Err.Number
Case 3151 'Unable to connect using an ODBC driver
If FMRdsn = "LCOG_FMR_ODBC_NATIVE" Then
Native_Fail = True 'Native Client didn't work, try MDAC
Resume Native_No
ElseIf Native_Fail Then 'Both MDAC and SQL Native Client ODBC driver connections failed.
MsgBox ("Cannot connect to LCOG SQL database, check ODBC drivers - switching to local database"), _
vbOKOnly + vbCritical, "Cannot connect to LCOG SQL database (Error #" & Err.Number & ")"
FMRdsn = "local_FMR"
End If
Case Else
MsgBox Err.Description & " (" & Err.Number & ")", vbOKOnly + vbCritical, "Error"
If Not IsAdmin Then Application.Quit
End Select
Set rs = Nothing 'close recordset
End Sub
ASKER
Thanks - I'm out of the office today but when I get back in, I'll try that. . .
ASKER
Well, the ODBC error still appears adding DoCmd.SetWarnings False.
Also, I was mistaken about it working in 2003 - it does the same thing in that version as well. . .
Also, I was mistaken about it working in 2003 - it does the same thing in that version as well. . .
I was afraid of this. Try this instead:
Err_Open_:
if err.number <> 3151 then
MsgBox Err.number & " " & Err.Description
Endif
Resume Exit_Open_
ASKER
If you look at my code above, you'll see that I'm already catching the 3151 error and resuming. The problem appears to be that the ODBC error is displayed before Access can catch it. . . If I remove the OnError statement, I get TWO error messages - the first one being the ODBC error I mention above and the second being the Access 3151 error. . .
Thanks for your help!
Jeff
Thanks for your help!
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you tried?
Application.DisplayAlerts = False
......your code
Application.DisplayAlerts = True
Application.DisplayAlerts = False
......your code
Application.DisplayAlerts = True
ASKER
@13598 - there is no such command in Access VBA. The Access equivalent is DoCmd.SetWarnings False already suggested by patsmitty above.
ASKER
Christian,
Your idea of checking for the Native Client and installing it if it is not there is a great solution. For now, your comment gave me the idea to check for the Native Client and use it if it is there, otherwise use the MDAC client and that solves my immediate problem. When I get a chance, though, I'll work on implementing your better solution. Thanks!
Jeff
Your idea of checking for the Native Client and installing it if it is not there is a great solution. For now, your comment gave me the idea to check for the Native Client and use it if it is there, otherwise use the MDAC client and that solves my immediate problem. When I get a chance, though, I'll work on implementing your better solution. Thanks!
Jeff
Open in new window