Password the DB how to:

[mloucel]

Hello All:

I decided to add a password to my database to avoid people getting their hands in my tables, I ask Google AI and Grok how to create code to open the database with that password and gave me this:

GROK:

Sub OpenProtectedDatabase()
    Dim appAccess As New Access.Application
    
    ' Open the database with the password
    appAccess.OpenCurrentDatabase _
        Filepath:="C:\YourPath\Auth.accdb", _
        Exclusive:=False, _
        bstrPassword:="Athoriz"
    
    ' Make it visible to the user
    appAccess.Visible = True
    
    ' Optional: Clean up reference later when done
    ' Set appAccess = Nothing
End Sub

and Google:

Sub OpenAuthDatabase()
    Dim accApp As Object
    Dim dbPath As String
    
    ' Set the full path to your "Auth" database
    dbPath = "C:\YourPath\Auth.accdb"
    
    ' Create a new instance of Access
    Set accApp = CreateObject("Access.Application")
    accApp.Visible = True
    
    ' Syntax: .OpenCurrentDatabase (filepath, exclusive, password)
    accApp.OpenCurrentDatabase dbPath, False, "Athoriz"
End Sub

Which one of those codes is better or is there a better way?

 

[DHookom]

If they can’t get “their hands in my tables”, they probably won’t be able to use your application. Also, I’m confused why you would want to use code to open your password protected application. Typically, you add a password and the user gets prompted when attempting to open your file.

@isladogs has a great presentation on locking down an Access application. I expect he will chime in.

 

[mloucel]

If they can’t get “their hands in my tables”, they probably won’t be able to use your application. Also, I’m confused why you would want to use code to open your password protected application. Typically, you add a password and the user gets prompted when attempting to open your file.

@isladogs has a great presentation on locking down an Access application. I expect he will chime in.

Thanks, for the tip, but I believe his approach is way more that what I intend to do, mine is very simple password the BE and opening the BE using VBA.

 

[theDBguy]

Which one of those codes is better or is there a better way?

Did both of them work?

 

[isladogs]

I agree with @DHookom.

I am not sure of your reasons for using code to open the BE.

When applying security to an Access DB, you need to decide who or what you are trying to protect your database from. I provide a number of options to choose from. Most of the steps available are simple to implement. Very few developers would need to do all of the steps described in my various articles.

Encrypting your BE with a password is a standard approach to improve security.
Normally you would then just link the FE to the encrypted BE
If you want to prevent users accessing the BE tables, then you can do a number of things including
a) hiding or deep hiding the BE tables
b) hiding the nav pane (or the application interface)
c) restricting user actions in Access options

Or you can go much further than that by using disconnected ADO recordsets to the BE instead of linked tables …. but for most purposes that is far more work than is necessary.

So by all means password protect your BE. Doing that is definitely a good idea.
But why do you then want to open the BE with code?

 

[xavier.batlle]

Thanks, for the tip, but I believe his approach is way more that what I intend to do, mine is very simple password the BE and opening the BE using VBA.

If I'm not mistaken, the code you have found opens another MS Access instance. If you want to open a BE password protected from the Front End you'll need something like this:

    Dim MyBE_DB as DAO.Database
    Set MyBE_DB = DBEngine(0).OpenDatabase(BACKEND_PATH_AND_FILE, False, False, "MS Access;PWD=" & DATABASE_PASSWORD)

 

[mloucel]

I agree with @DHookom.

I am not sure of your reasons for using code to open the BE.

When applying security to an Access DB, you need to decide who or what you are trying to protect your database from. I provide a number of options to choose from. Most of the steps available are simple to implement. Very few developers would need to do all of the steps described in my various articles.

Encrypting your BE with a password is a standard approach to improve security.
Normally you would then just link the FE to the encrypted BE
If you want to prevent users accessing the BE tables, then you can do a number of things including
a) hiding or deep hiding the BE tables
b) hiding the nav pane (or the application interface)
c) restricting user actions in Access options

Or you can go much further than that by using disconnected ADO recordsets to the BE instead of linked tables …. but for most purposes that is far more work than is necessary.

So by all means password protect your BE. Doing that is definitely a good idea.
But why do you then want to open the BE with code?

Hi Colin,
During the weekend 1 of the workers somehow got to the BE folder in the server, and was able to double click the BE and open the files, She panic and called me, so it was fine, that got me thinking how can I put a password on my BE and be able to open the tables, but only while the FE is running, so if any user opens the BE file, they will receive a "ENTER PASSWORD" message, as simple as that, I started looking on line and using Grok and Gemini and after I ask the question got those codes, since I've never done anything like this, I am worried I screwed up and lock the tables, of course I am trying in my own copy using the data yo saw previously.
So literally I have never done it and I have no idea what to do, I was hoping for something simple, I'm going to go back to your website and look at your articles first, I'm sure I will find my answer there.

 

[theDBguy]

Hi Colin,
During the weekend 1 of the workers somehow got to the BE folder in the server, and was able to double click the BE and open the files, She panic and called me, so it was fine, that got me thinking how can I put a password on my BE and be able to open the tables, but only while the FE is running, so if any user opens the BE file, they will receive a "ENTER PASSWORD" message, as simple as that, I started looking on line and using Grok and Gemini and after I ask the question got those codes, since I've never done anything like this, I am worried I screwed up and lock the tables, of course I am trying in my own copy using the data yo saw previously.
So literally I have never done it and I have no idea what to do, I was hoping for something simple, I'm going to go back to your website and look at your articles first, I'm sure I will find my answer there.

If you did put a password in the BE and someone double-clicks on it, they would get an "enter password" prompt by design, without you having to use any VBA at all - if that's what you were after.

 

