[AccessD] Compound Unique Keys...Again

[Charlotte Foust]

​I'm not sure what you mean, Gustav.  I've certainly tried append queries
but with the same uneven results. The problem seems to be on the Access
table end in the accdb.  It isn't keeping duplicates out even though the
unique key is set and none of the fields is null and all are required.  If
someone slips and re-imports the same data file, we wind up with duplicate
records when the unique compound key should have kept them out.​  The text
file is one big flat file of about 75 fields.  It's getting broken into
chunks on the import but there has to be a unique transaction  (and ID) in
the primary table to keep the data straight.  Filtering a query on about
six fields to avoid duplicates is asking for "query too complex" responses,
if it runs at all.

I've used this technique forever in Access to keep duplicates out, and
suddenly it isn't working.  My hair can't get any grayer, but I'm pulling
it out in handfuls over this!

Charlotte Foust
(916) 206-4336

On Thu, Apr 2, 2015 at 7:39 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Charlotte
>
> Haven't seen this, but couldn't you link the textfile, then run a grouping
> append query using the linked file as source?
>
> /gustav
>
> ________________________________________
> Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Charlotte
> Foust <charlotte.foust at gmail.com>
> Sendt: 2. april 2015 16:33
> Til: Access Developers discussion and problem solving
> Emne: [AccessD] Compound Unique Keys...Again
>
> I'm beating my head against the wall on this.  I set a unique key on
> multiple fields and Access 2010 allows me to import the same data
> repeatedly!  Since the import is from a text file which has no unique
> values in it, I'm finding it impossible to keep duplicates out.
>
> Anyone else encountered this?  I may have to set those multiple fields to
> the PK, which is a nuisance.
>
>
> Charlotte
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>