none
Access 2016 database files being used by an application RRS feed

  • 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?

    Thursday, February 15, 2018 9:53 PM

Answers

  • 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...

    Friday, February 16, 2018 3:35 PM
  • .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)

    Friday, February 16, 2018 10:12 PM
  • 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.

    Sunday, February 18, 2018 4:53 PM

All replies

  • 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...

    Thursday, February 15, 2018 10:02 PM
  • 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?

    Friday, February 16, 2018 3:03 PM
  • 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...

    Friday, February 16, 2018 3:35 PM
  • .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)

    Friday, February 16, 2018 10:12 PM
  • 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.

    Sunday, February 18, 2018 4:53 PM