Access opens up as Read-Only

[rubenidas]

Hi, I created a simple database to manage contacts. I then wanted it to display in excel, so i used the option in excel to "Get External Data" and selected "From Access". I pointed it to my database and pulled the data from a query i have.

The problem now is that the excel file is the master data, not access. If i have the excel file and then open access, my database only opens as read only. I found out i have to close excel , then open access to allow access to be able to edit the data, but i do not want it to work like this. I want access to always be the master data handler and excel to only view the data, never actually be able to edit it from there.

How can i accomplish this?

 

[danielphayward]

I know that you can choose to open files always as read only by going into the properties of the file in file explorer. Would that do it for you?

So you could choose to always open up the excel file as read only.

 

[rubenidas]

I know that you can choose to open files always as read only by going into the properties of the file in file explorer. Would that do it for you?

So you could choose to always open up the excel file as read only.

well the problem is that other will be using the excel file, not necessarily me. And they will be doing other items in it...so they can't have it open read only.

 

[xenou]

When you use "From Access", click the Properties button in the dialog box after you select the table. Then switch to the definition tab in the connection properties dialog box. Look for the words Mode = Share Deny Write. Change that to Share Deny None.

THis will allow you to open Access and work with the data which is what you want. Changes in Excel will have no effect (They are not going to be updated back in the database if you change a cell value in Excel - of course test this a bit to be sure).

Note: haven't tried but you could also try changing Share Deny Write to Read

 

[rubenidas]

When you use "From Access", click the Properties button in the dialog box after you select the table. Then switch to the definition tab in the connection properties dialog box. Look for the words Mode = Share Deny Write. Change that to Share Deny None.

THis will allow you to open Access and work with the data which is what you want. Changes in Excel will have no effect (They are not going to be updated back in the database if you change a cell value in Excel - of course test this a bit to be sure).

Note: haven't tried but you could also try changing Share Deny Write to Read

Thank you!! It worked, both did actually. Not sure what Share Deny None vs Read do differently but i left it as read and it is working just how i want it now. I can update the data thru Access (only) as the master data , and have it show in excel for someone else to see and use for vlookups and stuff and not be able to edit it. Awesome!

Thank you thank you thank you!!

 

[xenou]

Good. Share Deny None seems to be the more popular choice, but both seem to work as far as I can tell. Something about file permissions is what is at issue - and share deny write is basically saying "open this file exclusively" (where "this file" means the access database file).