Distinguish If Data Already Exists In a Table

[curscascis]

Hey guys I need some suggestions. I am new to the world of VBA and access but I've gotten a more than adequate grasp over the last two months. I currently need a way to check to see if a a field already exists. To be exact I have an access application that imports data from multiple sources and saves the file name of these imports into an import tracker table. The table has four fields, file name, Date Imported, Import Type, and Number of Records. I am writing some code that checks a Boolean given by a form. If the Boolean is true It skips over checking. If it is false It needs to check my import tracker file names to see if the file was already imported. If it was then it will skip over the file and the algorithm will check the next file given by an array. So I was wondering..How would I go about making it check if the file already exists in the table. I was thinking of turning the File Name Field into a primary key, since there will only ever be one File Name of every type. What are your thoughts? Thanks!

 

[Mihail]

Two ways:
1) Use DCount function
2) No need to set the field as primary key. Set the Index property for the field to Yes-No duplicates

(Your idea should work too but is not preferable)

 

[curscascis]

Alright well let me explain something else. So there are two tables dealing with the records (plus one for errors). One of the tables is the main storage of the imported files. Then there is another table the has the same fields but is temporary and only holds the current "run" of files imported. So when the code checks whether or not to import it first import to the temporary table, and if we hit a button to append the data to the main table I would like it to overwrite the data that is already there. But if it is indexed, won't an append query with the same file name be ignored, actually I can have it run an update query instead if it has a match.Now there are already hundreds of files in this table since it is coming from an old system, how would I got about using DCount() to remove duplicates, because I cannot set it to indexed mode(no duplicates) while there are duplicates in the file. I am a little unsure of how I would go about using the function? I was thinking of using a delete query but I am not sure how that would find the duplicates either.

 

[Mihail]

Create a back up of your DB before proceed !
You can use THIS method to remove duplicates from a table.
I advice you to create an index, not a primary key.

Also take a look here.

 

[curscascis]

I always backup everything I do, even the unimportant things. And I discovered how to delete duplicates after come clicking around. Thanks alot you have been very helpful!