[AccessD] Excel Linked Table – “Sharing” Problem

I would think it would be easier to kill the excel table, then use
DoCmd.TransferDatabase with acImport to recreate it. Why bother with ADO
which leaves you responsible for re-coding if field names change?
On Oct 2, 2014 7:33 PM, "Darryl Collins" <darryl at whittleconsulting.com.au>
wrote:

> Yes... some flavour of this would be my solution too.  Personally I would
> use ADO to push the data into temp table location.  That way it doesn't
> matter what app is open.  The user can just refresh the data whenever
> without any locking issues.
>
> Cheers
> Darryl
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
> Sent: Friday, 3 October 2014 5:15 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Excel Linked Table – “Sharing” Problem
>
> "One user will still need to use Excel to make updates to the data as they
> have for the past 10 years.  They do not want to give up this approach."
>
> A classic resistance to change problem. S/he does not want to change
> "because"...
>
> However, assuming you cannot persuade the CEO (or whomever it is) to join
> the 21st century, if the other users only need to see the current data then
> what about this approach.
>
> When the Access application opens, import the data from the Excel file to
> a table (using an import spec instead of a linked table), and use the table
> to present data to the Access users. Then at intervals reimport the data.
>
> Lambert
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Thursday, October 02, 2014 12:48 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Excel Linked Table – “Sharing” Problem
>
> All,
>
> We have an existing Excel file that I would like to use as an Access
> Linked Table in order to provide easier access to the data for our users.
>
> I built a small Access 2007 application with several reports that the
> users really like.
>
> One user will still need to use Excel to make updates to the data as they
> have for the past 10 years.  They do not want to give up this approach.
>
> If Excel is opened first, the Access application works fine.
>
> However, if Access is opened first (with the Excel file as a linked
> table), Excel does not open nicely and we see this message.
>
> “Excel cannot open the file ‘Test.xlsx’ because the file format or the
> file extension is not valid.  Verify that the file has not been corrupted
> and that the file extension matches the format of the file.”
>
> Evidently, when Access opens the Excel file as a Linked Table it does
> something to “lock” the file or something along these lines.
>
> Is there a way to prevent this?
>
> Thanks,
> Brad
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>