Primary Key in Linked tables?

[shakenama]

Deeper and Deeper in the rabbit hole I go -

I have a DB that is linked to three Excel sheets. The data is easily updated on a weekly basis from the sheets in Access. (no prob there)

The issue I have is creating a Primary Key in the 3 tables to create proper relationships (One-to-Many)
I'm unable to modify tables that are linked. (or can I ?)

I've created a column in each spreadsheet for a unique number, but cannot define the Primary Key in Access. Access is saying "...can't save property changes for linked tables" (yes...I'm trying to create a decent query using both tables)

Is there a work-around for this?

BTW - this website is great - helped me out many a times

 

[fkegley]

I am pretty sure that you cannot do this as you have it now. Access will not let you fool with linked tables properties from the linking database, only from the database where the tables physically reside.

However, you might try developing a Make-Table query that puts the data from the linked tables into physical tables. Then you could set the properties of those tables.

You could probably arrange for this to be done via code each time you open the database, so that you would always have the current data from the spreadsheets.