importing without duplicates

[t.roff]

I will be importing data from text files every week or so. In the table that
I am importing into, there are two fields that can have duplicates but not at
the same time: ie. field 1 field 2
A 1
A 2
B 1
B 2
is ok, but
A 1
A 1
is not.

I've tried setting up a unique index for the two fields together. Upon
importing, I get a key violation message, but some of the data still gets
imported into the table (a few columns are missing, making a real mess).
What I really want is a message that warns me that the import will create
duplicates of the two fields in question and the option to stop the import
process or overwrite the existing data.
Any ideas?

 

[John Vinson]

I will be importing data from text files every week or so. In the table that
I am importing into, there are two fields that can have duplicates but not at
the same time: ie. field 1 field 2
...

I've tried setting up a unique index for the two fields together. Upon
importing, I get a key violation message, but some of the data still gets
imported into the table (a few columns are missing, making a real mess).

There's something else wrong with your query then. As you've expressed
it, what SHOULD happen is that the first instance of any set of
records with duplicate values on these fields will get imported,
intact; additional records which would create a duplicate will be
excluded from the import, with a warning message. Note that if there
are additional fields in the text file, you'll get only one record -
the additional fields in subsequent duplicate records will simply be
discarded.

If this isn't what's happening, please post the SQL view of your
query.

What I really want is a message that warns me that the import will create
duplicates of the two fields in question and the option to stop the import
process or overwrite the existing data.
Any ideas?

That would get a bit complicated. What you might need to do is write
VBA code to *link* to the text file (not import it), check for
duplicates using a query joining the linked table to the Access table,
warn the user, and run an Append query from the linked table (or not,
depending on the user response).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[t.roff]

I am importing data that depends on place, date and time of day. The idea is
to prevent importing the same data twice (which would mess with statistics).
Using the index, the first instance of duplicate records does get imported
(like you said). That is enough to mess up the data in the table.

So, I think that the second approach that you suggested is the way forward.
I'm happy with linking the new data to the table:

DoCmd.TransferText acLinkDelim, ImportSpec, "Data", Forms![data
import]![filename textbox], no

Next, I'd want to check that there are no records in the main table and the
new linked table that have identical entries for 'Place' AND 'Year' AND 'Day'
AND 'Time'
Any suggestions? (there are other fields that are ok to be different)

If no records are found then I'll import the new data:

DoCmd.TransferText acImportDelim, ImportSpec, "Data", Forms![data
import]![filename textbox], no

But I'll put some kind of text box if records are found:

MsgBox "Warning! This data has already been imported. Cancel the import?",_
vbYesNo
If iAns = vbYes Then Cancel = True
End If
If iAns = vbNo Then
DoCmd.TransferText acImportDelim, ImportSpec, "Data", Forms![data
import]![filename textbox], no
End If

Finally, I'd want to automatically delete the linked table, is there a way
to do that?

Any suggestions are much appreciated.....

 

[John Vinson]

I am importing data that depends on place, date and time of day. The idea is
to prevent importing the same data twice (which would mess with statistics).
Using the index, the first instance of duplicate records does get imported
(like you said). That is enough to mess up the data in the table.

So, I think that the second approach that you suggested is the way forward.
I'm happy with linking the new data to the table:

DoCmd.TransferText acLinkDelim, ImportSpec, "Data", Forms![data
import]![filename textbox], no

looks good...

Next, I'd want to check that there are no records in the main table and the
new linked table that have identical entries for 'Place' AND 'Year' AND 'Day'
AND 'Time'
Any suggestions? (there are other fields that are ok to be different)

SELECT <whatever>
FROM maintable INNER JOIN [Data]
ON maintable.Place = [Data].[Place]
AND maintable.[Year] = [Data].[Year]
AND maintable.[Day] = [Data].[Day]
AND maintable.[Time] = [Data].[Time];

will show all the duplicates; a Totals query will count them.

If no records are found then I'll import the new data:

DoCmd.TransferText acImportDelim, ImportSpec, "Data", Forms![data
import]![filename textbox], no

No need to do this a second time. You've already linked them once, no
need to do so again.

But I'll put some kind of text box if records are found:

MsgBox "Warning! This data has already been imported. Cancel the import?",_
vbYesNo
If iAns = vbYes Then Cancel = True
End If
If iAns = vbNo Then
DoCmd.TransferText acImportDelim, ImportSpec, "Data", Forms![data
import]![filename textbox], no
End If

And here you're linking the same data A THIRD TIME - not needed at
all! Instead, run an Append query to append the data in the linked
table to the main table.

Finally, I'd want to automatically delete the linked table, is there a way
to do that?

CurrentDb.Tabledefs("Data").Delete

will just delete the link (not the data).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps