Making a linked table read-only

[KornGeek]

I have a series of linked tables that I believe are in an SQL database. I don't want users of my Access database to be able to change the data in the SQL database. Is there a way to set this up to make the tables read-only?

I have not yet implemented security on the Access database (it's planned, but lower on the priority scale).

I tried creating queries based on the SQL tables and making the recordsettype be Snapshot, which makes that query read-only. However, once I create another query based on my Snapshot query, the data can be edited.

For instance, I have a table tblEmployees that is linked to an SQL database, which my customer does not want changed. I then created qryEmployees, which is a read-only Snapshot query. I have another table tblEmployeeData which is an Access table with a one-to-one relationship to tblEmployees. When I create qryEmloyeeFullData, which combines these tables, it allows me to edit any field from either table, when I only want to be able to edit the fields from tblEmployeeData.

I realize that I could set controls on my forms to determine which fields can be edited, but I'm doing this in a large database that has already been built. I'm having to retro-fit it to solve this and several other issues.

I appreciate any and all suggestions.

 

[danvlas]

If you include a group by clause in your queries, then they'll be read-only.

On the other hand, security in SQL Server should be employed in such a way as to deny all access to underlying tables. Data manipulation should be done through views/stored procs/functions.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886