Access 2003 Database crashing

[Nadihaha]

Why does my access 2003 database keep crashing when I try to create a form?
The table I'm trying to create it from has 2 lookup fields in it only 1 of
which has data in it at the moment? Could it be that there's now too much
data? (doesn't seem real likely)

 

[Tom van Stiphout]

On Tue, 30 Jun 2009 19:37:03 -0700, Nadihaha

Not unless you have 2GB of data, which is the maximum.

Much more likely you have some form of corruption. The most reliable
way I know to get rid of it is to export all objects to text files
using the (officially undocumented, but widely documented)
Application.SaveAsText method. Then create a new database and import
all objects using .LoadFromText.
Of course you have already compacted the db, and installed all service
packs, right?

-Tom.
Microsoft Access MVP

 

[Tom Wickerath]

Hi Nadihaha,

It sounds like your database has some corruption in it. Try these steps to
see if it resolves the issue for you:

Create a brand new database and immediately disable the Name Autocorrect
feature (see: http://allenbrowne.com/bug-03.html for reasons why you want to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. When importing
local tables, it is a good idea to click on the Options >> button and check
the options to import relationships (unless you suspect corruption in a
relationship), along with Menu/Toolbars and Import/Export Specs. If any of
the objects in the source DB are hidden, you'll need to first unhide them.
While Access will allow you to import all objects in one operation, the
experts at FMS, Inc. (a Microsoft Partner), have stated that it is best to
import objects one group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch using File | Get External Data |
Link tables... Access can cache a lot of information about linked tables,
which may no longer be valid, so it's always best to recreate the linked
tables from scratch. You will need to set the checked references to match the
source database, along with any startup options set under Tools > Startup.
Going through this process often times solves corruption problems, because
you get a new set of the hidden system tables (the tables whose names start
with "MSYS"). These system tables are updated appropriately as you import
objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling Name Autocorrect, importing all objects one group at
a time, re-establishing any linked tables, setting startup options, and
setting references to match the source DB is usually a fairly quick
procedure. When you are in the Visual Basic Editor, in order to check that
the references match the source DB, you should do a Debug > Compile
ProjectName as well.


By the way, Lookup fields defined at the table (or query) level art thy
creation of the evil one. You might want to reconsider creating lookup
fields. See the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Nadihaha]

Thanks Tom,

I have figured out that the Lookup Box didn't quite work as I had envisaged
but hadn't turned them all off yet, I don't suppose doing so will have
remedied my problem.

When you say "import" is at as simple as finding an import button?

Thnks

 

[Tom Wickerath]

Hi Nadihaha,

I really do not think there is any way that having Lookup fields could cause
this crash when creating a new form, however, it is a good idea to get rid of
them in any case. Set the Display Control back to a text box to do so.

When you say "import" is at as simple as finding an import button?

From the menu:
File | Get External Data | Import

Use this command from within the newly created database, to import all
objects from your source database.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Nadihaha]

Thankyou Sooooooo Much!!!! It worked Yippee!!

I was about ready to give up on the whole concept and throw my computer
through our 3rd floor Window.

Your Help is greatly Appreciated!! Now to fix all the other things that
won't do what I want!!

 

[Tom Wickerath]

Glad to hear of your success!

By the way, the fix I suggested can be found on the bottom of page 3, of a
Word document that I call "Access Links". You are welcome to download a copy
from my web site, shown below. This document is filled with lots of good
information. If you decide to take a look, concentrate for the present time
on the material covered in the first four pages. Just skim the rest of the
material to get a general idea of what it includes.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________