Rollback

[boblovesyousomuch]

Hello,

I am trying to catch errors and rollback the data when importing a text file to my database. Hiowever I have two problems.

1. This occurs each time there is an error. An error table gets created with all the error rows in it. I don't want this to happen because obviously it will increase the size of the DB.

2. When a text file is imported if it is not in the correct format (ie. not the same amount of colunms) some information still gets added to the actual table and an error table is created. The actual table will then contain invlaid data and NULL values in some rows. How can i stop that?

Hope that is not to much!?? My code is below.

Thank you

Carl

Private Sub CmdOk_Click()    
On Error GoTo Err_CmdOk_Click
Dim db As Database
Dim wsMyWrkSpc As Workspace
Set wsMyWrkSpc = DBEngine.Workspaces(0)
Set db = CurrentDb
DoCmd.SetWarnings False  
'begin transaction  
wsMyWrkSpc.BeginTrans  
'import text file in to temporary table      
DoCmd.TransferText acImportDelim, "Avaya Specification", "tblImport", Me.TxtFile    
With db  'copy data from temporary table to actual table          
.Execute "INSERT SQL"
.Execute "UPDATE SQL"
DoCmd.DeleteObject acTable, "tblImport"    
End With ' commit transaction     
wsMyWrkSpc.CommitTrans   'success message   
MsgBox "Stats Imported", vbInformation, "Imported Ok!"
DoCmd.SetWarnings True
Exit_CmdOk_Click:  
Exit Sub
Err_CmdOk_Click:     'rollback if needed     
wsMyWrkSpc.Rollback    
errMsg = errMsg & "Error Description: " & Err.Description & vbCrLf    
MsgBox errMsg, 16, "LogError"    
Resume Exit_CmdOk_Click
End Sub

 

[GummowN]

Before you copy the data from the import table to the live data, run validation on the data.

If the data is invalid copy it to another table and delete it from the import table.

Once you have finished validation copy the data from the import to the live table.

The data that did not get validated email as a spreadsheet to yourself, deleting the data from the table as you go.

I might be able to help you with some of the code if you like. It is always best to validate and get the imported data sorted before you append it to the live data tables

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.