Newbie: Advice for checking errors

[microfich]

Hi, newbie here.

If you could hold my hand a bit please...

I am importing several large (up to 1MM rows) txt files into several Access
tables and want to check for roughly 200 format and value errors and ref.
integrity scenarios. This will not be an application, just a 2 or 3 time
effort to support a conversion. I entered validation rules for the scenarios
into the table design and while that works to a point at import time, no
specific errors are thrown and I'd like to be able to code to show all the
errors in a query or report (e.g. If table.field1.value <> 'xyz' then "Record
#n: field 1 value not equal to 'xyz' ".)

I'm thinking of creating a form with a button that would launch the error
checking but don't really know how to go about the coding of it. Any advice
on the best way to do this?

Thanks!

 

[Scott Lichtenberg]

Here's a very stripped down function that might point you in the right
direction. Add two fields to the end of your record. Call one ErrorFound
(Yes/No) and the other ErrorMessage(Text 255)


Public Function fnValidate()

Dim db as Database
Dim rs as Recordset

Dim lngRecordCount as Long

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

SysCmd acSysCmdInitMeter, "Processing", rs.Recordcount 'Set up meter
so you can see how far along you are

Do While Not rs.EOF

'Validations Go Here. If record fails validation, set error flag and
add comment
If rs!Field1 <> 1 Then
rs.Edit
rs!ErrorFound = True
rs!ErrorMessage = "Failed Validation 1"
rs.Update

etc.


rs.MoveNext
lngCount = lngCount + 1
SysCmd acSysCmdUpdateMeter, lngCount
Loop

SysCmd acSysCmdRemoveMeter
MsgBox "Done"


End Function

 

[Steve Sanford]

I would disagree with Scott about adding columns to your tables. With
"roughly 200 format and value errors and ref. integrity scenarios" to
validate/check, I would think a row might have multiple errors; two fields
wouldn't handle it.

I would create a table to hold the errors/problems.

---Structure----
tblErrors

ErrorID Autonumber PK
txtTableName text 'which table
lngRowID long ' PK from txtTableName
txtDescription text ' what error

The field "lngRowID" would also be a FK to the table. Then your report could
have groupings by txtTableName and lngRowID. You could also include the row
of data that has the error(s).


You don't say how you are importing the text files, but what happens when
there is an validation error? Is the row still imported? Is the field blank?

You might think about writing a custom import routine.

I have to import text files a few times a year, with around 3000 rows and 35
columns; 28 columns need to be validated.

BTW, all of the validation is done in code during import.... not in the
table "ValidationRule Property".

When there was an error, I used to write the data and the error to a text
file. But it was hard to search/print. Too many lines <g>. So I started using
a table and a report. Much easier now. I have one button that does the
validation but *doesn't* insert the record into the tables. When all of the
errors are fixed, I have another button the validates (again), then inserts
the records.

Anyway, that is my 2 cents worth.

HTH

 

[microfich]

Thanks to both of your for your help. Got me started in the right direction
and the VBA is coming back to me.

BTW, went with building a table with the errors (one for each .txt file)