MS Access VBA Error: Run time error '70' Permission Denied

Question

I believe this issue is a result of a recent update to MS Office/Access or Windows 10. When I run this code:

Dim s As String
With CreateObject("Scriptlet.TypeLib")
    s = Left(.Guid, 9)
   newguidx = Right(s, 8)
End With

I get an error: Permission Denied on the With Statement. This is new only only cropped up after my admins updated our systems. I have no option to roll back updates. Anyone come across this and have a work around?

OS: Windows 7 Enterprise Access Version: 2010

Thanks

Gyron

Answer 1

From Microsoft:

This issue is by design, as of the July security updates. This control is blocked as a security measure to help prevent malicious code from running in Office applications. We are working on getting a knowledgebase article out with the recommended method. Until that KB is ready, I'll post the content here:

Workaround The preferred method is to change the code to use Windows API CoCreateGuid (https://msdn.microsoft.com/en-us/library/windows/desktop/ms688568(v=vs.85).aspx) instead of CreateObject("Scriptlet.TypeLib").Guid

' No VT_GUID available so must declare type GUID
Private Type GUID_TYPE
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type
Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (Guid As GUID_TYPE) As LongPtr
Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (Guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr
Function CreateGuidString()
    Dim guid As GUID_TYPE
    Dim strGuid As String
    Dim retValue As LongPtr
    Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

    retValue = CoCreateGuid(guid)
    If retValue = 0 Then
        strGuid = String$(guidLength, vbNullChar)
        retValue = StringFromGUID2(guid, StrPtr(strGuid), guidLength)
        If retValue = guidLength Then
            ' valid GUID as a string
            CreateGuidString = strGuid
        End If
    End if
End Function

Alternate Workaround

This will allow you to use CreateObject("Scriptlet.TypeLib")however it will lessen the security protection added by the Office July 2017 security update. You can set a registry key that will allow Scriptlet.TypeLib to instantiate inside of Office applications. When this registry key is set, Office will not block any use of this COM control.

• Open Registry Editor.
• Go to: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\COM Compatibility{06290BD5-48AA-11D2-8432-006008C3FBFC}
  • Note: you may need to search the registry to find the correct location under HKEY_LOCAL_MACHINE based on your installed Office version.
• Add ActivationFilterOverride
• Set DWORD: 1

Detailed Instructions:

1. Click Start, click Run, type regedit in the Open box, and then click OK. Locate and then click the following key in the registry: HKEY_LOCAL_MACHINE
2. On the Edit menu, point to Find, and then enter the following in the Find What: text box: COM Compatibility
3. Ensure Keys is selected, and Values and Data are deselected. Next select Match whole string only, click Find Next Locate and then click the following key: {06290BD5-48AA-11D2-8432-006008C3FBFC} Note: if the key is not present you will need to add it. On the Edit menu, point to New, and then click Key. Type in {06290BD5-48AA-11D2-8432-006008C3FBFC}
4. On the Edit menu, point to New, and then click DWORD (32-bit) Value
5. Type ActivationFilterOverride for the name of the DWORD, and then press Enter.
6. Right-click ActivationFilterOverride, and then click Modify.
7. In the Value data box, type 1 to enable the registry entry, and then click OK.
8. Note to disable the ActivationFilterOverride setting, type 0 (zero), and then click OK.
9. Exit Registry Editor, and then restart any open Office application.

Menes

I also had to add it in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Common without the version number. – Katiekatina

What's the point of retValue = CoCreateGuid(guid) if retValue is never returned or used other than the condition right below it? – Demonstrate

@Demonstrate seems like a question for Microsoft, but I think the answer is "the point of that variable is that it's used in the condition right below it". I'll agree the naming convention is a bit odd, but apart from that what reason would you expect it to be returned? And where else it would possibly be used, if not after its assignment, which is literally the last block of code in the function? – Menes

I was wrongly thinking the return value of CoCreateGuid was the created GUID. I thought returning 0 instead of a GUID might be a backwards/version compatibility thing. Read it in that context and you'll probably understand what I meant. What I missed is that CoCreateGuid is passed a pointer guid which gets set to the created GUID and that's used inside the conditional block. 0 is S_OK indicating the GUID was successfully created. Thanks for sharing this solution. – Demonstrate

Cheers @Demonstrate based on what I'd consider (somewhat) normal naming conventions, that's the same assumption I made and I had to step through it to see what's actually happening as well :). – Menes

This accepted answer does not work for me on Windows 10 with Office 365. – Orit

There are new prompts added to this question which say is this answer outdated Yes|No ... I can't answer Yes or No because I can only speak for Windows 10 with Office 365. The answer may well still work for the OP on Windows 7 Enterprise Access Version: 2010 – Orit

Answer 2

In Access, we can suffice with this very short function to generate a GUID by leveraging Application.StringFromGUID to cast bytes to a GUID. It generates pretty verbose GUIDs, though, with the format {guid {00000000-0000-0000-0000-000000000000}}.

Declare PtrSafe Sub CoCreateGuid Lib "ole32" (ByVal GUID As LongPtr)
Public Function NewGUID() As String
    Dim b(15) As Byte
    CoCreateGUID VarPtr(b(0))
    NewGUID = Application.StringFromGUID(b)
End Function

You can strip away unwanted characters, for example by replacing the last line of this function with NewGUID = Mid(Application.StringFromGUID(b), 8, 36). Then, the format will be 00000000-0000-0000-0000-000000000000

Failing

Answer 3

Since windows update taken out "Scriptlet.TypeLib", try the following:-

Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long
Public Function GenerateGUID() As String
    Dim ID(0 To 15) As Byte
    Dim N As Long
    Dim GUID As String
    Dim Res As Long
    Res = CoCreateGuid(ID(0))

    For N = 0 To 15
        GUID = GUID & IIf(ID(N) < 16, "0", "") & Hex$(ID(N))
        If Len(GUID) = 8 Or Len(GUID) = 13 Or Len(GUID) = 18 Or Len(GUID) = 23 Then
            GUID = GUID & "-"
        End If
    Next N
    GenerateGUID = GUID
End Function

Alternatively, if you are connecting to SQL Server 2008 or higher, try to use the SQL NEWID() function instead.

Jobe