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>

 

[Marshall Brooks]

Looks like you don't like links ...

Oddities:

• If we click Cancel, the message comes back up, but if we click Cancel 7 times, the database opens and appears to work normally.
• The error started occurring after a recent update to the database, but it does not seem to be related to changes made from the update.
• The error also occurs with previous versions of the database, which worked before the database was updated. Now those versions also give the error, but nothing was changed in the files.
• Other Access databases open without the error, so this isn't an across-the-board issue with Access.
• datanumen<dot>com/access-repair/errors/vba-project-cannot-be-read/ - This website says the error can occur without any VBA code in the database, but doesn't offer solutions other than their paid software.

<more>

 

[Marshall Brooks]

Solutions Tried:

• The database contains about 5,000 records in 7 tables. It isn't really a relational database - we use it more like Excel with a GUI and a lot of background VBA code. The front end was previously about 420 Mb. I linked to the background images and decompiled and got it down to 24 Mb, but did not clear the error.
• I can't really share the database - too much proprietary info in it and if I took all the info out, that in itself might resolve the error.
• I did a compact and repair of the database per <Google EverythingAccess Steps to recover from Access Database Corruption> - I didn't know about name auto-correction, but I have it off now, but it has been on for all the years we've been using the database.
• I tried importing the database into a new blank database. The Tables, Queries, Forms, and Macros (Access, not VBA), imported successfully, (there are no reports) and I didn't get the error with the new database.
• I tried importing the VBA code into the new database. There are 19 modules of VBA code in the database. I imported three of them without getting the error. I imported another one and got the error.

<more>

 

[Marshall Brooks]

