Darryl Collins
darryl at whittleconsulting.com.au
Thu Oct 2 18:30:08 CDT 2014
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