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.

 

[Pat Hartman]

Due to a lawsuit that Microsoft lost several years ago, they had to remove the ability of Access to update linked spreadsheets. I believe that all you can do is append records to the end of a linked spreadsheet so you might want to rethink your design.

The definition of relationships has nothing to do with the ability to create queries that join tables. Relationships are defined so that you can enforce Referential Integrity and that's it! Not that that is a small thing. In fact, it is a very large thing when creating proper relational databases. But in this case, you cannot do what you are asking for several reasons. The most obvious of which is that only tables in the same database can have relationships that enforce RI. Since it is only possible for the Jet engine to control a single dtabase, there is simply no way it could handle RI that crossed databases. Each database would need to have the identical RI defined and that can't be ensured either so you are left with the only practical solution - RI exists within a single database. Since the spreadsheets are external to the database, even if the updateability hadn't been forcefully removed, you still would not be able to enforce RI because they are external files.