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
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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/
0
Luke ChungPresidentCommented:
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.
0
bfuchsAuthor Commented:
Thank you my experts!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.