Primary Key in a linked table

[Rats]

I am setting up an access frontend to an excel worksheet. I am creating linked tables using the excel input fields. I find when I have created the tables I can't create a primary key as access won't allow modifications to the linked table. Has anyone any suggestions as to how I can set a primary key on these tables. Thankyou

 

[Pat Hartman]

Excel files (or other flat files) don't support the concept of a key. Why are you trying to define one?

 

[Rats]

Sorry for the delay in reply I have been on a break. I have a very complex spreadsheet which works very well but is about 4mb in size and causes storage problems when saved because I have a large number of clients and they have one spreadsheet each and this number is growing.

I want to use Access to provide the input data and use the the XL spreadsheat as a template calculator which has its variables populated from access. I can link the ranges in question but as I can't create a relationship I have no way of linking the client tables with the input data.

Exporting the data is no good because the cell references in excel become invalid if the sheet is overwritten.

I am lost in this one and as the calulations are so complex I don't have the skill to do them in Access which is where they probably should be.

So basically I need to know how to calculate in XL and then use that data in a relational database.

Any help would be appreciated.

Thanks for your help.

Peter

 

[Pat Hartman]

I'm sure that your spreadsheets are not normalized so doing a straight import into Access isn't going to do anything for you. You would need to normalize the spreadsheets so they are broken down into appropriate tables. Once you do that, it is likely that the calculations won't actually be a complex as you think they are.