asked on

Making Access project application read-only.

Hi Experts,

We have an ADP project that was moved to another platform, and we want to make this now as read-only.
I created a SQL user that has only read permission, and changed the connection property of the project to that user/pwd.
Now I realized that a lot of code were programmed to perform auto updates...
for example the following upon users login...

    sSql = "Insert into InitialsLogins (Initial,PlacementVersion) values ('" & txtINITIALS & "','" & Replace(LabelApplicationVersion.Caption, "V ", "") & "')"
    CurrentProject.Connection.Execute sSql

Open in new window

Wondering if there is an easy way to handle all these, or will have to comment out all this code?

Also in a case where I do need the update to occur, how can I change the code to use another user, not the CurrentProject.Connection?

Thanks

ASKER CERTIFIED SOLUTION

Anders Ebro (Microsoft MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

When you connect to the tables, are you connecting with that user? Sounds like you're not doing that. If you created an SQL login that has only read permissions, and you're using that login to connect, then you should have only read permissions. Be sure the login is not a member of group that has less restrictive permissions (on that database, not the server).

If you do need to update, you can create a new ADODB Connection with the correct user:

Dim con As New ADODB.Connection
con.ConnectionString = "Your Connection STring"
con.execute "YourSQL"

You can get connection string examples here: https://www.connectionstrings.com/

Luke Chung

You can't have it both ways. If the login to the SQL Sever database is read-only, the front end program (ADP or anything else) won't be able to change the data.

In that case, you'll need to remove the data modification features from the ADP.

Alternatively, if you can limit the tables where editing is allowed (e.g. system tables to track user logins), you can create a database login that allows exiting those tables but its read-only for other tables.

Hope that helps.

bfuchs

ASKER

Thank you my experts!