Access Crashes When Rt Click Build in Query Design View

[mnjkahn via AccessMonster.com]

I'm running Access 2003, modifying a query that has over 45 fields. When I
right click on the field name in Query Design View, and then click Build,
Access crashes before the Build window appears. It doesn't happen every time,
and using the Zoom window works fine. It appears that it only happens when I
want to modify an existing expression. This continues to happen even after
the database is repaired and reopened. Anyone have any suggestions?

--
Message posted via http://www.accessmonster.com

[Allen Browne]

Not sure what's causing the problem, but here's a sequence that addresses a
number of issues.

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. Open your query in SQL View.
Copy the query statement out to (say) Notepad.
Close. Then delete the query.
Compact again.
Create a new query.
Paste the query statement from Notepad into SQL View.
Switch to Design view, and tidy it up.

BTW, after setting a property, *always* press Enter before you do anything
else. Do not leave the cursor in the same property and try to test the query
or close the properties box, or ...

Let us know how you went.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mnjkahn via AccessMonster.com" <u27308@uwewrote in message
news:7156d89e81930@uwe...

I'm running Access 2003, modifying a query that has over 45 fields. When
I
right click on the field name in Query Design View, and then click Build,
Access crashes before the Build window appears. It doesn't happen every
time,
and using the Zoom window works fine. It appears that it only happens
when I
want to modify an existing expression. This continues to happen even
after
the database is repaired and reopened. Anyone have any suggestions?

[mnjkahn via AccessMonster.com]

Sorry, Allen, but it didn't work. I thought you had something there for a
moment. When I checked the references I noticed that the ADO library was
Version 2.1. Since I was using Access 2003, and working with an Access 2002-
2003 format database, I changed the reference to 2.5. It worked a few times,
then crashed just like it always had. Now, all I have to do is click Build
on a field, modify it or just select and copy, close the Build, hit enter,
then click on another field and then Build, and I get a crash every time.
And it will happen on more than one query in the database. I have even
created a new database, copied over the linked tables and queries, and had it
crash on the Build.
The query pulls data through 3 queries with data originating from 3 tables
linked to Excel spreadsheets, one of which contains over 75,000 records. I
wouldn't think that either the size, or the fact that I'm using linked Excel
spreadsheets would make a difference, at least it hasn't under Access 2002.
However, at this point, I'm at a loss to explain it.

Here's a little more info, for what its worth:
MS Access 2003(11.6566.8122) SP2
References:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library
OLE Automation

Allen Browne wrote:

>Not sure what's causing the problem, but here's a sequence that addresses a
number of issues.

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. Open your query in SQL View.
Copy the query statement out to (say) Notepad.
Close. Then delete the query.
Compact again.
Create a new query.
Paste the query statement from Notepad into SQL View.
Switch to Design view, and tidy it up.

BTW, after setting a property, *always* press Enter before you do anything
else. Do not leave the cursor in the same property and try to test the query
or close the properties box, or ...

Let us know how you went.

>I'm running Access 2003, modifying a query that has over 45 fields. When
I

[quoted text clipped - 6 lines]

>after
the database is repaired and reopened. Anyone have any suggestions?

--
Message posted via http://www.accessmonster.com

[Allen Browne]

Well, I'm not sure what else to suggest.

Since you disabled Name AutoCorrect and decompiled and compact/repaired and
fixed references and now have fully compiled code and then recreated the
query, I'm not sure what alternatives might help.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mnjkahn via AccessMonster.com" <u27308@uwewrote in message
news:718915f9d099a@uwe...

Sorry, Allen, but it didn't work. I thought you had something there for a
moment. When I checked the references I noticed that the ADO library was
Version 2.1. Since I was using Access 2003, and working with an Access
2002-
2003 format database, I changed the reference to 2.5. It worked a few
times,
then crashed just like it always had. Now, all I have to do is click
Build
on a field, modify it or just select and copy, close the Build, hit enter,
then click on another field and then Build, and I get a crash every time.
And it will happen on more than one query in the database. I have even
created a new database, copied over the linked tables and queries, and had
it
crash on the Build.
The query pulls data through 3 queries with data originating from 3 tables
linked to Excel spreadsheets, one of which contains over 75,000 records.
I
wouldn't think that either the size, or the fact that I'm using linked
Excel
spreadsheets would make a difference, at least it hasn't under Access
2002.
However, at this point, I'm at a loss to explain it.

Here's a little more info, for what its worth:
MS Access 2003(11.6566.8122) SP2
References:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library
OLE Automation

Allen Browne wrote:

>>Not sure what's causing the problem, but here's a sequence that addresses
a
number of issues.

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. Open your query in SQL View.
Copy the query statement out to (say) Notepad.
Close. Then delete the query.
Compact again.
Create a new query.
Paste the query statement from Notepad into SQL View.
Switch to Design view, and tidy it up.

BTW, after setting a property, *always* press Enter before you do anything
else. Do not leave the cursor in the same property and try to test the
query
or close the properties box, or ...

Let us know how you went.

>>I'm running Access 2003, modifying a query that has over 45 fields.
When
I

[quoted text clipped - 6 lines]

>>after
the database is repaired and reopened. Anyone have any suggestions?