April 22, 2008 at 3:52 am
I have an Access Front end linking to a SQL Server 2005 back end for the tables.
If the server name changes or password change then we need to trap the error at Startup
by checking that we can link.
As part of a startup routine I use the following Code (which works just fine if linking to another Access database).
Dim varX
'Some other stuff here
'Check for table linked correctly
On Error Resume Next
varX = DCount("*", "tblDefaultValues")
If Err.Number <> 0 Then
'Here we decide what to do if it fails
...................................................
Else
'Here is the stuff if it connects ok
...............................................
End If
This ought to work, BUT it does not.
The error is returned as Run Time Error 3146 a network Error
BUT it is ignored by the error handler instruction
It comes up as an ODBC error and drops into the Code Window with Debug regardless.
The Error Trapping is set to Break on Unhandled Errors because there are a lot of Error traps set
and I do NOT want it to break on all errors.
This would appear to be a handled error that is not handled.
I have also tried to check the connection object with a connection string
and to connect to an adodb and dao recordset instead of using DCount
BUT all give the same problem, straight into debug ignoring the error handler.
Any suggestions?
April 22, 2008 at 10:28 pm
I think you may be worring. I have found Access links to SQL back ends with very few drop outs.
How often is a server name changing? It should not be too often.
How often is a password changed?
If it does change then it adds a security layer if a DBA needs to reconnect.
Are you trying too hard?
Do you use a BE/FE model for your access database?
Regards
Graham
April 23, 2008 at 1:39 am
It is true it is not often, but to be honest the question is more academic now than crucial to my life. I have found a solution where the error can SOMETIMES be trapped using DAO Er;)rors in Acceess.
This is done by opening a DAO recordset and then checking against the dbEngine Error collection. However, even this does not always work, because although it picks up the error, it still opens it's own error system first before using my error handler.
The system is FE/BE.
My point here really is.
If VBA has Error trapping it should trap Errors.
It should not ignore ODBC Network Errors, and I was wondering if there is a simple solution (which it appears there isn't) unless anyone else knows better.
April 23, 2008 at 1:44 am
OK, I see what you are saying.
April 24, 2008 at 1:50 am
Trouble is you're checking a table not a connection.
I do this as a two parter - first check that I can connect, then check for the table. The basic guts of this with no error checking or garbage collection is:
Public Function CheckConnection( _
ConnectionString As String) As Boolean
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = ConnectionString
cnn.Open
CheckConnection = (cnn.State = adStateOpen)
End Function
The connection string I use looks like:
"Provider=SQLOLEDB.1;" & _
"Data Source=" & ServerName & ";" & _
"Initial Catalog=" & Database & ";" & _
"Integrated Security=SSPI"
Obviously, I'm using windows authentication.
April 24, 2008 at 1:59 am
Thanks.
I'll give that a go.
It still does not get MS out of the issue that
their Error Trap in Access VBA doesn't trap all errors correctly.
April 24, 2008 at 2:33 am
Problem is not all errors are trappable.
Look at trappable errors in access vba help to see what is marked as "trappable".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply