Avoid file duplication input on table with VBA

[ccollalto]

Hello guys,

I've been looking for a solution on this for quite a while now and wasn't able to find a way around thus far:

Simply put, I have an Access frontend database where I get a bunch of text files imported everyday, data is later used in Excel. As the files are generated from the backend in the same directory with names according to date, I have a form to import only the files with the dates I need. No big deal if I hadn't had a number of users that "forget" they have already imported the files and duplicate the whole thing.

No expert VBA and SQL dev, assuming I have a table to log which files I've already imported, I've been trying to get a message box to pop up with the following piece of code (relevant code below, of a lot of things done in the process):

********************

NAME_FILE_1 = "File_" & DATA_FORM & ".TXT" 'File that I'm getting from the network

Set DUPLI = BD.OpenRecordset("SELECT NAME_FILE FROM TLOTE WHERE NAME_FILE='" & NAME_FILE_1 & "'") 'TLOTE is the table where the log is, trying to look there if the file has already been imported

If NAME_FILE_1 = DUPLI Then 'Runtime errors happening here

MsgBox ("The file has already been imported.")

Else

MsgBox ("Bring it on.")

End If

********************

I was expecting it would work just fine, but I'm obviously wrong about how the SQL select is set onto DUPLI, runtime errors 13 or 3001 unfold depending on some different SQL statements I try.

Any help appreciated.

Thanks. Caio

 

[ranman256]

Im guessing the target file is not indexed, thus illiminating dupes.

You could do 3 queries to import,
Q1 to import the data to a temp table, tTmp
Q2 to delete (or mark them) the existing items in tTmp that exist in the target table via join.
Q3 append the tTmp to the target table.

 

[ccollalto]

Im guessing the target file is not indexed, thus illiminating dupes.

You could do 3 queries to import,
Q1 to import the data to a temp table, tTmp
Q2 to delete (or mark them) the existing items in tTmp that exist in the target table via join.
Q3 append the tTmp to the target table.

ranman256 thanks a lot for your response, unfortunatelly the files are way to big to import into a temp table, then into another, just to mark/delete duplicated data. I am really looking for a way to check if the file name I'm trying to import already exists on the log table - before anything else - therefore avoiding wasted time.

 

[Micron]

"'") should be "')" But based on what you wrote, I figured you were putting files in a network folder and storing the filename in a table. Your response to Ranman suggests you are importing data from those files. Either way, my suggestion may help, but indexing tables to prevent duplicates is the way I'd go if you are importing data. During execution of the append query, you suspend warnings that you'd otherwise get about duplicate records.

I'm assuming
- DUPLI is dim'd as a recordset and BD is dim'd as a database somewhere in code
- DATA_FORM is a form control

then after Set DUPLI = BD.OpenRecordset... I would do

if DUPLI.recordcount >0 then
   msgbox "File has already been imported."
  exit sub
end if

 

[ccollalto]

Hello Micron, thank you for taking the time to answer. You are correct, trying to put it as short as possible I've omitted those details. As you've mentioned, I am taking files from a network folder and storing the file name in a table. DUPLI is the recordset and BD is assigned as current database earlier in the code, DATA_FORM is the form control where the date is assigned to a string, which in turn determines which files I'm importing from the folder, also earlier in the code.

Your suggestion is to count the number of records in the recordset, assuming there is any, a duplicate exists. I just tried that and it works beautifully. This also answers my question about how the recordset is being fed by the SQL select statement and the runtime errors I was getting. It came to be:

Set DUPLI = BD.OpenRecordset("SELECT FILE_NAME FROM TLOTE WHERE FILE_NAME='" & FILE_NAME_1 & "'")
                
                If DUPLI.RecordCount > 0 Then
                    MsgBox ("File has already been imported.")
                    Exit Function
                Else
                    MsgBox ("File will be imported.")
                End If

Thanks a lot for the support of both of you guys. Cheers from South America.

Caio

 

[Micron]

You're welcome! Where in SA? Maybe you should add that to your profile. I was in Salvador and Rio last year, but SA is a big continent. Perhaps you are nowhere near those places...
I see that I missed the mark in my first post. The ending bracket was part of the openrecordset function ().

 

[ccollalto]

Oh really? Will look into that. I hope you enjoyed your stay, I'm brazilian from São Paulo, closer to Rio than Salvador. I see you are from Ontario, Canada. Nice country, nice people.

 

[Micron]

I did! Stayed very near Copacabana, walked Ipanema, did Corcovado & Sugar Loaf, tours etc. Spent a few days after getting off the cruise ship. Nice people, but it took a while to figure out why I kept getting lime when I asked for 'lemon' wedge! And beer is cheaper than soda. How great is that?!