Keying linked files

[JLamb]

I have two linked files that are generated from mainframe sales reports. I
want to key these linked tables as the reports contain subtotal and total
lines that are obviously unneccessary and even cause some problems. Does
anyone know of any way to key - or otherwise automatically remove - these
tables so the total rows won't show up (as they will have nulls in key
fields)?

 

[Pat Hartman]

If you define a primary key for the table, the records with null values in
the pk will not be appended. You will get a summary message - "x records
not added because ...." which you can ignore.

 

[JLamb]

You can't define a key on a linked table once you set it up. So the keying
has to be done in the linking process and I haven't found a way to do that
yet. That's what I was hoping someone could help me with (or some other
alternative to exclude the records I don't need that are lacking "key values")

 

[Pat Hartman]

"You can't define a key on a linked table once you set it up"
Of course you can. You just need to do it from the db that physically
contains the table you want to modify. You can't do it from the link db
side. Alternatively, you can use DDL or DAO but neither is appropriate for
a one time change.

 

[JLamb]

It's not a linked TABLE it's a linked FILE. So once it's linked you can't
modify it's design (i.e. create a key). So you have to define a key in the
linking process. The problem is that I haven't been able to discover a way
to do that (if it's possible at all). Or an alternative way to restrict the
data in the way I would like.

 

[John Nurick]

Just use queries that return only the records you're interested in.

 

[Pat Hartman]

Sorry, I misunderstood your first post. You cannot add a key to a text file
and even if you could, Access wouldn't allow the key to be created if the
table already contained records that violated the uniqueness of the key. So
you have two options that I can see:
1. Import the data into a table that does have a PK defined. The records
with null values in the PK will not be appended.
2. A better choice - Use selection criteria in your queries to ignore rows
where the PK field is null.