Solved F11 - Disable or not.

[Marshall Brooks]

I think my database is fairly secure now, and I have an idea how I want to proceed but I thought I should "Ask the Experts" also.

The issue:

• If a user presses F11, they can open the Visual Basic Editor. If they have access to the full .accdb version of my database (they don't), they could bypass most of my security fixes.
• This can be disabled under File Options - Enable Special Keys and re-opening the database:
• 
  but I just noticed this also enables the Navigation Pane, and more importantly, it disables breakpoints in the VB Editor. Somewhat of a pity MS links all these functions together ...
• There is a VBA option to check/uncheck the Special Keys file option, but you still have to close and re-open the database.

I see three possible fixes:

• Leave the option off - which I am currently doing. But this creates a double (human) memory issue:
  • First, typically I will need to debug something, and my breakpoints won't work. I'll do a Google search which leads me here or here:
    • https://www.access-programmers.co.uk/forums/threads/urgent-vba-not-breaking-on-breakpoints.142831/
    • https://www.access-programmers.co.uk/forums/threads/step-into-to-debug-does-not-work.130062/
      and then my memory kicks in.
  • And then once I enable them so my breakpoint work, I have to remember to disable them before I distribute the database or I might as well leave them enabled.
• I was considering (and still somewhat am considering enabling the option (but I didn't originally realize the navigation pane was affected also):
  • I distribute an .accde. I don't think most of our users would know that F11 opens the visual basic editor. If they do, they would know all my module names (no real help there), and then if they tried to see anything, they would see "Project is not viewable." No harm/no foul.
  • Nav pane is a BIT more concerning, but they would have to be really determined - plus the new switchboard is more convenient for them to use. Risk here is if they REALLY know what they were doing, they could go to Navigation Options, turn on show hidden items, find the hidden data tables and delete some records - but again, they have to know a fair amount to figure that out.
• It would be somewhat possible to automate this -
  

  Toggle Navigation Tabs On/Off in VBA

  I have a A2007 application that I have developed in A2003 If I design a form in 2007 and have the Form Navigation tabs flag unchecked I cannot find a way to close a saved form. However if I turn them on I can. The issue is I don't want to have to do this prior to opening the app. Is there a way...

  www.access-programmers.co.uk
  

  AllowSpecialKeys Property [Access 2003 VBA Language Reference]

  learn.microsoft.com
  Simplest method:
  • I have a Dev Mode/User Mode button on my switchboard (only for me). If I enable Dev Mode, It changes the setting and prompts me to re-boot. Then I have to click Dev Mode again and not reboot. When I click User Mode, it disables the setting.
    
    Better Method:
  • This is complicated, but I could create a TempVars https://www.devhut.net/access-tempvars/ for DevMode. If I click the DevMode button, it sets the TempVar to True and enable the property. https://btabdevelopment.com/free-access-tools/ - Front end manager has an option to close the database and download a new version and re-open it. I could modify this to just close and re-open the current database. Then disable the property, and set the tempVar to false when I re-open the database.

What do you recommend and/or do?

 

[isladogs]

In development mode, my apps are unlocked with all special keys enabled, nav pane visible etc.

However, the app includes a startup routine which locks down the app if it is compiled as ACCDE or run on any PC other than my own dev machine. I also disable the shift bypass on other workstations but not on my own. I also use a number of other restrictions, many of which are outlined in my Web article.

Improve Access Security

This article summarises some of the main steps that developers should take to improve security in Access databases

www.isladogs.co.uk

You can do all the changes in Access options using code if you prefer
After saving as ACCDE, I open the file twice to ensure the all my security features are implemented

 

[Marshall Brooks]

That would be an option also, I only need F11 for the .accdb, not for the .accde.

Question: Do you have a button that saves the database as .accde after disabling special keys (and how would I save as an .accde from VBA?) or do you test if the file name ends in .accde and then set the properties (which as you said would need to open the new database twice for the changes to take effect)?

 

[Gasman]

Here is wha I used to use to create an accde after updating the version number.

Private Sub cmdCreate_Click()
Dim strFEPath As String, strBEPath As String, strACCDE As String, strPath As String, strDummy As String
Dim Dummy As String, strDB As String
Dim objFSO As Object
On Error GoTo ErrHandler

strFEPath = CurrentDb.Name
strBEPath = GetBackEndPath()
strPath = Left(strBEPath, InStrRev(strBEPath, "\"))
strDummy = strPath & "Dummy.accdb"
' We need to make a copy of the db for the accde vba code to work. Will not work on current DB
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile strFEPath, strDummy
strDB = objFSO.GetFileName(CurrentDb.Name)
strDB = Replace(strDB, "accdb", "accde")

strACCDE = Left(strBEPath, InStrRev(strBEPath, "\") - 1) & "\" & strDB
Dummy = MakeACCDE(strDummy, strACCDE)
MsgBox "ACCDE created in as " & strACCDE & vbCrLf & "Check Date and Time"
objFSO.DeleteFile strDummy

ExitSub:
Set objFSO = Nothing

Err_Exit:
    Exit Sub
   
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitSub

End Sub

Public Function MakeACCDE(InPath As String, OutPath As String)

Dim app As Access.Application
Set app = New Access.Application

app.AutomationSecurity = 1 'msoAutomationSecurityLow

app.SysCmd 603, InPath, OutPath

Set app = Nothing

End Function

 

[Marshall Brooks]

If I wanted to disable special keys in the .accde version, but not in the .accdb, using @Gasman 's code, would this work:

...
strACCDE = Left(strBEPath, InStrRev(strBEPath, "\") - 1) & "\" & strDB
Dummy = MakeACCDE(strDummy, strACCDE)
' ***
Dummy.Properties("AllowSpecialKeys").value = False
' ***
MsgBox "ACCDE created in as " & strACCDE & vbCrLf & "Check Date and Time"
...

Thanks!

 

[theDBguy]

If I wanted to disable special keys in the .accde version, but not in the .accdb, using @Gasman 's code, would this work:

...
strACCDE = Left(strBEPath, InStrRev(strBEPath, "\") - 1) & "\" & strDB
Dummy = MakeACCDE(strDummy, strACCDE)
' ***
Dummy.Properties("AllowSpecialKeys").value = False
' ***
MsgBox "ACCDE created in as " & strACCDE & vbCrLf & "Check Date and Time"
...

Thanks!

The MakeACCDE() function doesn't seem to return anything, so I doubt that will work.

 

[Pat Hartman]

If a user presses F11, they can open the Visual Basic Editor. If they have access to the full .accdb version of my database (they don't), they could bypass most of my security fixes.

That is why Applications are compiled to .accde format and then the extension is renamed to accdr and that is what is distributed. You don't ever want the user dropping into code. If you distribute the .accdr, the MS Access executable goes into Runtime mode and locks out the design options totally. You can add code to your startup form that if it isn't you or other developers opening the app, then check to make sure that the database name is has the .accdr extension. If the extension is anything else, shut down.

Renaming to .accdr is a "trick" and technically a savvy user might think to rename the FE to a different extension. But the best he can see is whatever the .accde allows. It is certainly enough to do damage but at least they can't get to the code. If your app only opens in user mode if the extension is .accdr, that prevents the rename from doing anything.

If you are selling the app to the public, you have a whole different problem than if you are doing internal development. For internal development, you are much more concerned with protecting against accidents rather than people stealing your intellectual property since it isn't your intellectual property anyway. It belongs to your employer.

 

[Marshall Brooks]

The MakeACCDE() function doesn't seem to return anything, so I doubt that will work.

Public Function MakeACCDE(InPath As String, OutPath As String)

Dim app As New Access.Application

app.AutomationSecurity = 1 'msoAutomationSecurityLow

app.SysCmd 603, InPath, OutPath

Set app = Nothing

End Function

From - https://www.access-programmers.co.uk/forums/threads/make-accde-in-vba.290367/

 

[theDBguy]

Public Function MakeACCDE(InPath As String, OutPath As String)

Dim app As New Access.Application

app.AutomationSecurity = 1 'msoAutomationSecurityLow

app.SysCmd 603, InPath, OutPath

Set app = Nothing

End Function

From - https://www.access-programmers.co.uk/forums/threads/make-accde-in-vba.290367/

Were you trying to make a point, or were you asking another question? Not sure I understand your last post.

 

[Marshall Brooks]

Maybe a little of both ... @Gasman left out two lines of the function (which are pretty critical) in reply #4.

I did a Google Search and found what the code should be.

I haven't tested it so I don't know if it works or not.

 

[theDBguy]

Maybe a little of both ... @Gasman left out two lines of the function (which are pretty critical) in reply #4.

I did a Google Search and found what the code should be.

I haven't tested it so I don't know if it works or not.

Ah, which two lines are those? I don't really see a difference in functionality on both versions. To my point though, both versions don't return anything, using dummy.anymethod will just produce an error.

 

[Gasman]

Maybe a little of both ... @Gasman left out two lines of the function (which are pretty critical) in reply #4.

I did a Google Search and found what the code should be.

I haven't tested it so I don't know if it works or not.

Really, what are they?, as I just copied the function from a db.

 

[Marshall Brooks]

My bad - I didn't scroll down, so I didn't see the
app.SysCmd 603, InPath, OutPath
Set app = Nothing

However, unlike javscript and some other code, I don't think VBA functions have to return a value. In fact they usually DON'T return a value.

i.e. you can rename a sub as a function and it will work just fine (one difference is functions can be called by the generic Access macro code and subs cannot).

In fact you have to define a value for it to be returned - something like:

Function TrimSpaces(InputString as string) as string
TrimSpaces = Trim(InputString)
End Function

 

[Gasman]

My bad - I didn't scroll down, so I didn't see the
app.SysCmd 603, InPath, OutPath
Set app = Nothing

However, unlike javscript and some other code, I don't think VBA functions have to return a value. In fact they usually DON'T return a value.

i.e. you can rename a sub as a function and it will work just fine (one difference is functions can be called by the generic Access macro code and subs cannot).

In fact you have to define a value for it to be returned - something like:

Function TrimSpaces(InputString as string) as string
TrimSpaces = Trim(InputString)
End Function

Wrong. Most functions do return a value, otherwise I use a sub.
That code, I believe I got here.
I just ran it and it created the accde.

You could test for an error and return a True or False if any occur that stop the accde being created. That is why I say check in the message.
These faciliies are not avaiable to normal users, just myself and run from a switchboard form.

Edit: Well I was partially correct. I got the code on the net, and then amended wih code from @CJ_London

Make ACCDE in VBA

I'm trying to create an ACCDE file from within my database. I discovered code on the net Public Function MakeACCDE(InPath As String, OutPath As String) Dim app As New Access.Application app.AutomationSecurity = 1 'msoAutomationSecurityLow app.SysCmd 603, InPath, OutPath Set app = Nothing...

www.access-programmers.co.uk

 

[The_Doc_Man]

However, unlike javscript and some other code, I don't think VBA functions have to return a value. In fact they usually DON'T return a value.

You actually got that sort of inverted. Subs don't return a value. Functions always do - but you can ignore the value because a function CAN be called as though it was a sub, leaving no directions on where to put the returned value. I.e. the function DOES have to return a value but YOU don't have to use it.

 

[isladogs]

You actually got that sort of inverted. Subs don't return a value. Functions always do - but you can ignore the value because a function CAN be called as though it was a sub, leaving no directions on where to put the returned value. I.e. the function DOES have to return a value but YOU don't have to use it.

I don't agree with the function part of that explanation.
Subs never return a value - agreed.
Functions will often be used to return a value but that isn't a requirement.
For example, if a subprocedure is modified to be a function it will still work and won't return any value

 

[Marshall Brooks]

For example, if a subprocedure is modified to be a function it will still work and won't return any value

Basically what I was trying to say in reply #13.

 

[isladogs]

@Marshall Brooks
You might find my articles on creating ACCDEs in code useful

Create ACCDB/ACCDE Files In Code

This explains how to create both ACCDB & ACCDE files in code. Creating an ACCDE file requires the use of an undocumented SysCmd function

isladogs.co.uk

Compile Modules Using Code

This article explains how to save and compile all VBA modules using code. This requires the use of an undocumented SysCmd function

isladogs.co.uk

Why can't I create an ACCDE file?

This article explains the main reasons why creating an ACCDE file may fail. It also explains how to fix those issues

isladogs.co.uk

 

[GPGeorge]

My bad - I didn't scroll down, so I didn't see the
app.SysCmd 603, InPath, OutPath
Set app = Nothing

However, unlike javscript and some other code, I don't think VBA functions have to return a value. In fact they usually DON'T return a value.

i.e. you can rename a sub as a function and it will work just fine (one difference is functions can be called by the generic Access macro code and subs cannot).

In fact you have to define a value for it to be returned - something like:

Function TrimSpaces(InputString as string) as string
TrimSpaces = Trim(InputString)
End Function

It is more accurate to say Functions return values. However, you don't have to do anything with those returned values. It's safe to ignore them.

 

[theDBguy]

Basically what I was trying to say in reply #13.

But you were missing my point. The code you posted that you said you wanted to attempt was written as if the MakeACCDE() function was returning an object, which I was saying that it wasn't, so I was just saying your idea won't work as written. Of course, you would have found that out anyway once you get a chance to test it.