Problem with a function that MS Access can't find in Windows 10 Office 16.

I have an application in Access 2010 that I deploy with SageKey which includes the Access 2010 Runtime.  I have hundreds of users  and have successfully deployed it many times on Windows 10.

In the last few days, I have had 2 users report that they can't run the application due this error.

Can anyone help?

Thanks in advance.

Clive BeatonAccess DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

NorieAnalyst Assistant Commented:

Due to what error?
Clive BeatonAccess DeveloperAuthor Commented:
"The expression you entered has a function that Celebrant Assist can't find."
Sounds like this might be a references issue.  If the exact same copy of the file works on one PC but not another, there is some missing reference.  If you have the full version of Access, you will be able to find it by using an .accdb.  Open any code module and go to Tools/References to find the missing reference.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Clive BeatonAccess DeveloperAuthor Commented:
I've checked the references on both my Office 10 and 16 machines and they are all OK.  I may not be able to check them on the client machine.
Gustav BrockCIOCommented:
I may not be able to check them on the client machine.

But you will have to. You could create a small test app with the same references as in your normal app, and in this run this code:

VerifyReferences True

Open in new window

and let the users report their findings.

Public Function VerifyReferences(ByVal booErrorDisplay As Boolean) As Boolean

' Verify Access' external references and re-establish these if possible.
' Uses function IsBroken97().
' 2001-07-29. Cactus Data ApS, CPH.
' 2003-07-23.
'   Changes:
'   Added call to DoCmd.Echo True to allow for display of MsgBox.
'   Removed call to SysCmd() compilation which did not work.
  Dim refA                    As Access.Reference
  Dim refX                    As Access.Reference
  Dim strRefFullPath          As String
  Dim booNotBuiltInRefExists  As Boolean
  Dim booIsBroken             As Boolean
  Dim booRefIsMissing         As Boolean
  Dim strMsgTitle             As String
  Dim strMsgPrompt            As String
  Dim strMsgHeader            As String
  Dim strMsgFooter            As String
  Dim lngMsgStyle             As Long
  Dim strCrLf                 As String
  ' No special error handling.
  On Error Resume Next
  ' User oriented error message.
  strMsgTitle = "Missing support file"
  strMsgHeader = "One or more supporting files are missing:" & vbCrLf
  strMsgFooter = vbCrLf & vbCrLf & "Report this to IT support." & vbCrLf
  strMsgFooter = strMsgFooter & "Program execution cannot continue."
  lngMsgStyle = vbCritical + vbOKOnly
  ' Look for the first reference in the database other than
  ' the built in "Access" and "Visual Basic for Applications".
  For Each refA In Access.Application.References
    If refA.BuiltIn = False Then
      ' At least one not built in reference is in use.
      booNotBuiltInRefExists = True
      ' Check if the reference is not broken.
      If IsBroken97(refA) = False Then
        ' The first not missing not built in reference is found.
        Set refX = refA
        Exit For
      End If
    End If
  If booNotBuiltInRefExists = False Then
    ' Only built in references are in use.
    ' Nothing more to do.
    If refX Is Nothing Then
      ' All not built in references are missing.
      ' Don't remove missing references as there is no way to
      ' re-establish a reference if its identity is lost.
      ' Remove this not built in reference and add it back to
      ' force Access to revalidate all references.
      ' This may or may not rebuild links to missing references.
      With Access.Application.References
        strRefFullPath = refX.FullPath
        .Remove refX
        .AddFromFile strRefFullPath
      End With
      Set refX = Nothing
    End If
    ' Check references if any should be missing.
    ' If so, no attempt to read a reference is done as it most likely
    ' either is not installed or has been moved to an unknown directory.
    For Each refA In Access.Application.References
      booIsBroken = IsBroken97(refA)
      If booIsBroken = True Then
        ' Build list of missing files.
        strMsgPrompt = strMsgPrompt & vbCrLf & refA.FullPath
      End If
      booRefIsMissing = booRefIsMissing Or booIsBroken
    ' If any reference is broken, display error message if requested.
    If booRefIsMissing = True And booErrorDisplay = True Then
      strMsgPrompt = strMsgHeader & strMsgPrompt & strMsgFooter
      Access.DoCmd.Echo True
      VBA.MsgBox strMsgPrompt, lngMsgStyle, strMsgTitle
    End If
  End If
  Set refA = Nothing
  ' If References have been updated, the application is left decompiled.
  ' Run command in AutoExec macro to compile and save all modules.
  VerifyReferences = Not booRefIsMissing

End Function

Public Function IsBroken97(ByVal ref As Access.Reference) As Boolean

' Alternative method to check if a reference is broken
' as the IsBroken property cannot be used in Access97.
' 2000-03-19. Gustav Brock. Cactus Data ApS.

' Refer to this article at Microsoft Technet:
' Article ID: Q186720
' The information in this article applies to:
' Microsoft Access 97
' In Microsoft Access, IsBroken is a property of the References collection.
' The Microsoft Access Help topic on the Isbroken property states the following:
' The IsBroken property returns a Boolean value indicating whether a
' Reference object points to a valid reference in the Windows Registry.
' Although this statement is correct, to receive this Boolean value
' you must trap for errors that are generated by the broken reference.
' Also, the IsBroken property becomes True only when the file being referenced
' is deleted and the Microsoft Windows Recycle Bin is emptied.
' This article details the steps necessary to receive the Boolean value.

  Dim booRefOK As Boolean
  On Error GoTo Err_IsBroken97
  If Len(Dir(ref.FullPath, vbNormal)) > 0 Then
    booRefOK = Not ref.IsBroken
  End If

  IsBroken97 = Not booRefOK
  Exit Function

  ' Ignore non existing servers, drives, and paths.
  Resume Exit_IsBroken97
End Function

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you list out the references that are in your application?
Clive BeatonAccess DeveloperAuthor Commented:
Gustav; Thanks I will try it.

Visual Basic for Applications
Microsoft Access 14.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Office 14.0 Object Library
Clive BeatonAccess DeveloperAuthor Commented:
Gustav:  I ran the code as the first thing in my app and there wee no missing references.
Gustav BrockCIOCommented:
Interesting. But I have no clue what could cause this.
Does SageKey leave an install log file on the user machine? If so, you could address the issue at SageKey support.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Were the users able to run it previously? Or was this a new installation?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does SageKey leave an install log file on the user machine? If so, you could address the issue at SageKey support.>>

 I would check the Windows event logs as well.

Clive BeatonAccess DeveloperAuthor Commented:
Scott:  Both are new installations on a new machine.  I have many users who have done successful installs on new machines with Windows 10.  Most do not have Access installed but these do.  I'm wondering if it could be a clash with Office 16.  Having said that, I can run it on my Windows 10/Office 16 machine.

Jim: I'll check both.
Gustav BrockCIOCommented:
Most do not have Access installed but these do.

That you didn't tell. If so, the runtime is not needed, but the installer should take care of that.
The log should list why the install fails.

Clive BeatonAccess DeveloperAuthor Commented:
Gustav: I understand the Runtime is required but I will check.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm curious why you have a reference to DAO 3.6 in an Access 2010 application instead of referencing the Microsoft Office Access Database Engine Object? Are you working with older .mdb formatted files?

Gustav: I understand the Runtime is required but I will check.
The runtime is not needed (nor can it be installed) if you have a full version of Access on the machine.

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
Clive BeatonAccess DeveloperAuthor Commented:
Thank you all.  Most useful.  I appreciate it.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just curious - what was the eventual solution?
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
Windows 10

From novice to tech pro — start learning today.