We help IT Professionals succeed at work.

Access 2013 ACCDE file not running on specific computers

79 Views
Last Modified: 2020-04-21
We have an Access front end application (sql server backend) which is developed in Office 2010 and compiled using Access 2010.  When we distribute the application, it has run with no issues on systems running Access 2010 - 2019 in retail versions,  volume licensed editions,  Office 365 subscriptions , on OSs from Windows 7 through Server 2016.
Now we have Lenova workstation that when running simple VBA function code its giving an error.

"3075 function is not available in expresssions in query expression  ((tblEmphistoryStartDate) < Date() - 1)"

If I compile the Access application on this machine, then I do not get this error when running the compiled accde version.
The 2013 build on this computer 15.0.5111.1001.  is the same as a server on the same network and it runs the accde compiled in 2010 without error.

I'm lost as to what to look for to correct this on the workstation.  I have run Office Repair from Control Panel and that did not correct it.  

Any suggestions are welcome.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Most likely it is caused by one or more missing references.
So, create a normal accdb version, and then run this on the offending machines and study the references and if any of these couldn't be resolved leading it to be marked MISSING.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Mel,

 What you need to watch out for is 32 versus 64 bit installs. An .accde cannot be recompiled on the fly because there’s no source code as part of it. So if you compiled it on a 32 bit  Machine then it must run on a 32 bit  Office install. If the user has 64-bit office installed it’s not gonna fly .

Jim

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
In this post : https://social.msdn.microsoft.com/Forums/en-US/d0daa06d-c6ac-4582-afc9-92df27ab98d3/how-to-get-programmatically-a-list-of-all-available-references-of-a-vba-project?forum=isvvba
there is a nice function to list all the references..just compare the lists between the "running" and the "not running" machines.

Author

Commented:
All installations are Office 32 bit.  Our application will not even launch on an Office 64 bit installation.
No missing reference appear when viewing the code using the accdb version.  The vba/vbe dll files appear to be identical on offending machine to those on other machines.  
This issue appears to be very similar to that caused by KB3085515 update for Office 2010 about 4 years ago.  But this is Office 2013 and that KB is not listed in the updates, nor should it be.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Shouldn't tblEmphistoryStartDate  be tblEmphistory.StartDate ?
Do you have any empty procedures?  If so, delete them all.
Does your VBA compile without errors?
Can you post the full SQL Statement of your query.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is this a new workstation? If so, you may have a corrupt install of Office (or Windows). The Repair option sometimes doesn't do a very good job, and you have to manually uninstall and reinstall:
https://support.office.com/en-us/article/uninstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5dbf61d8
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Has to be references then as gustav said.

Jim.

Author

Commented:
This issue is now no longer happening on this one computer, but is also happening on another Lenova machine with the identical install of OS and Office.  I've seen this similar error twice in my career.  Once around 2012 when a specific brand of computer that previously had an older version of MS Access installed on it and was recently updated to Access 2007 or 2010.  The only fix for those computers was to completely re-install the OS and then install the new version of Access.  The other time was 4 years ago when KB3085515 update came out.  Users started getting this error running the accde version, but using the uncompiled version of Access, the error never happened.  In that case, uninstalling the update fixed the issue.
So now I'm getting this same error again in only the accde and seemingly no resolution to the problem.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I would just add that it probably has nothing to do with the machine brand, just what is installed on it. I missed this in your question:

If I compile the Access application on this machine, then I do not get this error when running the compiled accde version.

 That tells you right there that an existing reference was corrected when you compiled and nothing is wrong with the DB or the code.   It's just a reference issue.

You could add some simple startup code to check the references:

Dim ref As Reference
If Application.BrokenReference Then
   For Each ref in Application.References
      If ref.IsBroken then
          ' Log the error
         Debug.Print ref.name & " is broken."
      End if
   Next Ref
End If

But there is better code here:

https://www.devhut.net/2017/03/03/vba-list-references/ 

 Which you might want to try out.   It gives you a lot of detail on the references that are loaded and what is broken.

 Also there is an old MSKB article here that gives an overview of refrences and has a few pointers in it:
https://support.microsoft.com/en-us/help/310803/how-to-resolve-reference-issues-in-an-access-database 

 The one in paticular that is a gem is Refreshing the refrence list.   Sometimes all it takes is adding a reference and then clearing it to get a DB to work.   Not sure why or what the process is behind that, but I've often found it to be the case.

Jim.

Author

Commented:
I added the reference code to the database and compiled it.  When loading the compiled accde on the affected computer, no missing reference was detected.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You may need a deeper test of the references called by an AutoExec macro.
The attached demo shows an examples of a missing reference not marked as MISSING.

Then modify it to include the references of your application. Compile, save, and run it on the offending machine.

CheckReferences.accdb
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
If mysterious problems arise do check your references...you might reference an ocx that looks the same, "feels" the same but is not the same...
i had a "hell broke loose" case  some years ago when an Ms .ocx got  a bit of minor upgrade...like from version 1.5.614 to version 1.5.617 (can't remember the exact .ocx, version numbers)...well everything was working great on my machine since i had the latest version and i have compiled my copy against it...guess what,  on everyone else it crashed.... crashed really badly....when i got the ocx to same level everything started working just fine...(it might have the same size...my memory is not at it's best...but do check that you have the exact same build numbers)

Author

Commented:
Thanks to all experts who provided suggestions.  I've learned from it and I'm sure I'll be using some of that code in the future

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.