?
Solved

Hide ODBC popup error message in Access 2007?

Posted on 2010-11-24
11
Medium Priority
?
2,162 Views
Last Modified: 2013-11-27
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?
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

Open in new window

0
Comment
Question by:jeffcook_lcog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34210787
The version for Access 2007 is 12.0. So you could say:

If Application.Version = "12.0" Then
DoCmd.SetWarnings False
End If

Open in new window

0
 

Author Comment

by:jeffcook_lcog
ID: 34240835
Thanks - I'm out of the office today but when I get back in, I'll try that. . .
0
 

Author Comment

by:jeffcook_lcog
ID: 34261685
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. . .
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34262154
I was afraid of this. Try this instead:

Err_Open_:
if err.number <> 3151 then
MsgBox Err.number & " " & Err.Description
Endif
Resume Exit_Open_

Open in new window

0
 

Author Comment

by:jeffcook_lcog
ID: 34262322
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
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 34328803

Hi,

unfortunately VBA cannot trap any error. But maybe in this case a simple solution could be to use the Form_Error event on the form where you call the Init function instead of the Init. 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.

But the better method at all would be to
1. never use an external ODBC connection which needs an ODBC definition in the operating system of the target computer - simply change all links to DSNless connections. The method is descibed here:
How to create a DSN-less connection to SQL Server for linked tables in Access
2. don't use two different drivers on the same database - if the user's computer has not installed the SQLNCLI driver, simply install it. Here are some hints how to test the existence of the driver on the target computer and how to install it with your application:
Test if sqlncli is installed

If you test the described registry key or anything else to see if the driver is installed you don't need to open a recordset and so you will not get an error which maybe is not trappable in Access.

Cheers,

Christian
0
 
LVL 16

Expert Comment

by:13598
ID: 34343744
Have you tried?
Application.DisplayAlerts = False
......your code
Application.DisplayAlerts = True

0
 

Author Comment

by:jeffcook_lcog
ID: 34346215
@13598 - there is no such command in Access VBA.  The Access equivalent is DoCmd.SetWarnings False already suggested by patsmitty above.
0
 

Author Closing Comment

by:jeffcook_lcog
ID: 34346286
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
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question