Adding additional fields to a linked excel table in Access where the data in those additional fields is retained as the Excel data source is updated

Anonymous
2025-01-22T15:48:43+00:00

Hello all,

I am looking to understand how best to link an excel spreadsheet (that contains a list of courses) to Access and where I can add additional 'comment' type columns to that linked data and eventually query it all or view bits of it in a form.

When the linked excel spreadsheet is updated (Usually daily i.e. some courses are removed from the list, some added, most remain and data for each course may change), I would like the additional columns that I have added in Access (and the comments that have been added in those columns) to remain 'attached' to courses where those courses are still present in the linked data as it changes.

I've tried to do this in Excel with a self-referencing power query and managed to get this to work in a fashion, but as the number of columns I am using is 90+, hiding and unhiding columns after refreshing the data, breaks the query so I'm reverting back to Access to see if there is/is an easier way to do this.

I appreciate any suggestions or pointers :-)

Thanks,

C

Microsoft 365 and Office | Access | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Duane Hookom 26,935 Reputation points Volunteer Moderator
    2025-01-22T16:06:32+00:00

    90+ columns suggests an unnormalized application. You are possibly still thinking “spreadsheet” when you need to model your data in a relational solution.

    You can create a table in Access that has a column that matches a unique value in your linked Excel table. Your Access table could be updated as a subform on a from bound to your Excel table.

    Bottom line, we need more information about your existing table/fields to provide more quality assistance.

    Was this answer helpful?

    0 comments