[AccessD] Excel Linked Table – “Sharing” Problem

Sure... There are many ways of doing this.  But the key is to 'unlink' the live connection between Excel and Access.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Friday, 3 October 2014 9:47 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com