Results 1 to 15 of 15
  1. #1
    GID is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Location
    USA
    Posts
    14

    VBA Module Will Not Compile

    Something in the VBA code module of a vital Access databaseis causing a “Compile error,” rendering all of the many functions in the moduleunless. The error message always associates the problem with one function, butchecking that one, or even deleting and retyping it, just makes the error specifya different function. In other words, the code is ok, but there is something(unseen) there that interferes.


    I’ve tried several things including copying the module textto a Word text document, hoping that would eliminate the problem, and then copyingthe text back into a new and empty module. Other Access databases, with codemodules, are working fine, so I don’t believe the problem is a corruptedcompiler.
    Is there any way to fix this without having to retype thatlengthy module from scratch?
    There should be a Microsoft “fix” for this.


  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have you tried DECOMPILING? That may solve your issues.
    See http://www.fmsinc.com/microsoftacces.../Decompile.asp
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    do you have any more information other than 'compile error'?
    are you using option explicit?
    have you checked for missing references?

  4. #4
    GID is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Location
    USA
    Posts
    14
    Thanks for that suggestion. I decompiled, following thoseprocedures. When I compiled the module, I got the same “Compile error: Invalidprocedure call or argument” at the first function in the module. Even though Icould see nothing wrong with that function, I commented it out and recompiled.It stopped at the next function, etc. . . .The procedures (functions) in themodule have worked for years.
    Even though the error message specifies one function, theproblem is with compilation of the whole module.


  5. #5
    GID is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Location
    USA
    Posts
    14
    No, I am notusing Option Explicit, and I am not familiar with checking for missingreferences.


  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    recommend putting option explicit at the top of every module (just below option compare database). You can set this default in the vba editor by going to Tools>Options and ticking the 'require variable declaration' option which will automatically add it for new modules

    for references, in the vba editor go to tools>references - you may see one or more flagged as 'missing'

    as a minimum you should have

    Visual Basic for Applications
    Microsoft Access XX.X Object library (for 2010 this would be 14.0)
    Microsoft Office XX.X Access database engine Object library

    You may well have more (such as for Excel, word, scripting etc)

    Also I note your tag says you are using 64bit Access - in which case any API calls need to be changed - to include the word ptrSafe in the declaration and where relevant, pointers (which are long in 32bit) are stated as longlong (64bit specific) or longptr (determined at run time). google 'differences between 32bit and 64bit access' to find out more.

  7. #7
    GID is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Location
    USA
    Posts
    14
    So sorry, Iaccidentally checked the 64-bit version, when I’m using the 32-bit one. And allthe usual references are appropriately checked, and all of my other Access databasesare working properly.

    I appreciateyour suggestion. I believe I understand the advantages of Option Explicit for catchingvariable misspellings, but I’ve been programming in Access for some 20 years ormore and never used Option Explicit. The module in question is quite long, andgoing through it to explicitly declare every variable would be a huge chore –admittedly not nearly as huge as having to recreate the module, especially whencutting and pasting functions from the old one (even through the interim stepof a Word text file) seems to eventually carry with it the same compilationerror.

    Since mydata are intact (in a back-end database), yesterday, I tried starting freshwith a new database and copying into the new module, one-by-one, just the functionsneeded for one report. When it crashed, one would think deleting the lastfunction pasted would fix the problem, but it didn’t. Again, if I comment outthe function specified by the error message, the compilation just pauses at thenext function, so I doubt that the problem is variable declarations.

    I’mcertainly not arguing with someone whose help I appreciate, but it would bedepressing to spend hours declaring variables, only to find that does not solvethe compilation problem. If I’m wrong, please tell me.

    Thanks.



  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Is the FE in question located in a trusted location?

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    only other thing I can think of is you have a naming issue - function/sub same name as module or a function/sub in another module.

    Also just googled your error message and found

    https://docs.microsoft.com/en-us/off...gument-error-5

    normally a specific line will be highlighted but you said

    The error message always associates the problem with one function
    what is actually highlighted? perhaps you can provide a screenshot of the error line

    Even though the error message specifies one function, theproblem is with compilation of the whole module.
    the complier will stop at the first error and you can't get beyond that until it is resolved

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The procedures (functions) in themodule have worked for years.
    what has changed recently? Windows or Access version? Office version?
    If every module won't compile (as stated, fix or comment out one, the failure just trickles down to the next one) then it is something generic, like references or bitness, but that seems to have been covered. Then maybe all functions use early binding and the wrong Office versions related to those references.

    surely you can post at least 1 function (please enclose in code tags - thread menu bar #) so we can zero in?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Or another thought related to possible corruption issues ...
    Create a new blank database and add all references required for your database
    Add options such as Require Variable Declaration
    Add one module from the original db & see if it compiles.
    If it does, then add Option Explicit and repeat.

    Repeat the above for each module in turn until all are imported.
    If all is OK next import all tables/queries.
    Then add macros (if you have any), forms and reports.
    Compile after each step.

    At the end, make a backup, decompile, recompile and finally compact.
    Be systematic. It will take a while but in my opinion the rewards of having a fully functioning database outweigh the time needed.

    If at any stage the process fails, come back to us with further info.

    There is another possibility of errors in a system table such as MSysObjects.
    I have a strategy for dealing with that as well if necessary ... but will leave that for now
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    GID is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Location
    USA
    Posts
    14
    Not sure what FE is, but the BE (back-end) is on my hard drive, which is secure.

  13. #13
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Well, if there's a BE, there must be a FE (front end) somewhere. Trusted locations are not the same as Windows file security protections. If you're not familiar with Office trusted locations, Google it.

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    A database needs to be in a trusted location for macros or vba to work.

    see this link for a start ... http://www.simply-access.com/TrustedLocations.html

    Are you upgrading to a new version of Access? if so what was the version it was written in?

    edit: By the way, there are other reasons to declare variables other than spelling errors. An undeclared variable, I believe, is a variant by default. You're relying on access to decide for you and variants require the most memory.

  15. #15
    GID is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Location
    USA
    Posts
    14
    Thanks forall your suggestions. The database in question is on a desk top hard drive – boththe FE and the BE -- along with quite a few other Access databases that are allworking properly. Nothing has changed in regard to Windows, Office, or Access. Iam the only user. The BE data are intact, redundantly backed up, and successfullyaccessible by other Access databases.
    I’m going tohave to back off this project for a week or so to work on others, but I’m beginningto wonder if, by chance, I’m having a have a String Space problem with thislengthy module that I might need to trim down.
    In any case,some of your suggestions will be painstaking and time-consuming, so I’ll beback on the Forum in a week or two. Again, thanks.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Compile error
    By Gregm66 in forum Access
    Replies: 9
    Last Post: 09-20-2016, 10:22 PM
  2. Compile error next without for
    By cc.caroline15 in forum Programming
    Replies: 3
    Last Post: 03-08-2015, 01:33 PM
  3. Compile from Database
    By Stefan Moser in forum Access
    Replies: 2
    Last Post: 03-05-2015, 04:23 AM
  4. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  5. Replies: 4
    Last Post: 05-16-2011, 04:58 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums