title: How can I best use VBA in Access or Excel to test an ODBC connection?
body: <p>Given a pre-configured ODBC System DSN, I'd like to write a function that gracefully <strong>tests</strong> that connection using VBA.</p>
  
  <pre><code>Private Function TestConnection(ByVal dsnName As String) As Boolean
  
      ' What goes here?? '
  
  End Function
  </code></pre>
  
  <p>Edit: To clarify, the System DSNs are pointing to external SQL Server 2005 databases, with Windows NT authentication.</p>
  
  <p>One approach I've tried is to send some random query to the target database and catch the error. If the query works, return true. If there's an error then return false. This works just fine but it feels...kludgy. Is there a more elegant way, especially one that doesn't rely on <em>On Error Goto</em> ?</p>
  
  <p>Note: It's a legacy Access 2000 database I'm working on, so any solution can't have any Access 2007 or 2003 dependencies. I'd like to make it generic to VBA, but if there's a simple way in Access that's fine too.</p>
  
  <p>Much obliged for any advice.</p>