MS Access 2016 .Net Framework Reference

katerina-p
katerina-p used Ask the Experts™
on
Problems with MS Access Reference(s) after changing from a server with 2008 R2 to 2012 R2 (and a new install of Access 2016)
The following code is what has initially highlighted the issue.

Public Function fSHA256Base64(s As String, k As String) As String
      Dim lngLoop                   As Long
      Dim oUTF, oEnc
      Dim hmac()                    As Byte
      Dim strText                   As String
      Dim strTemp                   As String

12890 strText = s
12900 Set oUTF = CreateObject("System.Text.UTF8Encoding")
12910 Set oEnc = CreateObject("System.Security.Cryptography.HMACSHA256")

12920 oEnc.Key = oUTF.GetBytes_4(k)
12930 hmac = oEnc.ComputeHash_2(oUTF.GetBytes_4(s))
12940 strTemp = Base64Encode(hmac())
12950 fSHA256Base64 = strTemp
End Function

Open in new window


Line 12900 throws run time error -2146232576 (80131700) ‘Automation Error’.

SVR with 2008R2 had referenced System.tlb from the v2.0.50727 .Net Framework folder.
SVR with 2012R2 has referenced from the v4.0.30319 folder.


However, when I change new server from v4 to the v2 file, even though it appears to have changed it (judging by the 'Location' reference), the next time I open the Reference panel it has changed back to v4! This happens even on a new file where the .net wasn’t even referenced to begin with. Ie I add system.tlb from the v2 folder. When I next open references the ref location is the v4 folder!


1)      Have I correctly identified the .net version as the cause of the actual error?
2)      How can I force the reference to the older version / stop it changing itself?

Many thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Not sure I understand how you got away with it in the first place, but you can't call .Net managed code directly from VBA without going through a CCW (COM Callable Wrapper), which serves as a go between from VBA to .Net.  This wrapper gets developed in .Net.

Since these are basic function, I'm sure there is direct VBA code floating around (I'm sure there is for UTF8Encoding) and what I'd do is drop the .Net calls entirely and use the VBA Equivalents.

Jim.

Author

Commented:
Jim, Thanks for your comment. I can't help on that - this is extremely old code which I've dragged through multiple versions of Access and Windows!

However the behavior on the references is still something I'd like to understand. There are multiple other references I have to set each time I open Access 2106 (eg, from XML 6 down to 5).
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<However the behavior on the references is still something I'd like to understand.>>

 VBA will auto update references to whatever is registered on the machine.  It's automatic and you can't stop it other than to make sure whatever you use is installed on the machine.  

 I've only see it do that though with Office references, never references that I set myself.   In general, your best bet is to use late binding (no reference set and just use CreateObject() at runtime).  That way, you don't tie yourself to a specific reference.

 In this case, given the functions, I would replace the calls with VBA code.  For UTF8Encoding, I found a number of them with a quick Google search.  I'm sure you can find some for the SHA256 hash as well (in fact I think I have some here - might have to dig though).

<< There are multiple other references I have to set each time I open Access 2106 (eg, from XML 6 down to 5).>>

  For each project, you should only use the minimum references required.   If you think your not using a reference, uncheck it, do a compile, and if your not using it, you'll get no errors.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial