MS Access 2016 .Net Framework Reference

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

katerina-pAuthor 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).
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.