External database linking

[Keith]

I have linked our Access database to an external dbase table in our contact
manager and know what the primary key is in the contact manager table I am
linking to. I have created a relationship between the primary key in the
contact manager database and a corresponding contact ID in another table
"sales orders". This should be a one to many relationship but the
relationship shown in the relationship window as "indeterminate". I am
trying to create an AutoLookup query that automatically fills in data. One
condition that must be met to do that is that the tables must have a
one-to-many relationship. I was able to create an AutoLookup query in
another instance with two tables in Access with a one-to-many relationship,
but am unable to accomplish this between the externally linked table and an
Access only table.

When looking at the linked DB table in Access it shows no primary key. In
the contact manager the Accountno field is the primary key as stated in
documentation.
So I attemped to change the Accountno field property to primary key in the
design view of the DB table that is linked externally. But the change will
not save. So i made sure the Accountno field in this table was indexed - no
duplicates.

I am still unable to automatically fill in data that is related to the
contact manager table. Any suggestions in how to get this one-to-many
relationship to the externally linked table?

 

[Vincent Johns]

Keith,

What I'm about to suggest may not work, but I'm hoping it may help.

Can you create a local Table (perhaps using a Make-Table Query) that
contains only the field from your external Table that the documentation
claims is its primary key? You'd have to keep doing that, any time the
external Table's contents change, but it might get around your problem.
You could check, as you import into this local Table of key values,
that the values are indeed unique. You could then define your
AutoLookup Query using these key values, and linking to the external
Table to get the fields that you really want to display.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.