[2005] #error data value problem in ms access

[rhythm52008]

my windows service crashes because of an unhandled exception in an oledb select command on ms access, i found that tables in query contain #Error as data value in some fields , can anyone tell me how to handle this case

[stanav]

1. Fix the cause of the error if possible.
2. Use a Try/Catch block to handle exceptions and perform appropriate actions should an exception occurs.

[rhythm52008]

1.I already used try/catch block it cannot catch this exception.
2. Actually data is inserted by a window application designed in vb6 with ms access as backend. I cannot change that application becuase it's not mine. All i have to do is to copy data from ms access to centralised sql server and make reports only.
3. I only want to copy rows which wont contain any data value #error

[jmcilhinney]

As you say, your app is crashing because of an unhandled exception. Identify the exception and handle it.

That said, if you try to retrieve the entire table in one go then you may well get nothing because the single operation will fail. You may have to query the table one row at a time and ignore those rows for which it fails. It will still be the same exception you're getting now though, so you've still got to work out how to handle that.

[rhythm52008]

1. It throws an unhandled exception of mscorlib.dll which cannot be handled by try/catch block vb code.
2. I cannot select single row at a time i have to select whole table because of speed issues and i am using sql bulk copy class to copy selected rows from ms access to sql server automatically through my windows service

[jmcilhinney]

I don't think there's a magic solution. If the data can't be bulk copied because it's got errors in it and you can't change the data to get rid of the errors then I'd say that you can't use bulk copy. Of course, you could always show us the code you're using, a sample of the data and the details of the error. If you want informed advice then you need to inform us. We shouldn't be finding out the details in post #5.

[rhythm52008]

I need my query select only rows that don't have #error as data value