Share via

Access VBA References caused compile error

Andrew Collins 61 Reputation points
2024-05-08T17:38:21.3566667+00:00

An Access database in Microsoft Office 365, which is up to date, uses references to several other programs, selected in the Tools | References menu in the VBE. It has compiled successfully for several months as it is developed. It then suddenly failed to compile after using Debug | Compile, producing an error box saying "Compile Error. Object library feature not supported" and highlighting "Set objXl" in the line "Set objXl = GetObject(, "Excel.Application"). Checking the references, MS Excel 16 Object Library was present and ticked as shown in the first jpg. After much experimentation I moved Excel up the priorities to the position shown in the second jpg.RefLibrary1RefLibrary2

All now worked correctly when I compiled. I noted that updates had been applied to MS365 in the preceding days, and also that a registry change that I had made to MaxLocksPerFile had reverted to the default. My query is whether the updates might have changed something. Also, have the various references that I have ticked produced a conflict somewhere that was resolved by the change in order of preference? Finally is there somewhere where I can learn more detail on precisely how the references operate?

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
838 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
2,028 questions
0 comments
Accepted answer
  1. Tom van Stiphout 1,701 Reputation points MVP
    2024-05-08T19:50:35.37+00:00

    How is objXl declared? It should be:

    dim objXl as Excel.Application

    Then the Set objXl line, which is currently using late binding, should be changed to:

    Set objXl = New Excel.Application

    That is the proper way to do early binding.

    See also: https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/early-late-binding/

    I have never heard of an Office update changing MaxLocksPerFile. Are you SURE? That value is in the registry several times.

    Did you ever decompile your application? You should, and it may improve the next Compile. My guess is that your reshuffling of references caused such decompile, and you benefited from that.

    I would never put any optional references above the standard four.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 1,701 Reputation points MVP
    2024-05-08T19:48:51.21+00:00

    How is objXl declared? It should be:

    dim objXl as Excel.Application

    Then the Set objXl line, which is currently using late binding, should be changed to:

    Set objXl = New Excel.Application

    That is the proper way to do early binding.

    See also: https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/early-late-binding/

    I have never heard of an Office update changing MaxLocksPerFile. Are you SURE? That value is in the registry several times.

    Did you ever decompile your application? You should, and it may improve the next Compile. My guess is that your reshuffling of references caused such decompile, and you benefited from that.

    I would never put any optional references above the standard four.

    0 comments
  2. Andrew Collins 61 Reputation points
    2024-05-09T08:48:35.26+00:00

    Tom

    Many thanks for your comments on this query, which confirmed my level of ignorance.

    objXl is declared as a private variable at the top of a module that contains several procedures that are called from the main procedure in the module and which also use objXl. It is declared, as you suggest, as Dim objXl as Excel.Application.

    I am by no means sure when or how the MaxLocksPerFile was reset to default of 9300. I just know that I did not make the change back. Possibly it came from a Windows update.

    Your comment about decompile is probably correct. I had done a compact and repair to try to sort out the problem, without success, and I had wrongly though that that, (and making changes to the code) caused a decompile because "Compile" was again shown in the VBA Debug menu. The DB in still being developed, so I had several previous versions going back some weeks. I checked them, and from about a week back they all compiled correctly without shifting the Excel reference up. The only changes since the last successfully compilable version were the addition of a new form containing some code. Your comment that it is not advisable to move added references up above the standard four caused me to move it back down to its original position, and to my relief the current version still compiles successfully. From that it seems likely that your suggestion that the move caused a decompile is correct, and that something in my last few versions has caused a problem, that unfortunately will now be untraceable.

    So thank you for your help, and I am now somewhat wiser on the topic of decompiling.

    0 comments