Jim Lawrence
accessd at shaw.ca
Tue Jun 21 19:57:35 CDT 2011
Thank you Asger. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Tuesday, June 21, 2011 3:06 PM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Avoid making an Access database read only when imported to Excel Hi group, Did you know that importing an Access database table to an Excel spreadsheet makes the database read only when opened in Access? That's so! And the read only state in Access isn't released until the Excel file holding the import is closed and the database is reopened in Access... This has puzzled me for awhile - but I've found a workaround avoiding this really annoying behaviour: If you edit the connection string in Excel (use Data -> Connections -> Properties -> Definition) you will see a statement in the connection string telling "Mode=Share Deny Write". This is what's making the database read only in Access. Change this value to: "Mode=Share Deny None" - and the database will be read write in Access. What I don't understand is: Why did MS create this weird setting for the connection string? Why should opening an Access table in Excel make the database read only for Access? IMO this is just stupid. When refreshing an imported table in Excel you are not writing any eventually changes from the imported table in Excel back to the Access database - quite the opposite: you are overwriting eventually changes in the Excel spreadsheet with the actual data from the Access database. And that's what's supposed to happen. So why the hell should the database bee made read only in Access? But wait: Maybe MS has some profound wisdom using "Share Deny Write". If so, please tell me. Asger -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com