Solved F11 - Disable or not.

[theDBguy]

So going back to Reply #5 ...

Is there an easy way to make the .accde file with special keys disabled, but leave them enabled in the .accdb?

(Short of setting the value to disabled, re-opening the database, clicking the .accde button, making the .accde and setting the value back to enabled?)

I think the code in Post #5 could work if you declare dummy as an object instead of a string and then modify the MakeACCDE() function to return the new file as an object.

 

[Josef P.]

Somehow I don't understand the problem: You want F11 to be disabled in an accde and not in accdb. Why don't you turn it off in the accde? You can also implement this using code.

steps to build accde as app for users:
1. create accde
2. disable F11, lock shift mode, activate ribbon, ... (see https://www.isladogs.co.uk/improve-security/)

You can implement this e.g. as VB-Script. =>Drag'n'drop accdb file over vb-Script file.

[OT]

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.

Functions always return something. If nothing is set in the function, you get vbEmpty.

Private Sub Test()
    Dim x As Variant
    x = TestReturnValue
    Debug.Print IsEmpty(x), VarType(x), vbEmpty
End Sub

Private Function TestReturnValue() ' implicit return type: Variant
'
End Function

 

[Marshall Brooks]

Somehow I don't understand the problem: You want F11 to be disabled in an accde and not in accdb. Why don't you turn it off in the accde?

I don't want to have to REMEMBER to turn it off in the .accde. Ideally, I want code to turn it off when I make the .accde. Otherwise, I'm better off leaving it turned off in the .accdb and remembering to turn it back on when I do debugging.

You can also implement this using code.

??? How?

You can implement this e.g. as VB-Script. =>Drag'n'drop accdb file over vb-Script file.

What would be vb-script file contain?

 

[The_Doc_Man]

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

Actually, yes it will. A sub converted to a function returns the system default value for whatever data type it is declared as. You are confusing "won't return" (a program action) with "don't care if it returns" (a programmer issue.)

According to the MS VBA Language Specifications v20140424, section 5.3.1 Procedure Declarations, under "Runtime Semantics",

Each invocation of a function declaration has a distinct function result variable.

A further explanation of Procedure Invocation (still within section 5.3.1) provides an activities list that says steps #1-4 deal with arguments such as ByVal issues, handle the initial error handler, create a function-result variable, and create any procedure-extent variables needed for the called procedure (i.e. function-local variables). Step #5 is the actual execution of the called procedure, and step #6 says

If the procedure is a function, return the data value of the result variable to the invocation site as the function result.

Step #7 of that action list merely says, "Execution is complete and execution continues at the call site"

Section 5.4.2.1, Call statement, defines a call statement and then says it

invokes a subroutine or function, discarding any return value.

The way the call statement is defined, the actual word "CALL" is an optional part. I.e. it is enough to name the entry point of the sub or function as the first executable (i.e. not a statement label) element on a non-continued line. If it is not the first executable element on the line, what it actually is can get a bit more complex, but that isn't the point being made here.

 

[Josef P.]

What would be vb-script file contain?

Code that does exactly what you want to set everything.
Is it possible that you haven't looked at Colin's site (isladogs.co.uk - 3 pages from Improve Security in Access Databases) in full?

Example:

Dim args, FileName

Set args = WScript.Arguments
If args.Count > 0 Then
    FileName = args(0)
    msgbox "create accde from " & FileName
    If CreateLockedAccde(FileName) then
       msgbox "accde built"
    end if
Else
    WScript.Echo "File name required"
    WScript.Quit 1
End If

Function CreateLockedAccde(SourceFilePath)

    dim AccessApp, DestFilePath
   
    Set AccessApp = CreateObject("Access.Application")
    AccdeFilePath = Replace(SourceFilePath, ".accdb", ".accde")

    if CreateAccde(AccessApp, SourceFilePath, AccdeFilePath) then
        CreateLockedAccde = LockApplication(AccessApp, AccdeFilePath)
    End if
   
End Function

Function CreateAccde(AccessApp, SourceFilePath, DestFilePath)

    DeleteFile DestFilePath
    AccessApp.SysCmd 603, (SourceFilePath), (DestFilePath)
    CreateAccde = True

End Function

Sub DeleteFile(File2Delete)
   set fso = CreateObject("Scripting.FileSystemObject")
   if fso.FileExists(File2Delete) then
      fso.DeleteFile File2Delete
   end if
End Sub

Function LockApplication(AccessApp, FileName)

    dim dbe, db
    Const dbBoolean = 1

    Set dbe = AccessApp.DBEngine
    Set db = dbe.OpenDatabase(FileName)
 
    SetDbProperty db, "AllowBypassKey", dbBoolean, False
    SetDbProperty db, "AllowSpecialKeys", dbBoolean, False
    '...
   
    db.Close

    LockApplication = True
   
End Function

Sub SetDbProperty(db, PropName, PropType, PropValue)

On Error resume Next

    db.Properties(PropName) = PropValue
 
    if Err.Number = 3270 Then
        db.Properties.Append db.CreateProperty(PropName, PropType, PropValue)
    elseif Err.Number <> 0 then
        Err.Raise Err.Number, "SetDbProperty", Err.Description
    end if
 
End Sub

 

[isladogs]

I did indeed overlook the fact that a function without a defined output type and without an explicit return value will return an empty variant datatype. However, as far as the end user is concerned, nothing appears to be returned in such cases.

I've just discovered an article by Mike Wolfe written some time ago that explains the point well.

Every Function in VBA Returns a Value

Whether you assign one explicitly or not, every function in VBA has a return value.

nolongerset.com

 

[Marshall Brooks]

I have this ALMOST working perfectly. I used a combination of @Gasman 's code (simplified) and @isladogs code.

Code I am using (Sub Test would go behind a button click):

Sub Test()
Dim CurrentDBPath, DummyPath As String, OutPath As String
Dim objFSO As Object
On Error GoTo ErrHandler
CurrentDb.Properties("AllowSpecialKeys").value = False
'save and compile all modules - https://isladogs.co.uk/compile-modules/index.html
      Application.SysCmd 504, 16483
CurrentDBPath = CurrentProject.Path & "\" & CurrentProject.Name
DummyPath = CurrentProject.Path & "\" & "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")
Pause (10)
objFSO.CopyFile CurrentDBPath, DummyPath
OutPath = Replace(CurrentDBPath, "accdb", "accde")
Pause (10)
Call MakeACCDE(DummyPath, OutPath)
objFSO.DeleteFile DummyPath
MsgBox "ACCDE created as " & OutPath & vbCrLf & "Check Date and Time"
CurrentDb.Properties("AllowSpecialKeys").value = True
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
Public Function Pause(NumberOfSeconds As Variant)
' https://www.access-programmers.co.uk/forums/showthread.php?t=167470
' Supports resolution at least to the the tenth of a second, maybe greater.
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = timer
    Do While timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    Box Err.Number & " - " & Err.DESCRIPTION, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

Issues and Questions:

• As far as I can tell, the .accdb is not affected. Technically special keys are disabled, which requires a re-start, and then they are re-enabled, which requires a restart. I verified that F11 still works on the .accdb and does NOT work on the created .accde.
  
  
• When I tried to open the .accde and still had the .accdb open, I got an error something like "The database has been placed in a state by user "Admin" on <mycomputername> which prevents it from being open or locked." It opened fine once I closed the .accdb. Not a huge deal, but I don't recall this happening when I created an .accde manually.
  
  
• The code worked fine when I stepped through it line-by-line. When I ran it automatically, it gave me the confirmation message, but no file was created. 20 seconds combined of pauses seems a bit long, but it also didn't work with 2 seconds of combined pauses. I suspect either the dummy file wasn't copied yet when Access tried to create the .accde from it, or the command to create the .accde was running before the new Access app opened. (Note: This is in the office where I have a fast network connection. I expect slow network connections when connecting from home - 20 seconds might not be sufficient for that. (However - 20 seconds is probably faster than I could do it from the file menu and typing the new file name.)
  
  
• My "Normal" Manual Process when creating an .accde for release is as follows:
  • Open the .accdb from a .bat file with the /decompile switch.
  • Hold down the shift key and do a Compact and Repair (C&R).
  • Compile from the VB Editor.
  • Hold down the shift key and do two more (C&R's).
  • Save as .accde.

Can any of the "Normal" process be automated, and/or is this overkill? Really, I could still do all of it and just remember to click the button instead of making the .accde manually.

Thanks again and in advance!

 

[isladogs]

Perhaps you would be better off using one or other approach rather than a combination of both?
You certainly shouldn't need to add a series of pauses
As long as the database is compiled before creating an ACCDE, there shouldn't be any issues.
My approach, as described in my web articles, just requires the relevant procedure to be run.
Much easier than what you are describing

FWIW the latest version of A365 includes a new SysCmd action called acSysCmdCompile with the enum value 603
After 20+ years of being unsupported, it is now officially available

 

[Marshall Brooks]

@isladogs - I'm open to suggestions ...

I tried your approach first and it didn't work - even when I stepped through the code, it didn't give me any error messages, but it didn't work.
Having said that - reading your approach carefully, it said the current database had to be closed, which is what @Gasman 's comments say in his code.

Your approach would probably work if I ran it from a clean database and gave it the path to my development database.

I didn't want to do that - I wanted to run it from my development database and save it in the same folder as the current database with the same name.

@Gasman 's code does that.

All I really changed in @Gasman 's code was I removed a lot of variables since I just wanted to save in the same folder and with the same file name as my current database, and I added a line to compile the current database b/c I might forget that and not know why it didn't work.

(And I might modify it to check if the .accde exists and report either way, rather than just saying it was created if the code gets to that step).

 

[Marshall Brooks]

Not tested yet, but I recommend replacing:

    MsgBox "ACCDE created as " & OutPath & vbCrLf & "Check Date and Time"

with

If Dir(OutPath) = "" Then
    MsgBox "Something went wrong! ACCDE file was not created."
Else
    MsgBox "ACCDE created as " & OutPath & vbCrLf & "Check Date and Time"
End If

 

[isladogs]

@isladogs - I'm open to suggestions ...

I tried your approach first and it didn't work - even when I stepped through the code, it didn't give me any error messages, but it didn't work.
Having said that - reading your approach carefully, it said the current database had to be closed, which is what @Gasman 's comments say in his code.

Your approach would probably work if I ran it from a clean database and gave it the path to my development database.

I didn't want to do that - I wanted to run it from my development database and save it in the same folder as the current database with the same name.

I think you are referring to this article: Create ACCDB/ACCDE Files In Code (isladogs.co.uk) which is referring to creating an ACCDE file of an external database

In that particular case, I stated:

For this code to work successfully, the external database to be converted MUST:
a) be created in the same version of Access
b) be in a trusted folder
c) compile without error
d) be closed

