Since this is a linked table, Access cannot add a primary key field to it or make any design changes.  Any change to the table design has to be done on the Excel end.  Any linked table in Access can only have its design modified at the source application.

You can't have a field on one end but not the other.  They wouldn't be linked anymore if that was the case.  "Linked" means what you see in Access must be identical to what you see on Excel.  So you can't add a PK on Excel and not have it shown on Access.

Therefore, if you need your Access linked table to be normalized and have a PK, the source table in Excel must be the same way too.  No way around that.

A better way would be to do the opposite thing to what you are doing: have Excel link to Access data instead of the other way around.  Being a database program, Access should serve as your data depository, not Excel.  If other programs need the data, they should grab them from Access instead of the other way around.  In Excel, you can easily link to Access data from the ribbon: Data -> Get Data -> From Database.

Import, not link, the data from Excel into a table in Access, fix the PK, then have Excel link to the Access table instead.

Kevin, excellent explanation. I very much appreciate your great comments on this. It really gives me a much better insight, and now I won't pursue a dead-end troubleshooting process of trial and error.

Unfortunately, your suggested workarounds won't work for my scenario, because I scape the data to excel with very few mouse clicks. My whole workflow is deigned to take me from raw data to an Access report in an almost automatic fashion. I right-click the table from the web page, I export it to excel, which is my sole option from the menu at the webpage. The dynamic link I use between excel and access eliminates the labor of importing the data from excel to access. At one time, I was successfully importing the data to Access. These days, the link from excel as the origin, to access as the destination, is very seamless. The link for me is paramount to an efficient medium to telegraph the data to access. The primary key is of lesser importance, and I could live without the primary key, however, the lack of the primary key has the downside, where my access report has hundreds of records, and each record lacks an easy record number to reference. (i.e. record number 129) There is, indeed, a 10-digit serial number for each record, but some records are duplicated (for good reason) because, occasionally, some of these records can have multiple statuses that get reported more than once (a pseudo-one to many setup, that originates within the origin table at the webpage). I suppose I can just insert a left-most column within excel, and enter sequential numbers as a copy sequence or a formula within the first column, but I am trying to keep the manual processes to a bare minimum, as I produce my reports daily. The data at the webpage table changes every 24 hours.

Your explanation is very intriguing, and gives me a much better understanding of how the dynamic link functions, and the underlying rules of how it is engineered to work.

I have gained much from your comment here. Manny thanks to you, Kevin. This will, indeed, serve me well for my design approaches in the future.

Why don't you just import the data every time it's updated in Excel? You could probably assign a composite key based on a combination of fields. It's very hard to say without seeing a sample of the data.

Richard, What about a one-to-one relationship in Access? I can leave the excel-linked-table in access alone, so that the access table still matches the excel file field to field, identically. Then I can make a second table that only contains 2 fields, an autonumber field, and a foreign key field. Then I could relate the two tables in a query. I can then base my report on that query instead. This should give me the original data, plus a primary key, without disturbing the table that is linked back to excel.

Hi Mark, you can only link the two tables if they both have keys that can be joined.  If your Excel table has no keys and "simply contains data" as you said earlier, then you can't join it to anything.

You said earlier that you have a way to "scrape" data to Excel.  Maybe you can scrape them to Access instead.  Access and Excel both use VBA, and maybe you can modify your Excel code to suit Access with not too many changes to the code.

Let me see a sample of what you're dealing with. Upload some screen shots.