Bulletproof an import from the SQL Server...

[sabavno]

Hi

I have a couple of procedures where I insert stuff from SQL table into my local Access 97 table.

What I want to have is the error trapping so that if any errors occured while inserting the table, I want to stop inserting, delete the things that were inserted and start the inserting all over again.

Please suggest any error trapping available in this cases

Thanks

 

[Maquis]

What kinds of errors can occur? Corrupt or nonsense data?

How about loading your data into temp table in Access. If all goes well then you can move the data into your real table and clear out the temp. Maq
<insert witty signature here>

 

[FancyPrairie]

You should wrap your code around BeginTrans and CommitTrans.
If an error occurs, your code should branch to your error handler and perform a Rollback. Else, if everything works ok, Commit the transaction.

 

[sabavno]

Could anyone give me the code example of BeginTrans /CommitTrans

 

[FancyPrairie]

There are many ways to do it, check OnLine Help. Here is an example of one way.

    Dim cnn As ADODB.Connection
    
    Dim lngAffected As Long
    
    Dim bolBeginTrans As Boolean
    
    On Error GoTo ErrHandler

    bolBeginTrans = False
    
    Set cnn = CurrentProject.Connection
        
    cnn.BeginTrans
    bolBeginTrans = True
        
    cnn.Execute "INSERT INTO tblABC...", lngAffected, adExecuteNoRecords
    cnn.Execute "DELETE * FROM tblABC..."
        
    cnn.CommitTrans
    bolBeginTrans = False
    Set cnn = Nothing

'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Sub

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Description, vbExclamation
        
    If (bolBeginTrans) Then
        cnn.RollbackTrans
        bolBeginTrans = False
    End If
    
    Resume ExitProcedure