Problems with Access 2003

[Geoff Goodacre]

Two problems: the first is more serious as I haven't got even a workaround at
the moment.

1. A user is unable to open any report in preview or design mode (also
probably print mode but I haven't tried) This happens for either an mdb file
or it's equivalent mde. Any attempt to edit the VBA code in the mdb version
crashes Access immediately. Tried two different applications, same result.
Other users using the same front end don't have the problem, although they
have Access 2003 SP2 also. The third party support company re-installed
Office 2003 SP2, same result.

2. At the same company the PC I use to get remote access has Access 2003
SP3 on it. Don't know whether this is causing the problem but it's
suspicious to me that when I do development on that PC the size of the front
end mdb file keeps creeping up, e.g. +50% after a couple of minor changes,
and it won't then compact. This happens to two different applications based
on files that haven't given trouble previously. Also, one of the apps has
collapsed in a heap with I think a couple of corrupt modules when I'm trying
to implement very minor mods. My present workaround strategy is to copy the
files to my own network and develop in Access XP which works and doesn't
cause the size increase problem, but has anyone else encountered issues with
2003?

 

[Allen Browne]

Geoff, the first thing to check would be whether that machine has a problem
printer driver. Since Access uses the driver to calculate the layout of the
report it is unable to do if either no printer is installed, or the printer
driver is inadequately installed (particularly with network printers), or
there is a bug in the printer driver (which can lead to crashes.) One way to
test that would be to try install entirely different printer driver and make
it the default.

Having done that, the next thing would be to try decompiling the
application. Particularly your comment about not being able to reduce the
file size by compacting suggests that some kind of bad binary is remaining
in the file. To do this, you will want to ensure you are logged in as a
local computer administrator (so there's no permission issues.) You will
also want to ensure the Name AutoCorrect options are turned off. There's a
standard set of recovery steps below to do this.

There are a few issues with A2003 on SP3. The ones I am aware of are listed
at the top of this page:
http://allenbrowne.com/tips.html

If none of this works, you could try reinstalling Office on this machine,
and apply service packs only as far as SP2.

Okay: here's the standard recovery steps:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile 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"

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

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Still in the code window, choose Options on the Tools menu. On the
General tab, make sure Error Trapping is set to:
Break on Unhandled Errors
and the Compile on Demand is unchecked.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
the code syntax is compilable, and the VBA options are set to show errors
and avoid this kind of corruption.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[Geoff Goodacre]

Hi Allen,

This looks very helpful thank you! I will try what you suggest when I can
get access to the PCs in question, and post feedback in this thread.