none
"There was an error compiling this function" RRS feed

  • Question

  • After a recent update from Office 2016 to Office 365. Trying to open a query in access gives the error "there was an error compiling this function. If I create a new query and include a function e.g Left, Instr, Trim- I get the error" There was an error compiling this function" "The Visual Basic module contains a syntax error" "Check the code, and then recompile it.  

    I moved all objects to a new database but the problems stills exists. Any advice. Thx

    
    Wednesday, August 21, 2019 2:33 PM

Answers

  • This is what has been causing the error
    Public Declare Function GetUserName Lib "advapi32.dll" _
     Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Apparently the library file advapi32.dll was removed after the Windows 64bit upgrade. I removed the code and all the queries(with functions) now work.

    • Edited by Bukka Tuesday, August 27, 2019 6:43 PM
    • Marked as answer by Bukka Wednesday, August 28, 2019 1:39 AM
    Tuesday, August 27, 2019 6:43 PM

All replies

  • Errors with built-in functions like Left() usually mean a missing library in your resources. Press Ctrl+G to open the code window. Under Tools>References look for any marked as "MISSING' Uncheck it and check the latest version in the list.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, August 21, 2019 4:02 PM
  • Yeah, I found this solution earlier but it did not work for me. I scrolled through all the available references and there were not any references marked or unmarked as "MISSSING".  Thanks Bill

    

    Wednesday, August 21, 2019 4:32 PM
  • Hi Bukka,

    I'm not sure if it's related to an Array function error. No idea what your problem, maybe you could post an example to demonstrate it.

    Array Error

    Thursday, August 22, 2019 5:22 AM
  • As I mention before these errors started with the upgrade from Office 2016(32bit) to Office 365(64bit)

    I was able to access the database on a machine that has not been updated as yet. But it will be upgraded soon. And I have already been told by the IT department that they will not be rolling back anyone to x32 Office apps. So I am in a pickle


    • Edited by Bukka Friday, August 23, 2019 12:00 PM
    Friday, August 23, 2019 12:00 PM
  • your original post says the error is thrown: "Trying to open a query"

    Is this a fixed query object in the navigation pane or an sql statement in VBA?

    To trouble shoot, if it is an sql statement, you should convert it to a fixed query object and attempt to run it manually to see what happens.

    If it is already a fixed query object....build a new one from scratch and test.

    This process doesn't fix it per se - but will probably highlight where the problem is....am wondering if you have nulls in a join field or something like that....possibly a data type mismatch....

    If it works as a fixed query object - - then view it in SQL View and note the syntax to reuse when converting it back to an SQL statement if that's where it needs to be.....


    Saturday, August 24, 2019 4:32 PM
  • This is what has been causing the error
    Public Declare Function GetUserName Lib "advapi32.dll" _
     Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Apparently the library file advapi32.dll was removed after the Windows 64bit upgrade. I removed the code and all the queries(with functions) now work.

    • Edited by Bukka Tuesday, August 27, 2019 6:43 PM
    • Marked as answer by Bukka Wednesday, August 28, 2019 1:39 AM
    Tuesday, August 27, 2019 6:43 PM