• I tried copying the module that was giving me the error - this one: <Google: everythingaccess Extended Replacement Dlookup> to Notepad and then pasting it into the VBE window and got the error. I re-copied it from the website and pasted it into the database and got the error (The module is named ELookup-AllenBrowne - I think naming it the same as the function is a bad idea (and didn't do so) - I removed the dash and still got the error).
• I tried inserting a new blank module with the same name as the problem module and did not get the error.
• I copied the code from Notepad several lines at a time and got the entire module working with no error
• I then saved a copy of the new database and tried to import another module and got the error again.
• I tried adding a blank module with the same name as the problem module and got the error with that.
• I then opened the version that was working with Elookup added and found out that NOW, it gives me the error also.
• I copied this info for reference previously, but I'm really not seeing that it adds much that I'm not already doing above: <Google Stackoverflow Access crashed on loading userform 41125239>
• Finally, I tried re-building the database using the previous version of Access under Citrix (which does give me the error), but I didn't get any further than above - i.e. the forms, queries, tables and macros copied over fine. Three of the modules copied fine. The fourth one gave me an error. I can't run a command prompt in Citrix, so I don't have the option to decompile from there.

I'm out of ideas, so I'm really hoping someone knows a solution!!!!

 

[Minty]

It sounds like a corrupt module.
You can export the Code modules as a text file. I would do this with the offending one that appears to have caused the crash.

You can then try importing the text file. This should remove any trace of the corruption, but it will be worth testing as you go.

Edit: sorry our posts Crossed in the ether.
In between imports - recompile each time then compact and repair after each module.

 

[Marshall Brooks]

@Minty - Thanks - I think you replied before my latest posts - maybe not.

A couple of oddities that make me think it isn't that ...

• Once you click Cancel on the messages the database works properly. When I was rebuilding it, I got the error on the Elookup error, but if that module were corrupt, I would think it wouldn't run. There are tons of times the database calls to that module.
• I don't know which of the 19 modules are causing the error. 3 of them imported fine and then with the 4th and 5th ones, I got the error.
• Once the error occurs, copying and pasting text doesn't work - in fact, taking the working partial re-build of the database and added a blank module (module2) caused the error.
• And more confusingly, the copy of the partial rebuild that was working then gives me the error.

 

[Minty]

Have you got any code or forms that run on start up?

Try disabling that, and don't copy and paste the code, use the export function in the right click menu:

Then after each modules import do a Compile.
If that doesnt work you might need to get someone to have a look at it for you.

 

[adhoustonj]

Have you tried a decompile? http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

I see in post #3 mentioning decompiled after linking images but in post #4 it says no access to decompile from citrix. You should just be able to paste the msaccess.exe path with /decompile in your windows search bar.

C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE /decompile

 

[Marshall Brooks]

@Minty - Yes, lots of code that runs at startup, a form that loads hidden and a main form that loads - all with background code also.

If I hold down shift when I open the database, it doesn't run the startup code, and I don't get the error, but the database isn't usable either.

To Clarify - and ask about a couple of points:

I've been rebuilding the main database (I'll call it MAIN) into a blank database - I'll call it BLANK.

For the Module Code - I'm been importing into BLANK from MAIN (External Data>New Data Source>Access Database.

I've also tried copying as text and pasting and that didn't work.

You are recommending EXPORTING the module from MAIN and importing into Blank, correct?

Also - the modules are interrelated, so when I compile, I will get errors simply b/c some of the modules aren't imported yet.

@adhoustonj - I did a decompile in Access 2019 and still get the error message in Access 2016 in Citrix. I don't have a windows Search Bar in Citrix. I MIGHT be able to create a shortcut and add decompile to the target in Citrix, but I didn't see a way to do that.

 

[bastanu]

Here is an old module I have that would use savetotext\loadfromtext to restore a front-end, I successfully used a few times to remove corruption in front-end files. The code was written for mdbs but I didn't see anything in it that would prevent it from running inside an accdb file. Import it into your MAIN (a copy of it ) accdb file and run the SaveMDBObjectsAsText sub.

 

[Marshall Brooks]

Update - @bastanu - Thanks, but that didn't work - it did, but ... (Meaning I imported the file and ran the sub and got the message that everything completed, but I still get the error in the test file.)

MAYBE that helps track down the error.

@Minty - Exporting and importing seems to possibly be working. I have one module that is giving me issues, but several others imported without causing the error, so I'm further along than when I started.

 

[adhoustonj]

If you hold down shift when opening the db does it let you open the VBA window? And if so could you then open and post the module code here?

 

[bastanu]

You tried to open the newly created db right, not the one in which you ran the code? Try to comment out the On Error Resume Next and see if you get any errors that might point to the issue.

Cheers,

 

[Marshall Brooks]

@ bastanu - No, I thought it repaired the open database, I didn't know it created a new one. Okay - I looked at the code and I figured out where it saved the new file. The new file opens without any errors, but I looks like all the modules are there, but none of the forms, userforms, queries, tables, etc. So I think I have two options - but I'm further than I was.

• Easiest - Take my original MAIN (the copy with your module) and delete all the other modules and import them for the newly created file.
• Not much worse - Take your newly created file (a copy of it) and import my forms, queries, and userforms (from the original MAIN File) and import them into it. Those files were not giving me the error, so it should work okay.

@adhoustonj - As far as I can tell, there is not a true VBA issue. I can hold down shift and get to the VBA window. I can also click Cancel 7 times and use the database. As I said previously, there is too much module code and too much of it is proprietary to post it here.

 

[Marshall Brooks]

@bastanu - okay odd - neither option worked. To recap:

The file that was created by your code opens with no errors, but it only has my main form (and my tables and queries), but none of my subforms or additional forms and none of my userforms.

I took a copy of my MAIN file and deleted all of my VBA Code and imported the VBA code from the newly created file and the error is back. (I didn't export from your file and re-import into my file, I just used the external data option.

Then I took a copy of your file and imported the forms, queries (didn't see they were there) and tables, and macros (which worked properly when I was creating the BLANK file) into the copy and saved it and the error is back ..

 

[adhoustonj]

• I tried copying the module that was giving me the error - this one: <Google: everythingaccess Extended Replacement Dlookup> to Notepad and then pasting it into the VBE window and got the error. I re-copied it from the website and pasted it into the database and got the error (The module is named ELookup-AllenBrowne - I think naming it the same as the function is a bad idea (and didn't do so) - I removed the dash and still got the error)

Understood - I wasn't sure if the troubled module was only the ELookup function. I tried to replicate by going to the AB website (http://allenbrowne.com/ser-42.html) and copying the module myself, and I get a VBA compile error message from it. (Compile error: Constant expression required)

You may have mentioned this already - but if you open the db holding shift, open the vba window, and click debug & then compile, does anything happen?

 

[bastanu]

Sounds like there might be some corruption in forms as well (what exactly do you mean by userforms, as that is Excel terminology, in Access we have forms).
Here is an updated module, I put some message breaks in the code so you could see where it stops; investigate the output folder and see which is the last object exported for the current group (forms, reports, etc.). Then look in the original file which objects comes next and try to export that as text manually and see if you get an error.

 

[Marshall Brooks]

@adhoustonj - I've lost track of which versions I have - doesn't help that Access updates the file date every time you open the file.

If I open the file that gives me the error message but works (not sure if that is the one that I did a decompile on and recompile on this morning), and click Cancel 7 times and then run a compile on it, there are no errors with the compile. The version from @bastanu does give me errors when I compile, but that is probably from missing references. (I've seen similar errors when I do a decompile and re-compile or when I try to rebuild to a blank database.

FWIW -I think I have the same code as the AllenBrowne page and THAT code gives me the error, no matter how I try to import it - I got to work one time by copying paragraphs from the code and bringing them over.

@bastanu - I haven't tried the new file yet, but ... There MAY be corruption in the forms as well, but if I ONLY import the forms into a blank database I don't get the error message. FWIW.

Access has Userforms but they aren't called that. I'm talking about things like input boxes, progress bars, etc. The terminology is confusing b/c: Forms are generally used to display data and controls. The database has 21 of them but 7 are primary ones. The code for these is in the VBE window as Microsoft Access Class Objects. What I am referring to as Userforms are on the VBE Window as Forms, but the code for them is with them and you can either view the code or view the object by clicking on them.

 

[Pat Hartman]

The reason you had trouble posting initially is because you had fewer than 10 posts. The site doesn't let you post links and certain types of attachments until you've passed that threshold to minimize our problem with bots.

Now, step back, move everything to an old folder and with what you have learned, start again. If no one has posted code that exports all objects for you automatically, ask, and I'll post some. If you get an error trying to export a certain form, that form is likely to be corrupted. Try to open it in design view and open the code module. If you can do that, copy the code to a text file and save it. Then delete the code from the form/report. Also uncheck the hasCode flag. save. C&R. Open the bad form and now try to export it. If that works, open the form in design view and add back the code module. Click on all the event procedures that have code to reconnect them. Compile the code and save the form and close it. Now try to export it again.

 

[Marshall Brooks]

@bastanu - tested with the new file and got the same end results. It only copied one of the forms, none of the macros, and none of what I am calling the userforms. It did give me more messages but just that is was saving items. The new db did open without errors. I then dragged the forms, macros and user forms over to the new database and saved it and the error is back again.

Again - the database seems to work properly with no errors after I click Cancel 7 times, so I'm not sure it is truly a corruption error, but I don't have a better explanation.

@Pat Hartman - I haven't seen code that exports all objects automatically - other than what @bastanu posted earlier. One odd thing - and maybe this will be helpful somehow. In the BLANK database, I had all my forms, tables, queries, and macros imported and was trying to import my modules. I had three of the modules imported and the file opened without errors. I then tried to import the Elookup module and the error appeared. Then I made of copy of the file that did not produce the error and added a new blank module to it and saved the module as ElookupAllenBrowne and the error appeared. Then a made a copy of the file that did not produce the error and added a new blank module to it and saved that module as Module2 and the error appeared.

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