Running Access 2003 db in 2007, and a few forms don't work

[Paul Shapiro]

I have a database that's been updated from at least Access 97 (maybe Access
2, I can't remember anymore) and has been running fine in Access 2003 for
years. My client upgraded to Vista with Office 2007. Almost everything in
the database works fine, except 3 forms. Each of these forms has a tab
control. Two of the forms have subforms on some of the tabs, one doesn't.
When I try to open the forms in Access 2007 running on Vista in Virtual PC,
I get the error "There isn't enough memory to perform this operation. Close
unneeded programs and try the operation again." I can't open the form in
design mode either. Same error. All the subforms open fine, and one of the
problem forms doesn't even have subforms.

I found a fix that I don't understand. If I open the problem forms in Access
2003 and use Save As to save them under a new name, they work fine in Access
2007, without any other changes. I used the Application.SaveAsText to
extract the complete form representation for both the working forms and the
problem forms. The only differences are the GUID's assigned to the controls.
Nothing else.

Saving a new copy of the form would be an ok fix, except for one problem. I
use SourceSafe to keep the version control. If I save the forms under a new
name, I lose the version history. I've found the version history very
helpful when a new problem appears, so I'm reluctant to throw it away. I've
thought of workarounds, like keep this history and start a new project for
an Access 2007 version. That would work, but I'd prefer to avoid the
complexity if possible. Any suggestions?

Thanks, Paul Shapiro

 

[Allen Browne]

Paul, did you try a decompile?

Since each version of Access uses a different binary, and the binary is not
part of the text version, it would be the suspect here. My suggestion would
be to decompile in A2003, then compact (ensuring no code runs), and then
open in A2007 and compile. I don't use VSS, so I can't comment on that.

1. Using Access 2003, make sure the Name AutoCorrect boxes are unchecked
under:
Tools | Options | General | Name AutoCorrect
Then compact.

2. Decompile a copy of the database by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

3. Open Access 2003 (holding down the Shift key if you have any startup
code), and compact again.

Then open in Access 2007, compile, and compact again.

 

[Arvin Meyer [MVP]]

Starting a new project, would be the most effective way to handle your
SourceSafe version control. I use a table (appropriately named ChangeLog) in
each front-end database to record each change. I store the name of the
object, the type of change, a description of the change and a timestamp. The
description gives me far more information than I can get from SourceSafe.

 

[Paul Shapiro]

Thanks Allen. I forgot to add that I tried decompiling a few times, but
Access crashes. I re-created the db from Sourcesafe, made a copy, compacted
to remove the source control info without letting any startup code run, and
still the decompile crashes.
Paul

 

[Paul Shapiro]

I think you're right that starting a new project is probably the best I can
do. I use the SourceSafe checking comments to keep the change history you
describe. The one extra thing I like SourceSafe for is the ability to do a
Difference between any versions of an object. That shows every detail of the
changes. And SourceSafe is a convenient way to fix the occasional front-end
corruption. Just build a new copy from SourceSafe.
Paul

 

[Allen Browne]

Ah: that's a fairly good indication that the problem may be in that area.

Copy the code from the bad forms out to notepad. Then set the form's HasData
property to No. Save. Compact the database. Try the decompile. If it works,
you can paste the code back in again.

 

[Paul Shapiro]

Thank you Allen! Removing those 3 forms' modules let the decompile complete
successfully, so it seems like a great start. I have to run out now, so I'll
finish the code restoration and testing in A2007 later today.
THANK YOU.

 

[Paul Shapiro]

Thank you once again Allen. It works fine now. I opened each form, checking
it out of SourceSafe. I removed the code module, saved the form, re-created
the module and checked in the form. No more Access 2007 errors.

I didn't do the decompile on the SourceSafe copy of the database. I had done
that on another db copy, before the first test in Access 2007. Do you think
I need to decompile the SourceSafe version if everything seems to be working
correctly now?

 

[Paul Shapiro]

OK, I thought everything was clean, but it's not finished yet. I checked
everything out from SourceSafe. I removed the code modules from the 3
problematic forms that weren't opening in Access 2007 (out of memory error).
I decompiled the db, successfully, and then compacted/repaired. I re-created
the code modules for those 3 forms. I checked everything back into
SourceSafe.

At this point the db worked fine in Access 2007. Before distributing a db, I
like to create a new db from SourceSafe, so I did that. That db has the
original problem (out of memory error) with the same 3 forms. So it looks
like either checking objects into SourceSafe or generating objects from
SourceSafe is causing the problem.

Any more suggestions? Is this something that might be worth pursuing with
Microsoft Support? I get a free support incident via MSDN so it doesn't cost
money, I think.

 

[Allen Browne]

Paul, I don't use VSS, so can't really provide you with info on that.

Perhaps someone who uses it can comment.

 

[Paul Shapiro]

I wanted to post back in case anyone else is pursuing similar issues. This
is an issue with either checking a form into Visual SourceSafe or checking
it back out.

I had 3 out of 20 or so forms in an Access 2003 db that would not open in
Access 2007. All 3 were resolved by removing the hotkeys assigned to
controls and/or tab pages. One of them was a very simple form, based on a
single table with about 10 fields. All my forms have a find-a-row combo box
in the form header, with <Alt><F> assigned as the hotkey by setting the
label caption to "&Find:". Removing that ampersand fixed the problem. Some
of the other problem forms needed additional hotkeys removed. Every one of
my forms has the same setup. All but those 3 work fine with the hotkeys.

 

[John W. Vinson]

All 3 were resolved by removing the hotkeys assigned to
controls and/or tab pages.

VERRY interesting. Thanks Paul, I'll try to remember this!


John W. Vinson [MVP]

 

[Allen Browne]

Thanks for posting the solution, Paul.

I never would have guessed VSS would barf over the hotkeys.
Hopefully that will help someone else in future as well.