Solved The VBA Project cannot be read ... (1 Viewer)

[Marshall Brooks]

I got a message that my post was spam-like, so I'm going to split it up:

I maintain a database for our company and the database is about 20 years old. There are 8 users. The database is split with a back-end on the server, and a front end that each user accesses (8 copies of the front end). Some tables are read from the backend to the frontend on startup.

About a week ago, some users started getting the following error when opening the database:

The distributed database is an .accde, but I get the error with the master .accdb file as well.

<more>

 

[Pat Hartman]

what I am calling the userforms

They are not userforms. Calling them userforms leads to confusion and if you search for userforms thinking that is the correct term, you will get solutions that are irrelevant.

This is seeming more like an Access and or Windows issue than a code issue, but I'm not able to truly judge that.

If it only affects this database, one of the objects (or more) is corrupted. It is very important to narrow down WHICH form is the problem so that is what you should be working toward.

Add a breakpoint in the code you got from Vlad so you can step through the export. Add a debug.print statement just before the export so you can see the name of the object where the export failed. You may have to rebuild this form from scratch. You can copy the code to notepad and that would eliminate any bogus characters so once you rebuild the form itself, you can paste in the code module.

@bastanu -- Vlad, I can't open this code module, can you post it as text so Windows doesn't keep trying to run it? Thanks.

 

[Marshall Brooks]

What is the proper term for what appears under "Forms" on the VBE Editor window - i.e. input boxes and progress bars and the like? If I call them "forms", I going to have people confused with the regular database forms which have code under the Class Objects tab.

So far, what seems to be working is Minty's suggestion to export each module as a .bas file and then import it. I have about 10 of the 19 modules copied that way and <knock on wood> am not getting the error message in that file (yet ...).

Also - I believe the error is in the Elookup module - http://allenbrowne.com/ser-42.html - but nothing really seems to resolve that - copying it as text fails, exporting as .bas and importing fails, copying from the website fails, one time just adding a new empty module failed. (By fails, I mean that the error message shows up, the database still works properly after that.)

I did get it to work properly one time by copying a few lines of the text file over and saving and repeating and I might have to resort to that again.

Or worst case I could leave it out and global replace eLookup with dLookup, but I like the way his code works (or at least how it used to work).

 

[bastanu]

Here you go Pat.

@Marshall Brooks: I am also attaching a .bas file with the module from Allen Browne, I imported into one of my files and compiled the code with no errors. Could you please post a screen shot of the VBE window showing the user forms you mention, this is what mine looks like, all forms and reports are under the MS Access Class Objects (running Office 2013 32-bit)

 

[Marshall Brooks]

If you didn't add them or drag them over from Word or Excel, your database won't have them. I'll try the updated files tomorrow. Thank you again for the assistance.

 

[bastanu]

What can I say, never saw or had a need for them, are there any references set in your VBA project to enable these? Why not use the InputBox function instead of a third party user form?
Good luck with this, please keep us posted if you can.
Cheers,

 

[Pat Hartman]

@Marshall Brooks are you using the code Vlad posted to export the objects?

 

[arnelgp]

if you have a chance, create a New db and copy/paste the original code to the new db.

 

[Marshall Brooks]

@bastanu - They require a reference to the Microsoft Forms 2.0 Object Library - Which is somewhat odd, in that that is not selectable, but if you create a Userform (correct term) in Word or Excel and drag it over, Access adds the record for you.

And it isn't third party - well, it is in the sense that it is added code rather than what MS Access provided, but it isn't adding code from some unknown and untrusted website.

InputBox was one example, but since you asked ... You can do a LOT more with it than that. I have progress bars - https://wellsr.com/vba/2017/excel/beautiful-vba-progress-bar-with-step-by-step-instructions/ - website is for Excel - You can't use Frames in UserForms In Access - I had to change that to textboxes, but once I did that, it works the same way.

I have a pop-up with 10 checkboxes and a button to select All/Deselect all.

I have an InputBox function, and I can have an Input box with two lines to fill in (for two different variables), and/or a combo box drop-down. And variable height depending on the length of the explanation text.