The code does work if used as described.

However, you cannot create an ACCDE of the currently open database.
You have to make a copy of the database first & make an ACCDE from that copy - as you are doing in the code shown above

 

[Marshall Brooks]

@isladogs - We are in agreement - just saying the same things in different ways. Your code works from an external database. @Gasman 's code works from the current one, which is what I was after.

Odd results: I just tried this working from home (as opposed to the office), and it worked flawlessly with the pause statements commented out. I can only think of three things that I did differently that could account for this. These were NOT it:

• Network connections and file transfers are SLOWER from home, but I didn't think about yesterday that my development folder is on my local hard drive, so Network Connections shouldn't affect this.
• I restarted the computer - so it was fresh this morning - and I'd been doing a lot during the day yesterday. (That wasn't it, more below ...)

This was the solution and I got interesting results:

• This morning I made a copy of my FE, and did my usual pre-release of decompile, C&R with shift key, Compile, C&R with shift key, C&R with shift key again, close and reopen. That reduced the file size from 73 MB to 26 MB - but I don't know that the file size is significant enough to account for 20 seconds of pauses to be required.
• However, as a second test I opened last night's 73 MB FE file from home and got the same results as yesterday. It failed with no pauses, worked fine with 20 seconds of pauses, and failed with 4 seconds of pauses.
• Oddly, I made a copy of yesterday's file and opened it and did ONE C&R - no decompile and no shift key bypass. That only reduced the file size to 53 MB, but the .accde creation worked flawlessly with the pauses commented out.

I looked into adding C&R to the .accde creation routine - seems to be mixed views on that: https://www.devhut.net/access-vba-compact-the-current-database/ - looks like it used to be simple, was disabled, and now is somewhat hit-or-miss with the new method.

For my purposes, unless anyone else has better suggestions: Since I typically only make an .accde when I want to release the database and since I want the minimal file size since we save the .accdb as backup ... I think I'm going to add a simple MsgBox "Did you do a decompile and C&R?" reminder and take the pauses out and put this to bed.