Read Only Linked Table

[Jalex]

Just can't figure it out.

- I have a Source.accdb which is a database back-end.
- One of the clients wants to link a table in Source.accdb to his Client.accdb.
- The owners of Source.accdb do not want the client to be able to do anything but read the data.

How can I do this?

I have tried to create a query and set it to snapshot in Source.accdb but this doesn't seem to work as the only things that can be linked are tables.
I suggested using another file but the client wants to be able to use his Client.accdb as he has lots of other data in there.
One solution I found was to use goups and user levels, but they were dropped in 2010 version.
The ideal solution would involve a Sharing.accdb which I could freely update with shareable information from Source.accdb and anyone could link to (I don't have free access to Client.accdb).

Thoughts?

Access version: Access 2010

 

[AccessMSSQL]

You can make the linked table hidden and have user access data through read only forms.

 

[AccessMSSQL]

Or, you can just make that data available through a linked spreadsheet to the source.accdb tables. This will automatically be read-only and you wouldn't have to give client.accdb access to the table.

 

[Jalex]

You can make the linked table hidden and have user access data through read only forms.

The problem with this approach is that nothing stops the Client from unhidding them and modifying the data.

Or, you can just make that data available through a linked spreadsheet to the source.accdb tables. This will automatically be read-only and you wouldn't have to give client.accdb access to the table.

Through an Excel spreadsheet? OK, Ill give it a try.

 

[Jalex]

Or, you can just make that data available through a linked spreadsheet to the source.accdb tables. This will automatically be read-only and you wouldn't have to give client.accdb access to the table.

The problem with this approach is that in order to get the latest data in Client.accdb from Source.accd they should open the excel file and trigger the refresh (refresh on open). Which is not what they are looking for.

 

[AccessMSSQL]

hmmmm...can you just lock down the Client.accdb? make it an accde file and disable the navigation pane?

The other thing you could do is go backwards a little bit. If you have Access 2003 version someplace - create a new database there with groups and user permissions - then import all the objects from Client.accdb into the new database. You can then set up your users and groups - set permissions on the objects - then, convert the DB back to your 2010 version. You can still use mdw files with Access 2010. Just create the proper shortcut to the new database and have users open the database with the shortcut. If set up properly, they will not be able to open the database directly without the mdw file. NOTE: I have many clients who were on older versions of Access who have upgraded to 2010 and we still use the mdw files. Works fine. There is also VBA code out there to allow you to control users/groups in mdw that you can still use from within 2010.

 

[Jalex]

hmmmm...can you just lock down the Client.accdb? make it an accde file and disable the navigation pane?

No I can't in fact I don't think the Client wants me to touch their Client.accdb, neither I want to have to update other clients accdb files when they want to access the data (which is most probably going to happen in the future).

The other thing you could do is go backwards a little bit. If you have Access 2003 version someplace - create a new database there with groups and user permissions - then import all the objects from Client.accdb into the new database. You can then set up your users and groups - set permissions on the objects - then, convert the DB back to your 2010 version. You can still use mdw files with Access 2010. Just create the proper shortcut to the new database and have users open the database with the shortcut. If set up properly, they will not be able to open the database directly without the mdw file. NOTE: I have many clients who were on older versions of Access who have upgraded to 2010 and we still use the mdw files. Works fine. There is also VBA code out there to allow you to control users/groups in mdw that you can still use from within 2010.

Thanks for the idea. I thought there could be a much simpler solution.

I appreciate all your support

 

[AccessMSSQL]

Jalex, here is another idea - you might be able to just connect to the source.accdb through code and load the data into a temporary table in the client.accdb and just let your users access the temporary table. You could avoid linking the table that way. But not sure how many records you are talking about - it may affect performance and your client.accdb wouldn't always have current data.

 

[mahenkj2]

Originally Posted by AccessMSSQL
hmmmm...can you just lock down the Client.accdb? make it an accde file and disable the navigation pane?
No I can't in fact I don't think the Client wants me to touch their Client.accdb, neither I want to have to update other clients accdb files when they want to access the data (which is most probably going to happen in the future).

making accde from a accdb should not affect the original client accdb, in the copy of accdb, you may link source table and convert to accde and distribute some copies of such modified dBs. Does this sound do-able.

 

[Jalex]

making accde from a accdb should not affect the original client accdb, in the copy of accdb, you may link source table and convert to accde and distribute some copies of such modified dBs. Does this sound do-able.

I didn't quite get it. Here is what I did:

1. Create a Sharing.accdb with a linked table from Source.accdb
2. Publish it as Sharing.accde
3. Tried to link Sharing.accde from Client.accdb

First of all the table linked from Sharing.accde is not read only and Client.accdb can't find the linked table in Sharing.accde.

Jalex, here is another idea - you might be able to just connect to the source.accdb through code and load the data into a temporary table in the client.accdb and just let your users access the temporary table. You could avoid linking the table that way. But not sure how many records you are talking about - it may affect performance and your client.accdb wouldn't always have current data.

Yeah that's not desirable at all

Thanks =/

 

[mahenkj2]

I am not sure how complicated your task is but just some lines from my side -

1. Make a copy of client .accdb (I assume front end)
2.Import a table from source.accdb (Backend) and link in client.accdb
3. In the access options, hide navigation pane.
4. Create a form with imported table and in form properties, in data tab, select data edits, additions, deletions as per the requirement. this form, the user would use to look at the read only data.
5. You may need to set this form as startup form in access options or have to give of accessing this form from clients already existing custom startup form.
6. convert this copy of client.accdb in to .accde.
7. Now,change file extension to .accdr of this copy
8. Distribute

regards.

 

[gemma-the-husky]

Jalex, here is another idea - you might be able to just connect to the source.accdb through code and load the data into a temporary table in the client.accdb and just let your users access the temporary table. You could avoid linking the table that way. But not sure how many records you are talking about - it may affect performance and your client.accdb wouldn't always have current data.

this.

one advantage of the above suggestion is that not every user needs to be actually able to see the actual data - eg, where it is part of an accounting package that not every dbs user might use.

everything is a trade-off. the choice between having "safe" data, and "risky" live updateable data is one you are going to have to consider.

 

[Pat Hartman]

Try using Active Directory to control the sharing. Some users will have update access and others will not.

The problem will be with the lock file. If the first user doesn't have permission to create the lock file, subsequent users get forced to read-only mode and I'm not sure you can give users Create permission but not Update permission. You could remove everyone's delete permission and that would leave the lock file permanently in place but it may need compacting if you do that.

 

[Jalex]

I am not sure how complicated your task is but just some lines from my side -

1. Make a copy of client .accdb (I assume front end)
2.Import a table from source.accdb (Backend) and link in client.accdb
3. In the access options, hide navigation pane.
4. Create a form with imported table and in form properties, in data tab, select data edits, additions, deletions as per the requirement. this form, the user would use to look at the read only data.
5. You may need to set this form as startup form in access options or have to give of accessing this form from clients already existing custom startup form.
6. convert this copy of client.accdb in to .accde.
7. Now,change file extension to .accdr of this copy
8. Distribute

regards.

Client.accdb I cannot touch. That's the whole point, they want something (sharing.accdb, sharing.xls) from which Client.accdb can "link" and work on their own. They don't want anyone touching client.accdb nor have to depend upon developers to update the link.

If I create a form in Sharing.accdb we are back to the beginning, "forms cannot be linked".

Try using Active Directory to control the sharing. Some users will have update access and others will not.

Can't I don't have access to AD.

everything is a trade-off. the choice between having "safe" data, and "risky" live updateable data is one you are going to have to consider.

Yup that's something I have considered, will be hard to explain to our clients though.


************************************

Thanks to all of you guys, sorry for the delay in the response. I have managed to make the Client open the Sharing.xlsx and then saving it so he can work with updated data from Client.accdb (linked to that Sharing.xlsx).

Not the finest solution, but a solution by itself.