MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Error log for non inserted records?

  Asked By: Madeline    Date: Nov 09    Category: MS Office    Views: 682

I need to insert records into a sybase database. Using 2 for loops,
1 for the row, and another for the column, i'm able to loop thru e
worksheet and insert the records.

However, what happens if I have an error in 1 of the records, and it
doesn't get inserted, but I want the error record to be output to an
error log file so that I will know which record wasn't inserted.

I have also put in On Error Resume Next, so that even though there
might b an error record in the middle of the data, the insertion
would skip the error record and continue inserting the rest of the
proper records.

My problem is how to extract the faulty record and put it in an
error log file.



3 Answers Found

Answer #1    Answered By: Erika Evans     Answered On: Nov 09

Once you've engaged the On error  Resume Next mechanism, you can easily test
for success or failure by checking the status of the Err object. If it's
anything other than 0, something unexpected happened. I typically use the
FileSystemObject to hold an error log  open for quick updates with these
errors. After you've recorded the error, reset the Err object's value to 0
by using the Clear method. Quickie sample:

On Error Resume Next

YourData="Your Data"
YourSQLObject.YourRecordAddMethod YourData
If Err <> 0 Then
'gather the errors and send them to your log routine
LogError "Error: " & Err.Number & ", " & Err.Description & _
", Data: " & CStr(YourData)
End If

'When you've finished all your work, set
'the Error object back to it's natural state
On Error GoTo 0

Answer #2    Answered By: Jermaine Powell     Answered On: Nov 09

Am I right to say that the CStr function would force the excel row  to become a
string, allowing me to output  to the error  log file?

Also, is it possible to return the row at which the error row occured? Imagine
if I have thousands of records, if I just return the string, I would still have
to search thru the entire worksheet of records  to find the error row.

Answer #3    Answered By: Frank Butler     Answered On: Nov 09

CStr is my care method of being sure you can write the data  to the log. As
for writing your row, sure you should be able to do that using Excel's own
objects. When handling errors, though, be careful to not do something that
will cause a new error. The simpler, the better.

Didn't find what you were looking for? Find more on Error log for non inserted records? Or get search suggestion and latest updates.