Avatar of Mel Brooks
Mel Brooks asked on

Access 2013 ACCDE file not running on specific computers

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.
Microsoft OfficeMicrosoft 365Microsoft AccessVBA

Avatar of undefined
Last Comment
Mel Brooks

8/22/2022 - Mon
Gustav Brock

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)

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 Tsioumpris

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Mel Brooks

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 Pineault

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 )

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

Has to be references then as gustav said.

Jim.
ASKER
Mel Brooks

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)

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.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Mel Brooks

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.
Gustav Brock

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
ASKER CERTIFIED SOLUTION
Mel Brooks

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Tsioumpris

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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Mel Brooks

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