Prevent duplicate data entry in Access using VB.NET?

[Rocker1]

Hello guys,

I am reading a downloaded textfile which has a fields like date,NAV and Name using VB.NET. The date on which the textfile is downloaded remains the same.After each readline operation it looks into the Access DB (tablename:Details) and updates the 3 columns(Date,NAV,Name).My question is how do i prevent the user accesing the already downloaded file or in other words how do i prevent my DB being loaded with duplicate values in VB.NET. Can anyone tell me how to go about it?

[janders468]

If I understand your question correctly you upload from a file which may or may not contain old information and you want to ensure that you don't reload information you already have.

You could do this with vb.net but I would probably use the database to enforce this as it can do so much more efficiently. That being said you will probably have to load the text file into a staging table which doesn't care about duplicates, then load the non duplicated table with the stage table data doing an outer join on the fields that make the table unique and only loading where the NonDupe_tbl values are null. Assume you have two tables Stage_tbl and NonDupe_tbl the sql would be as follows:

INSERT INTO NonDupe_tbl ( [Date], NAV, Name )
SELECT Stage_tbl.Date, Stage_tbl.NAV, Stage_tbl.Name
FROM Stage_tbl LEFT JOIN NonDupe_tbl ON (Stage_tbl.Name = NonDupe_tbl.Name) AND (Stage_tbl.NAV = NonDupe_tbl.NAV) AND (Stage_tbl.Date = NonDupe_tbl.Date)
WHERE (((NonDupe_tbl.Date) Is Null) AND ((NonDupe_tbl.NAV) Is Null) AND ((NonDupe_tbl.Name) Is Null));
 
 

You can run the queries from vb but the database enforces the rules, which will be more efficient and probably less error prone.