Link to home
Start Free TrialLog in
Avatar of DanielT
DanielTFlag for Canada

asked on

Cannot View Code

I have a database that was developed in Access 2003 and had an issue when it was implemented on an Access 2002 system in a front end (FE) / back end (BE) arrangement. There is no password set on the code yet.

However, when I run it in Access 2002 and then try to edit the VBA code, it returns an unpleasant "Cannot View Code" and goes no further. It will not open the editor or, of course, any code. This same FE file on at least two other systems (albeit Access 2003 versions) can quite comfortably open the VBA editor and edit code.

Why would this occur? At first I thought it might be some references but of course I'd need to get INTO the editor to change or add. The strange thing is - everything runs OK on the 2002 version - code and data access is fine!?

Has anyone seen this problem - or have any ideas?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Can you get into the design view of Forms, Reports etc., and any code behind them?

mx
Avatar of DanielT

ASKER

Well that was a simple angle! I thought I had tried this but could not remember for sure.

I tried and the form edit mode was accessible so I tried to get into code. Since the issue above I also had a strange problem that caused me to have to replace the FE file from another PC. That file was protected and it asked me for the password, then opened fine! I had tried a number of times with the old FE MDB. Anyway, closed down, reopened and tried access to VBA editor. Once again, it acted as it should.

I did not nothing other than try what you suggested so - either going in that way helped after the initial successful open or the file subsituted made a difference. Only problem with the file sub was the original files worked on their respective machines without errors. Code even compared as identical for the over 4,000 lines!

I'd like to leave this open as I will need to drop in a new FE shortly and don't quite trust it yet <grin>.  Very strange.


For good measure ... do a Compact and Repair, although C&R doesn't really deal with code.

In fact, I would do a Decompile on this FE.  Do you need the routine?

mx
There is a weird problem involving signing code that causes this type of error when switching versions.  I have experienced it with Access 2002/2003 databases.  The sign is that you get a "Project Is Unviewable" error message.  Is that what you get?  There is a KB article on this topic, which I found to work (mostly).  Here is a link to it:
http://support.microsoft.com/kb/823225
Avatar of DanielT

ASKER

DatabaseMX
Sorry - may misunderstand your suggestion but FE is not compiled. A decompile would not apply, right? DB is compacted on each exit.

Helen
I think I saw something about this and the project was indeed signed initially. I thought I went through the procedure to remove OK but - at the time it did not help. Further work has been done since then and, perhaps, this is why it was OK at last check. Will be "installing" a new version soon (need to resolve another issue first) and will then see if it persists.
"A decompile would not apply, right?"
Yes, it would apply ... unless, your mdb has zero VBA code?

"DB is compacted on each exit."
For good measure, try a manual C&R to be *sure* it's successful.

mx
Avatar of DanielT

ASKER

MX

Unfortunately. a manual C&R did not assist.

Sorry - had a look - must have missed - how do you decompile?
(yes - there is a decent amount of code)

A **DeCompile** may help here ...

But first, if you have not already:
Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
Avatar of DanielT

ASKER

Thanks MX.
Did not think of looking at command line options.

Very clear but I need to clarify...
Are you saying this should all be done on the problem system or on the development system before reimplementing on the problem system?
"Are you saying this should all be done on the problem system or on the development system before reimplementing on the problem system?"

Well, both really. Start with the problem system first, since that is where the issue is.  Before I deploy any app, I will decompile prior to doing so, as well as in other various situations.  However, the general rule with Decompile is:  If It Works Don't Fix It.

Decompile has saved many a corrupted databases that would otherwise be trashed.

And remember this: ALWAYS make a backup of your mdb first before executing Decompile ... a 3 second operation in the folder containing the mdb ... Select MDB, Control C, Control V  :-)

mx
Avatar of DanielT

ASKER

OK. Appreciate the comments.

I think I will start on the dev system then, since if that resolves it, it will make future implementations go smoother if I do not have to perform the above process onsite. Then I can try again when I am next in at the client's again.

I haven't seen the exact message '"Cannot View Code" per se before.  But at this point, Decompile is the only thing I can think of ...

mx
Avatar of DanielT

ASKER

Well - was able to try the decompile quickly. All was well with a minor edit of MSACCESS path to be "C:\Program Files\Microsoft Office\OFFICE11\Msaccess.exe".

But at step 6b, the option to compile is greyed out but it does indicate the name of the project. Did not matter whether I started Access with Shift or not at that point.


