Access 2016 database files being used by an application

Question

I have a vb.net 2010 desktop application that uses an Access 2016 database. Some of the users update the Access 2016 database directly instead of updating the tables using the application. By updating the tables this way, some data is entered incorrectly.

Thus is there a way to make the access database files read-only. If so, can you tell me how to make the access 2016 database tables read only. 

If this is not possible, would you let me know what other options there are can be? I think that the access files can be in folders within the company where the users where users only have read access.

Thus would you tell me what some possible solutions would be?

Answer 1

Hi,

I don't think it's possible to make the Access data file "read only" because your program has to be able to write data to it (unless you give your application a different permission than the user running it).

The only other approach I could recommend is to password-protect the Access data file, so users cannot open it directly to modify the tables.

Just my 2 cents...

Answer 2

If I did what you recommended above about the 'password-protect the Access data file', I have the following questions:

1. how do you setup the password protected data access file?

2. Would the users be able to look at all the data tables?

3. Would this cause the application to have a problem with the 'password-protect the Access data file'? Would the application be able to update the access files with no extra code? If not, what would have to change in the application so that it could update the access file?

Answer 3

Hi Wendy,

1. You open the data file in exclusive mode and then go to File > Encrypt with Password.

2. Once a password is assigned, users will have to enter it to get to the tables. So, if you don't give them the password, then they can't directly look into the data tables.

3. Since you said you're using a vb.net application to create the data, I have no way of knowing what is required for it to function with a password-protected Access database file. If it was an Access front-end application, all you have to do is relink all the tables using the new password. No other code changes would be necessary.

Just my 2 cents...

Answer 4

.theDBguy is essentially correct. I would recommend encrypting and hiding the database password so that no one but an Admin knows what it is. Then you can create your own user level security, using an Access table, to determine who can make changes or view data. This will be easier to implement if the Access database is only acting as the data store and does not have the UI.

BTW, the below link contains an example of an OLEDB connection string for an Access database that has a database password:

https://www.connectionstrings.com/access-2007/

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

I am pretty sure that if you use the ACCESS Encrypt Password procedure to secure the Back-end file, then each time a user opens the database, they will be prompted for the encrypted password even if only the Back-end file is password protected. At least that has been my experience.

So you would need to re-connect the front-end file whenever it is opened. You can put the following VBA code in the On Open Event for a form that opens when the Front-end file is opened:

Dim db As DAO.Database
Set db = CurrentDb
Dim tdf As DAO.TableDef
Dim strBackEnd As String
For Each tdf In db.TableDefs
    If Len(Left$(tdf.Connect, 1)) > 0 Then
        Set tdf = db.TableDefs(tdf.Name)
        strBackEnd = ";Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:Database;Password=MyDbPassword;& """
            tdf.Connect = strBackEnd
            tdf.RefreshLink
    End If
Next tdf

Obviously replace the Data Source parameter with your own path and the Password parameter with the encrypted password (no quote marks). This would prevent opening the Back-end file without the password but does expose the password in your code. So additional measures need to be taken to secure your code from prying eyes.