Linked excel sheet not updating

[747skipper]

I fear I may have misunderstood something elementary. Please can someone can tell me what.

Every 6 month I need to print double-sided A4 coversheets. Side one has an individual's details from a database I maintain, side 2 has an order form.
A barely computer literate member of our association produces this order form as an Excel sheet. (I don't know why and I can’t change this.)

I have an unbound Object frame on my Access report and when I create it, I set the Source Doc to the path of the Excel sheet and set OLE Type to linked. It will not let me create one with SourceSoc blank.
This works! But six months later when I replace or update the Excel sheet the Access report does not change, indeed I can delete the Excel sheet and Access will happily continue using the original.

I have definitely NOT set the type to embedded.
I cannot change the sourcedoc in code. (I have tried a couple of examples from Microsoft but they always fail at 'Create linked object.' ' OLE1.Action = acOLECreateLink'

I have tried Bound objects, which logically sound like what I need, but with even less success.

Filling a report field of fixed size and location with data from another source sounds so simple., what am I doing wrong please?

 

[Gasman]

I can only get the Type Allowed changed.?
The property above is Read Only?

 

[747skipper]

I can set whatever I like but it still ignores changes in the file specified, I can delete the file name but the image stays the same.
(This a report not a form.)

 

[Gasman]

Have you tried a Refresh?
Try on a local file?

 

[747skipper]

Thanks Gasman but the file is local and I can shut down for 6 months and it doesn't help

 

[Ken Sheridan]

In a case like this, where the report is opened only periodically, I would not use an object frame, or indeed reference the Excel data directly. I would import the Excel worksheet into a local Access table, which I'd empty first, and use a query on this table in the report, as the RecordSource of a subreport. The attached file illustrates how to browse to an Excel file and import it into a table. It then goes on to decompose this into a correctly normalized set of tables, but that need not concern you.