Solved The VBA Project cannot be read ...

[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>

 

[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,

 

[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.

 

[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.

 

[Marshall Brooks]

Minor update: I took the created file and copied my user forms over to it and that file works in either version of Office.

So I have everything imported and working, except for my native Macros - particularly my AutoExec macro ...

Dragging that across from the old database in Office 365 gives me the error in Office 2016.

Importing that macro: External Tools>New Data Source>Access in Office 365 gives me the error in Office 2016.

Next I'm going to try manually re-creating the macro in the new file.

 

[Marshall Brooks]

Almost there, but I want to back up a bit ...

My AutoExec macro loads a form as hidden, deletes about 8 tables from the FE if they exist, copies the same table from the backend, and then opens one of the forms.

The commands are a bit different but I could do all of this in VBA and then @bastanu 's macro would have probably fixed this.

Before, I tried to do this in VBA creating a subroutine called AutoExec() and it didn't work. The code worked, but it didn't run on startup.

Is there a way to accomplish this? I can see several options, but not sure what is best:

• I'm not sure how to have the code automatically load. Having a subroutine named AutoExec in Module1 didn't work. Would creating an AutoExec Module work?
• I could change my Access AutoExec Macro to call the AutoExec Subroutine, but I'm still using an AutoExec Macro if I do that.
• The last step of my macro opens a form - I'll call it "FORM A". I could simply call AutoExec from the OnLoad Event of Form A and then set Form A to load at startup from the This Database Options menu. Seems a bit circular, though - i.e. the first line of AutoExec loads a logout timer form. That checks for the existence of a file and then loads form downformaintenance. Then the autoexec checks for that form and exits if it is found.
• I could create a separate AutoExec Form that loads hidden and put the startup code in the Load Event for this form.

How is this normally done?

As far as the database - I loaded all of my macros EXCEPT AutoExec and it works fine in both Office 365 and Office 2016. I manually created AutoExec and loaded the hidden form, but I haven't added any other commands, but that version is working both in 365 and 2016.

 

[adhoustonj]

Almost there, but I want to back up a bit ...

My AutoExec macro loads a form as hidden, deletes about 8 tables from the FE if they exist, copies the same table from the backend, and then opens one of the forms.

The commands are a bit different but I could do all of this in VBA and then @bastanu 's macro would have probably fixed this.

Before, I tried to do this in VBA creating a subroutine called AutoExec() and it didn't work. The code worked, but it didn't run on startup.

Is there a way to accomplish this? I can see several options, but not sure what is best:

• I'm not sure how to have the code automatically load. Having a subroutine named AutoExec in Module1 didn't work. Would creating an AutoExec Module work?
• I could change my Access AutoExec Macro to call the AutoExec Subroutine, but I'm still using an AutoExec Macro if I do that.
• The last step of my macro opens a form - I'll call it "FORM A". I could simply call AutoExec from the OnLoad Event of Form A and then set Form A to load at startup from the This Database Options menu. Seems a bit circular, though - i.e. the first line of AutoExec loads a logout timer form. That checks for the existence of a file and then loads form downformaintenance. Then the autoexec checks for that form and exits if it is found.
• I could create a separate AutoExec Form that loads hidden and put the startup code in the Load Event for this form.

How is this normally done?

As far as the database - I loaded all of my macros EXCEPT AutoExec and it works fine in both Office 365 and Office 2016. I manually created AutoExec and loaded the hidden form, but I haven't added any other commands, but that version is working both in 365 and 2016.

You could put the code in the form_load() event and call that form to be the starting display form under options -> current db -> display form

 

[Marshall Brooks]

It works!!!

Thank you all!!!

I decided not to put the code in the form load event and just to convert it to a function and call that from the AutoExec macro. There might be times that I want to bypass the startup code and still open Form A and this will allow that - and if it ever gets corrupted again @bastanu 's code will fix the VBA function and worst case I just have to manually re-create one line in the AutoExec macro.

Final question (maybe ). My Startup code goes through and deletes local tables from the front end if they exist and then copies updated tables over from the backend if they don't exist - so the partial code looks like this:

    If (Not IsNull(ELookup("Name", "MSysObjects", "Name='tblHolidays'"))) Then
        DoCmd.DeleteObject acTable, "tblHolidays"
    End If
    If (IsNull(ELookup("Name", "MSysObjects", "Name='tblHolidays'"))) Then
        DoCmd.TransferDatabase acImport, "Microsoft Access", "\\network path\be.accdb", acTable, "tblHolidays", "tblHolidays", False
    End If

I can't say that is truly is related, but I've got 18 calls for ELookup on startup and that was the module that I had the most trouble importing back into the database.

I probably found that online and it works, but is there a simpler way to write this?

In English, the code would be:

If tblHolidays exists, delete tblHolidays
If tblHoldays does not exist, import tblHolidays.