handling run-time error

Posted on 1998-06-21
Last Modified: 2012-06-21
Dear friends,
    I've got a VB application with four forms. And each of the forms contain some sql statement such as insert,delete, update and select. I'm using ODBC and sql passthrough. However there would be times when my sql passthrough would have error due to some server problem(ie when server is down). Below is so called the pictorial form.

<Form 1>
Dim connect as string,db as database
connect$="ODBC;DSN=Oracle;UID='" & txt_1.Text & "';Pwd='" & txt_2.TExt & "';
set db=opendatabase("",false,false,connect)

<Form 2>
other vb codes....
sql_insert="Insert into table......"
db.execute sql_insert,dbsqlpassthrough
other vb codes....

other vb codes.....
sql_truncate="Truncate table...."
db.execute sql,dbsqlpassthrough
sql_insert="Insert into table......"
db.execute sql_insert,dbsqlpassthrough

other vb codes......
sql_delete="Delete table......"
db.execute sql_delete,dbsqlpassthrough
other vb codes...

Expert friends,
Can anyone please advise me with workable VB codes as how I should handle the run-time errors for these sql passthrough.
Q1)For statement "On error go to Error handler",are we supposed to define only once in every form or do we only need to define in one form.And in every form can we define more than once, is it necessary.

Q) Besides sql passthrough giving me run-time errors, there would be times when other of my VB Codes would give me run-time errors as well.
Therefore I would be most grateful if someone Can advise me on how I should code my application in order to handle these errors together(other vb codes and sql passthrough) elegantly.
Question by:kian042298
  • 2

Accepted Solution

bin_huwairib earned 140 total points
ID: 1463928

A1) You must define an error handler in each sub or function to get a better result and performance and to a void the regular VB error message "Run-time error 'xxxxx'".

A2) SQL passthrough errors and VB errors can be handled with the Err object, so the below error handler format will handle both of them unless you want specifically to track some errors. In this case you have to know the error number and assign its action when the handler get the error.

Here is my stander error handler:

Sub ShowMessage()
 On Error Goto Err_ ShowMessage

Exit_ ShowMessage:
 On Error Goto 0
 Exit Sub

Err_ ShowMessage:
 MsgBox Err.Description, vbCritical
 Resume Exit_ ShowMessage
End Sub

That was a global error handler, to make it specific replace the Err_ShowMessage label with the following:

 Select Case Err.Number
  Case 100
   Resume Next
  Case Else
   MsgBox Err.Description, vbCritical
   Resume Exit_ ShowMessage  
 End Select

NOTE that you must know the error number or at least the errors range, I mentioned the error no.100 just for explanation purpose.

Best regards
Ben Huwairib

Author Comment

ID: 1463929
Hi Bin_huwairib,
   I realised you always provide others with sound and solid VB codes and explanation. And I also do noticed that your ranking is going up as well. So sincerity and being helpful does, isn't it? Yes men! Bravo! Once again, thank you for all that.
   Just one more doubt to clarify with you, does it mean that I will have to declare the above section of codes in every of my VB forms and with that I would be able to capture and handle any run-time errors so long as I know the error number. If that is so where would be the best place to put in those error handling codes based on my scenario in the early question (any guidelines). Could you please enlighten me. Once again, thank you.

Expert Comment

ID: 1463930

Define it in every sub/function in each form because the handler will be in the procedure level only not for the whole form.

Suppose you have form1 with two subs: Form_Load and ConnectToSQL in this case you have to define the handler for both of the subs, it will be as follows:

Sub Form_Load()
 On Error Goto Err_Form_Load

 ' Your code....

 On Error Goto 0
 Exit Sub

 MsgBox Err.Description
 Resume Exit_Form_Load
End Sub

Sub ConnectToSQL(Param1 As String)
 On Error Goto Err_ConnectToSQL

 ' Your code....

 On Error Goto 0
 Exit Sub

 MsgBox Err.Description
 Resume Exit_ConnectToSQL
End Sub

Now you must similarly define the handler for the rest of subs/functions for this form and the other forms as well.

Ben Huwairib

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 VB code 9 102
VBA color chart bars 12 77
Saving history changes to sub form 4 36
How to produce a SHA-1 hash function in vb6 in order to save it to a table 8 36
Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

912 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now