Avatar of DanielT

ASKER

By the way - Compile on demand and Background Compile are checked...
Sooo ... the Decompile actually completed ... but, Compile is grayed out ??

mx
Well, I never have Background Compile checked, but doubt that would make a difference - but I supposed try just in case.

mx
Avatar of DanielT

ASKER

Was already on it.

Tried unchecking and going back through decompile process. Nope.
Turned off the startup form so it would not autorun and tried again. Still not available.

If this is valid from the immediate window...
? Application.IsCompiled
Returns True

Tried also starting Access in Decompile mode, then navigating to the MDB. No change.

Does Access actually SHOW that you are in decompile mode?
I see nothing to tell me it is.

"If this is valid from the immediate window..."
Yes, definitely.

"Does Access actually SHOW that you are in decompile mode?"
Well, once you actually execute the Decompile step (with database closed) ... once completed, the database will be opened ... at which point Compile should *not* be grayed out.  So ... assuming the decompile IS actually occurring, well ... this is very weird.

mx
Avatar of DanielT

ASKER

Thx MX

I saw that the database opened after the decompile step which is why I disabled the startup NAV Form (figured it would have to compile to run and may complicate things).

Anyway, I am going to do some further checks to see what I can figure out. But I have tried to decompile so many times I'm surprised the file size isn't negative (just kidding!).

Strange thing is that the code runs OK on Access 2003.

Wonder what would happen if I tried a decompile in Access 2007, assuming switch is still supported?  (On a copy of course)

Anyway - if you have any ideas let me know.
I will advise if I find something further...
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DanielT

ASKER

Maybe I've inadvertently caused some confusion...

A few posts back I mentioned I will start this process with the development system (Access 2003) - on that system I CAN edit the code. It is the decompile, recompile that I cannot seem to do. The "problem" system is at another location.

Mention this because of your last post - "and see if you can now edit the code." Not needing to do (or being familiar with) a decompile is it a mute point if the code is accessible?

ok.

The problem system is an A2002 system, right?  And with the A2003 app on that system, you cannot view the code?

mx
Avatar of DanielT

ASKER

Yes - that is correct. But the '02 system is remote at a client's site. I need to figure this out and be prepared when I am onsite for it to (hopefully) work!

I have been working with my dev system which is '03.

Have some interim news however, I created a new DB and imported all tables, forms, code etc and the filesize jumped to approx 7x the size. Upon entering VBE I have the compile option as selectable... (again this is on the Acess 2003 Dev system).
7X ... that's odd, because I would expect it to be the same, or smaller ... assuming the mdb where the import came from was just C&R'd ...

C&R is automatic when importing. But for kicks ... C&R this new 7x mdb and see what happens.

mx
Avatar of DanielT

ASKER

After C&R, back to about same size (12MB) AND I have access to the Compile menu option...
"AND I have access to the Compile menu option..."
But, you are on your A2003 system and that is normal, right?

mx
Avatar of DanielT

ASKER

Yes and No.

Yes because I am on the 2003 system.
No becasue I did not have access to the compile option - now I do.

I thought that was only on the A2002 system ?

mx
Avatar of DanielT

ASKER

in Access 2002...
- "project is not viewable" error
- NO Access to code, references

in Access 2003...
- COULD access and edit code
- could NOT access compile; that's where things got bogged down


ok ... I get it. Soooo, it's fixed then?

mx
Avatar of DanielT

ASKER

Hopefully it is... but dunno yet.

I will work with this NEW DB file moving forward and see if it resolves the site issue (Access 2002) when I install FE/BE setup. Need some time to get back in there....

Now that I have the compile option - I presume that I should now go through the last two steps of your previous post for compiling and a final C&R....
Final steps ... well, yes in that it should shrink the size after Compile.
Avatar of DanielT

ASKER

Importing the entire DB into a new DB allowed access to the compile option and when this 'new' DB was taken onsite the "cannot view code" error was no longer an issue!
"Grading Comment:
Importing the entire DB into a new DB allowed access to the compile option and when this 'new' DB was taken onsite the "cannot view code" error was no longer an issue! "

COOL!

mx
Avatar of DanielT

ASKER

Hopefully it stays that way! Today was the first time I was able to access code so I guess it was corrupted in some way that did not bother AC2003 but AC2002 did not like. Wierd.

Thanks "MX".