How do I add Unbound Column in a Continuous Form while allowing each row in the unbound column to be a unique user input

Question

I have an Excel table with Planned production information. I have an Access DB where I want to store this planned production information as well as Actual production information. I am using an Access form to allow users to log their actual production information according to the planned information. Using a continuous form I can pull all my planned information by having the Excel spreadsheet as a linked table. The problem is when I add an unbound column to this table for users to enter their Actual production information, all the row values for the new column is the same. Continuous Form

I tried looking for control properties and using VBA code but I could not achieve the result I am looking for.

Answer 1

Options:

1. get rid of Excel component and go all-Access instead of trying to make Excel work like a db

2. link to Excel so it can be edited, however, I doubt this would work nicely in a multi-user environment

A query object can establish direct link to Excel worksheet and allow edits and adding rows, but not delete rows. Example:

SELECT *
FROM [Sheet1$] IN 'C:\folderpath\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];

IMEX=0 allows edit
Anything else would be ready only. External Data wizard will usually assign 2 for Excel link.

3. complicated VBA code manipulating Excel file to write data, again, probably won't work nicely in a multi-user environment