We help IT Professionals succeed at work.

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
Comment
Watch Question

Microsoft Developer
CERTIFIED EXPERT
Commented:
I haven't used Access Projects myself, so maybe this advice doesn't make sense :)

That said, I think the easiest (least amount of investment in time) approach might be to modify the permissions on the server. Adjust the permissions on the tables and views such that the core data tables are all read only, but such "lesser" important tables as recording logins can still be written to. Sure you might get some error messages popping up in the user interface is the user tries to update records, so not ideal, but certainly less investment than having to recode the entire application.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 ChungPresident
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
Thank you my experts!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.