Cusack
asked on
ADODB Errors
I have a database that periodically gets corrupted for some reason (access 2000) and when I connect to it via a adodb.connection it crashes the program. Is there any way to catch this error then have it switch to a temporary backup database? I know when I use the adodc control I can use the error event to do this, any such thing I can do with adodb?
The adodb object has an errors collection. If you add error trapping in the procedure in which you're establishing the connection, you can interrogate the error object and switch connections.
ASKER
I already do that and the error is still verbose.
Just a sugestion. Have you tried to use ODBC and SQL?
You could catch the error by doing a query that you know you are going to get a result from !!! if you do not get a result you know that you are having problems !!!
You could to a SELECT COUNT statement to get a count of records in the table !!!
Could yu post the code that you are using !!! I will be able to help you a little bit better if i know what i am up against :)
SI
You could to a SELECT COUNT statement to get a count of records in the table !!!
Could yu post the code that you are using !!! I will be able to help you a little bit better if i know what i am up against :)
SI
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
pramodkumarsingh the code sample you provided seems like they will help deal with problem areas - I am going to be out of the office today. Tomorrow I will likely ask some additional questions about your code.
What is the size of MDB? > 100M?
How many tables in you MDB? > 1000?
How many tables in you MDB? > 1000?
ya sure..
ASKER
Pramodkumarsingh - for the with events you seem to have provided me with the solution for the initial connection. What about if there are errors when a command is being executed?
ASKER
rogerkhli - the size of the database can vary based on client. It can be anywhere from 1 MB to 100MB
* note: if anyone else gives me info I use I will ask multiple questions as a thanks
* note: if anyone else gives me info I use I will ask multiple questions as a thanks
THis might be useful. To avoid errors I use the following code to check my MDB file when the program is initially opened. Note that you will need to set a reference to the Microsoft Jet & Replication Objects library.
'check for data problems and compact the data file
Dim je As New jro.JetEngine, strSource As String, strDest As String
strSource = "Provider=Microsoft.Jet.OL
strDest = "Provider=Microsoft.Jet.OL
je.CompactDatabase strSource, strDest
Set je = Nothing
strSource = ""
strDest = ""
Name App.Path + "\tt.MDB" As datapath
'check for data problems and compact the data file
Dim je As New jro.JetEngine, strSource As String, strDest As String
strSource = "Provider=Microsoft.Jet.OL
strDest = "Provider=Microsoft.Jet.OL
je.CompactDatabase strSource, strDest
Set je = Nothing
strSource = ""
strDest = ""
Name App.Path + "\tt.MDB" As datapath
ASKER
TomLaw - I actually do that when the program starts too!
Hi Cusack,
I think you have not tried this in VB, when u use ADODB with WithEvent u get lots of Events to capture that allmost covers all the functionality provided by ADODB object.
As per ur concern the event that can be used is ExecuteComplete
This method is called after a command has finished executing.
An ExecuteComplete event may occur due to Connection.Execute, Command.Execute, Recordset.Open, or Recordset.NextRecordset.
hope this helps u......
I think you have not tried this in VB, when u use ADODB with WithEvent u get lots of Events to capture that allmost covers all the functionality provided by ADODB object.
As per ur concern the event that can be used is ExecuteComplete
This method is called after a command has finished executing.
An ExecuteComplete event may occur due to Connection.Execute, Command.Execute, Recordset.Open, or Recordset.NextRecordset.
hope this helps u......
the with the above example it will look like this..
Private Sub Con_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
End Sub
this is the explanation of the syntax
ExecuteComplete RecordsAffected, pError, adStatus, pCommand, pRecordset, pConnection
Parameters
RecordsAffected A Long. The number of records affected by the command.
pError An Error object. It describes the error that occurred if the value of adStatus is adStatusErrorsOccurred; otherwise it is not set.
adStatus An EventStatusEnum status value. When this method is called, this parameter is set to adStatusOK if the operation that caused the event was successful, or adStatusErrorsOccurred if the operation failed.
Before this method returns, set this parameter to adStatusUnwantedEvent to prevent subsequent notifications.
pCommand The Command object, if any, that was executed.
pRecordset A Recordset object. The result of the execution. This recordset may be empty.
pConnection A Connection object. The connection on which the command was executed.
hope this clears all .....
Private Sub Con_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
End Sub
this is the explanation of the syntax
ExecuteComplete RecordsAffected, pError, adStatus, pCommand, pRecordset, pConnection
Parameters
RecordsAffected A Long. The number of records affected by the command.
pError An Error object. It describes the error that occurred if the value of adStatus is adStatusErrorsOccurred; otherwise it is not set.
adStatus An EventStatusEnum status value. When this method is called, this parameter is set to adStatusOK if the operation that caused the event was successful, or adStatusErrorsOccurred if the operation failed.
Before this method returns, set this parameter to adStatusUnwantedEvent to prevent subsequent notifications.
pCommand The Command object, if any, that was executed.
pRecordset A Recordset object. The result of the execution. This recordset may be empty.
pConnection A Connection object. The connection on which the command was executed.
hope this clears all .....
ASKER
Last question - Err.Clear < --- What exactly does that do for me? I know it "clears" the error, but does that mean it makes it so my program won't crash, or what?
The error is traped and handled, we have closed the conncetion too .. that's why we clear the error collection .. any way if you don't do that and move to some other method which have error handler this automatically clears the error the statement On Error Goto ... automatically clears the Error Collection.
It's always good to clear the error ,if you don't want to propogate the error to upper level after handling it..
Hope this clears the points...........
It's always good to clear the error ,if you don't want to propogate the error to upper level after handling it..
Hope this clears the points...........
ASKER
I appreciate your time and patience. Thanks for clearing that final point as well.