VB/Access Data Validation

[stealbreeze]

I am developing a VB standard exe front end to an Access database. On one of my forms I import data from txt and Excel files. I need to validate the data in these records before loading into the database. Specifically, one of the fields being imported is a required foreign key that I need to validate against the primary key of another table.

The way that I plan to do this is running a SQL query (using ADO) and checking the record count of the resulting recordset. If it is greater than 0, then the field has already been set up and is valid. What other ways are there to validate a specific field against another table? I suppose I could load all valid fields into an array prior to starting the import, then search the array to validate each record. Any suggestions from the gurus?

Also, in this same project. I am using DAO on some forms and ADO on others. Is there any problem with this (other than increasing the overhead associated with the exe)?

Would greatly appreciate any comments. I am still working my way up the learning curve.

SB

[Dalida]

Imagine if you query recordset for every field from your text file how much time it should take. If fields in text file and in recordset are sorted properly, you can just read completely your text file in any temporary array or collection and then loop this collection. Skip records that you don’t need.
Using together ADO and DAO gave me other time some problem. It was many time ago, I think I had to explicitly write DAO.YourRecordset, but I am not sure, it was long time ago.

[coolbiz]

My two cents:

1. ADO or DAO - choose one.

DAO seems to have few additional useful features such as compacting and repairing M$ Access DB but the recordset retrieving engine is the old one. M$ keeps it for backward compatibilty purposes.

ADO is more general and the engine is much better than DAO. It does not support compacting or repairing M$ Access or nothing but this is the new technology that M$ is going for.

Use both? Not a good idea.

2. Verify imported data

If you enforce integrity of the relationship between table 1 and table 2, inserting a record in table 1 without existing foreign key in table 2 will cause an error. This way you can trap the error when trying inserting each records into table 1.

Read the table 2 data and store it in memory is okay as long as only 1 user using the app. If you have more than 1 users, then you might have data integrity issue.

Querying table 2 before inserting data into table 1 is okay as long as you have few records to import. As you already assessed, the more records you have, the slower the operation would be.

Good Luck,
-Cool Bizs

[stealbreeze]

Thanks for the input. I initially intended to only use DAO for this application since it is a single user, MS Access DB. I just started to learn ADO and wanted to try it out, so I wrote some code for the importing of text files into the db. It appears to work ok with the existing DAO code, but I can easily change it to be consistent.

SB