[mloucel]

If you did put a password in the BE and someone double-clicks on it, they would get an "enter password" prompt by design, without you having to use any VBA at all - if that's what you were after.

Thanks I am worried about the other half of the DB, the FE, that's where I was hoping the code will help me open the tables so my FE can work with them, I am sorry if it sounds incredible, but I have never done anything like it, I have no idea how it works to put a password on the BE and then be able to work with my FE without the EU having to enter the password when they need to open the tables.
With code that I used from the website that Colin has I am able to completly hide the ribbon and the app window, so that part is done and it works perfectly, the only problem is now with the BE, and I am completly lost there I have no idea what to do, I've looked and google east to west and either I am not looking at the right place or not using the correct words.
Colin has posted in his website about security but if I am not mistaken I already did all that by closing the gap for the EU to be able to even see if there are any tables or queries or much less use the ribbon, I control everything, except I didn't expect someone getting into the BE and by mistake opening, hence now the need of locking the BE.

Thanks

Maurice.

 

[theDBguy]

Thanks I am worried about the other half of the DB, the FE, that's where I was hoping the code will help me open the tables so my FE can work with them, I am sorry if it sounds incredible, but I have never done anything like it, I have no idea how it works to put a password on the BE and then be able to work with my FE without the EU having to enter the password when they need to open the tables.

Here are the typical steps when developing a secured BE with an FE.

1. Create the BE file
2. Add the tables in the BE
3. Apply a password to the BE file
4. Create the FE file
5. Link the BE tables to the FE
6. Access will prompt you (the developer) the password to the BE
7. If you enter the correct password, then Access will create a linked table. If you entered the wrong password, then Access will complain
8. After linking the tables, you would create your forms (and other stuff)
9. When you're done, you can give the FE and BE to the users
10. When the user opens that FE, they will not be asked for the BE's password (unless they open the BE file instead of the FE)

But, in your case, you probably took the following steps:

1. Created the BE file
2. Added the tables to the BE
3. Created the FE file
4. Linked the BE tables to your FE (no password prompt this time because the BE wasn't password protected)
5. Created your forms, etc
6. Now, you realized you wanted to add a password to the BE - what's the next step?

Here are the next steps:

1. Put a password on the BE file
2. Open the FE and delete all the linked tables
3. Create a new linked tables to the BE after you applied the password - you should be prompted for it
4. Once you're done with this, all your forms and code should work like before, and the users won't be asked for the BE password (they don't need to know it)

Hope that helps...

PS. I obviously left some things out of the above steps that we can talk about later.

 

[mloucel]

Here are the typical steps when developing a secured BE with an FE.

1. Create the BE file
2. Add the tables in the BE
3. Apply a password to the BE file
4. Create the FE file
5. Link the BE tables to the FE
6. Access will prompt you (the developer) the password to the BE
7. If you enter the correct password, then Access will create a linked table. If you entered the wrong password, then Access will complain
8. After linking the tables, you would create your forms (and other stuff)
9. When you're done, you can give the FE and BE to the users
10. When the user opens that FE, they will not be asked for the BE's password (unless they open the BE file instead of the FE)

But, in your case, you probably took the following steps:

1. Created the BE file
2. Added the tables to the BE
3. Created the FE file
4. Linked the BE tables to your FE (no password prompt this time because the BE wasn't password protected)
5. Created your forms, etc
6. Now, you realized you wanted to add a password to the BE - what's the next step?

Here are the next steps:

1. Put a password on the BE file
2. Open the FE and delete all the linked tables
3. Create a new linked tables to the BE after you applied the password - you should be prompted for it
4. Once you're done with this, all your forms and code should work like before, and the users won't be asked for the BE password (they don't need to know it)

Hope that helps...

PS. I obviously left some things out of the above steps that we can talk about later.

I'm going to do that with my test DB in my office friday after everyone is gone. [of course make backups of everything 3 times to avoid errors]

Question:
If I do all those changes, and password the BE and all the steps, [using my test DB], I should be able to simply go to the real one in my server [previous backup of course] put the same password, then copy my FE and distribute to the EU, and it should work, since I have linked the tables already in the compiled FE.
(I am working with an exact replica of the FE tables with bogus data)

Is my theory correct ? will the FE now move the password link tables to the production one?
I guess I will find out tomorrow in my office, but I have the feeling the answer is yes.

And that is as simple as I wanted to protect my BE, so I was dreaming with VBA, it was not needed after all.

Thank you so much for the steps, you should be a teacher.

Maurice.

 

[theDBguy]

Is my theory correct ? will the FE now move the password link tables to the production one?

Well, that depends on two things:

1. Do both BEs have the same name?
2. And will both BEs have the same filepath as when you created the new linked tables to the one with a password?

 

[mloucel]

Well, that depends on two things:

1. Do both BEs have the same name?
2. And will both BEs have the same filepath as when you created the new linked tables to the one with a password?

Yes to both I am very careful

 

[theDBguy]

Yes to both I am very careful

That's a little hard to believe because two files with the same name cannot be in the same folder together; but if you managed to do that, then you're good to go.

 

[isladogs]

I agree with @theDBguy that you approached this in the wrong order
I can now see why you asked the original question though its from a complete misunderstanding of how linking to an encrypted DB works

With regard to the suggested next steps from post #10, there is no need to delete and re-create the linked tables after adding a password to your BE.
This is my amended approach - I'm assuming you have the new linked table manager (A2021 or later)

1. Put a password on the BE file
2. Open the FE and right click on any linked table then open the linked table manager
3. Select the BE in the list and click Edit. Enter the password and click Save then click Refresh.

4. As already stated, all your forms and code should then work like before, and the users won't ever be asked for the BE password.