Unanswered: Help! Need ideas for linked Excel table
I posted this question earlier, but did not get replies. I hope someone would help me out.
I have a linked Excel spreadsheet table which contains monthly sales data by customers and by product (ie, Customer --> Product # --> Month --> Rev$). I have to link this in a query in Access that also links several Access tables (Customer, Purchasing Organization, Contract, etc).
To preserve data integrity, a unique record in the sales table would have to have a composite primary key, composed of a customer#, a contract#, the product#, and month of sale. I cannot define any of these in the linked Excel table.
I do not wish to do an upload as: (1) the amount of data will continuously increase, and hence overload the database; and (2) since the data is funneled through several enterprise systems before it reaches Excel, I have run into the problem of data typing when I do attempt to get it into Access.
A link serves me best, I think, and I would like to work with this, if I can resolve the problem outlined above.
Ok. I have a linked spreadsheet that shows up in the Access database window. This contains sales data.
If I now open the table in design view, I first get a Windows message saying: Table "ABC" is a linked table with some properties that can't be modified. Do you want to open it anyway? If I say yes, it will open in design view.
Now, if I define the primary keys (Access will seemingly allow you to do this) and then attempt to save the table, I have another Windows message box that says: Database can't save property changes to linked tables. Do you want to continue anyway?
Irrespective of your answer, the changes you make (ie, defining primary keys) will be discarded. This is what I meant.
I need to have the PK's defined and also the relationship in the query that links several other tables so that the report that comes off of this works correctly. I cannot define the PK's and I also cannot define the relationship to preserve referential integrity.