I have a pop-up calendar in the database that grays out non-work days (which aren't necessarily the same as recognized holidays) ...

Now, in fairness - I also use an Enhanced Message Box replacement (https://datenbank-projekt.de/beispiele/improved-enhanced-message-box-ms-access) Olaf set that up as a standard Access pop-up form - not a userform (or whatever the term is, nobody has told me except not to call them that on here) - and I could probably do that with my items above, but:

• There would be more forms in the database that I never wanted users to select. I could make them hidden and probably prevent users from showing hidden forms, but then it is more complicated for me to use/maintain.
• In most cases, I either have a UserForm working in Word or in Excel, and I can just bring it over and/or modify another form that I previously brought over. It would take a lot of re-design to figure out how to do it with a true Access pop-up form with no gain.

@Pat Hartman - Not currently. I had issues with @bastanu's code. His created database opened without the error message, but it didn't bring my forms over (and other items) over. When I imported the modules from his code to my rebuilt database or imported the form into his new database. I got the error message again. Right now, I am using @Minty 's suggestion of exporting as a .bas file from the old database (one-at-a-time) and importing to the rebuilt database), compiling, C&R, saving and repeating. Cumbersome, but so far it is working. Once I get the error cleared, I'll try @bastanu 's new code and find out I could have saved a lot of time.

@arnelgp - That's what I'm doing, but I've been having a hard time not transferring the corruption to the new database.

 

[Marshall Brooks]

Status Update: Something is wrong with ELookup and the current rebuilt database, but I don't know what it is.

I've been rebuilding my database to a clean database. I have about 10 of the 19 VBA code modules imported and I do NOT get the error message. I downloaded @Basanu's modElookup.bas file and imported it into my partial database. I saved it and renamed the module to ElookupAllenBrowne, compiled (with errors b/c I am still missing modules), did a C&R, and I now have the error. Went to the backup without the error and copied the module in and did not rename it from modElookup.bas and saved and now have the error. I don't think the error is with @bastanu's file, b/c I also got the error if I exported that module from my old copy of the database that worked with the error, and/or if I copied that data as text to the rebuilt database.

But I created a new blank database and only imported @bastanu's file and with that database I do not get the error message.

What I'm thinking is there is possibly something corrupt on one of the user forms that calls ELookup - and so if I import the form without the Elookup module, I don't get the error and if I import the Elookup module without the form I don't get the error, but when I have both, the error occurs.

I'm going to test with @bastanu's latest code now.

 

[Marshall Brooks]

I didn't realize @bastanu didn't post an updated file, just a text copy of the previous file.

I'm not sure where to put the debug.print statement, but I'm going to try again ...

 

[Marshall Brooks]

Progress: Not sure I mentioned it before, but I'm getting the error in Office 2016, not in Office 365. I've been doing my repair work in Office 2016, since if the db works there, it will probably work in the newer versions of Access. I stepped through @bastanu's code and didn't see any errors, but there were 19 error.txt files saying it couldn't import the form b/c it was created in a newer version of Access.

I'm going to test again using Office 365.

 

[Marshall Brooks]

There might be a light at the end of the tunnel - really odd process, though.

I ran @bastanu's file under Office 365 - took 45 minutes, but I'm working over VPN today, so not surprising. No errors with the process and no errors with opening the database. It did not copy my native Access macros (non-VBA), nor my ... <Forms that aren't class objects> - but that was expected.

I tried dragging my macros over and did a C&R and immediately got the error message, but that was in Office 2016. I'm going to try that in Office 365 and see what happens.

 

[bastanu]

@Marshall Brooks: Thanks for the detailed explanation on the user forms, seems like you have them "under control" so I agree that moving away from them at this point would be unwarranted. Hopefully you can identify if one of them is causing the error and maybe just replace that one with some native Access approach.

Cheers,

 

[Marshall Brooks]

I don't think the error is with the user forms, but I'll let you know as things progress. Thank you again for the assistance!!!

 

[isladogs]

I've only skimmed this thread but if I read it correctly you have imported user forms from Excel/Word into Access (hence the MSForms2.0 reference) in order to use features such as: input boxes, progress bars, message boxes and a replacement date picker (calendar)

If so, then I would instead use versions of each that are designed to work in Access and scrap the user forms which aren't intended for use in Access.
As well as the bult-in versions of each of those features, there are many replacements available. In fact, I have improved versions of each of those on my own website

 

[Marshall Brooks]

Ok, well, I introduced a new error ... Somewhat hard to explain ...

The file that @bastanu's macro created, when I ran it under Office 365, the database opens fine in Office 2016.
When I open it in Office 365 and open my main form (or probably any form), I get the following error:

Click OK three times and the form seems to open properly. No indication what is causing the error. Happens with all major forms, although the main one has more code under form load.

I then tried copying my macros over to the new file using Office 365. Did a C&R and tested with Office 2016 and the first error is back.

So now I have to click Cancel 7 times in Office 2016, or Ok 3 times in Office 365.

I haven't copied the user forms over yet, but I doubt that will help ...

If I compile, I get errors b/c variables on the userforms are missing, so I'm going to copy them just to see what happens when I compile.

@isladogs - I don't think the user forms are the issue. We've been using them for several years and this error just popped up last week ...

 

[isladogs]

I didn’t say they were the issue. My point is that they are intended for use in Excel/Word. Access forms have a much wider feature range.

 

[Pat Hartman]

@isladogs - I don't think the user forms are the issue. We've been using them for several years and this error just popped up last week ...

When you use objects out of context, you are asking for trouble and it sounds like it found you. Could be a change in anything that caused these forms to break because they weren't intended to be used in Access.

 

[Marshall Brooks]

@isladogs - Fair enough. Right now, I just want the db to open without errors like it did two weeks ago. And yes and no, there are a lot of native features in Excel/Word VBA that were never transferred to Access.

For everyone:

Okay - I'm back to where I started from (somewhat ...)

I copied the user forms over using Office 365 and I no longer get the second error in Office 365. Database compiles with no errors.

I still get the first error in Office 2016.

To recap, the error re-appeared when I copied my native macros to the newly created @bastanu database. I'm going to take a break and see if his code can also be modified to handle macros - unless he wants to do that (hint ...)

 

[Marshall Brooks]

Native macros might not be able to be done - I'm not sure where you find text for them ...

What is concerning is it somewhat seems like @bastanu's created file works, but once I make ANY changes to it, the error re-appears.