Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: Help! Need ideas for linked Excel table

    Hi,

    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.

    Please help.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Please elobrate on what you mean by "I cannot define any of these in the linked Excel table."

    S-

  3. #3
    Join Date
    Mar 2004
    Posts
    82
    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.

  4. #4
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    You can define relationships in your queries, otherwise you will have to import the tables, rather than linking them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •