none
Link Excel Spreadsheet to Table One-to-Many Relationship RRS feed

  • Question

  • Hi All,

    I am trying to create a relationship and I am at a loss. I don't know VBA... so that isn't going to be a route unfortunately, if that is part of your solution. I send people out on "individual jobs" sort of like contractors! My volunteer management system outputs an Excel spreadsheet with all of my volunteers' contact information. This changes on an ongoing basis.

    I want to link this "linked Excel Worksheet" by Member Number (this is the *one* connection) to my Jobs worksheet by member number (and this is the many).

    I can't figure out how to get it done so that I can edit directly in a query (which in turn would be a form... where I type in the member number and it populates fields... and I can enter the job details in the same query... I can get it to pull up information in a query, but I can't edit my jobs information in the query....

    Any ideas?? I attached some screenshots -- okay -- maybe not because I can't figure out how to get verified..... I want to create a one-to-many relationship from linkedActiveMembers.member_number (one unique record) to the tblDeployments.MemberNumber (many member numbers). I had another forum say I had to do things with Primary Keys and Foreign Keys, but I can't figure out how to set keys on a linked Excel Worksheet....
    Tuesday, July 4, 2017 3:31 PM

All replies

  • Hi James,

    Unfortunately, using a linked Excel worksheet makes the data "read-only." If you want to edit the data, you'll have to "import" the spreadsheet into a table in Access.

    Hope it helps...

    Tuesday, July 4, 2017 3:59 PM
  • So... I don't want to edit the data in the spreadsheet.  Does that make a difference?  In the combined query (jobs and spreadsheet), I only want to edit the data in the jobs fields... Is that possible?
    Tuesday, July 4, 2017 4:24 PM
  • So... I don't want to edit the data in the spreadsheet.  Does that make a difference?  In the combined query (jobs and spreadsheet), I only want to edit the data in the jobs fields... Is that possible?

    Unfortunately, yes it makes a difference. As soon as you link a table and a "read-only" spreadsheet, the resulting data set is also read-only.

    Have you considered using a form/subform setup? You can use the spreadsheet as the main form and the Access table as the subform.

    Hope it helps...

    Tuesday, July 4, 2017 4:34 PM
  • You cannot create an enforced relationship between a linked table and a local Access table.  For data entry you will need to use a members form with a deployments subform based in it, linked to the parent form on the member_number/MemberNumber columns.  The subform will be updatable.

    While it would be possible to violate referential integrity outside the form by amending a value in the foreign key MemberNumber column in the tblDeployments table, as the correct value will be inserted automatically into the column in the subform's recordset by the linking mechanism, referential integrity would be protected in the day to day operation of the database through the forms.  It would not be protected, however, if a member_number value were to be amended, or a row deleted in the Excel worksheet.

    Ken Sheridan, Stafford, England


    Tuesday, July 4, 2017 5:18 PM