Why does a database close when I open design of a make table quer.

T

tdcarm

I have an existing database used every 3 months for the last 4-5 years.
Today, when I tried to open an existing make table query, the entire database
closes. Everytime I try. I have compacted and repaired the database after
each use with no errors. Today, I created a blank database, copied everything
from the old database to the new database, and the same problem occurred
again. I could create a new version of the query, but can not open to see the
design. Any ideas? Thanks!
 
T

tina

hmm, you could try this:

in the database window, go to the Modules tab and click the New option. a
new module will open in the VBE window. paste the following code into the
window, below the lines

Option Compare Database
Option Explicit
(note: the second line may not be present in the window)

paste code:

Public Sub GetSQL()
Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("MyQueryName")
Debug.Print qry.SQL
End Sub

replace MyQueryName with the actual name of your make-table query, staying
inside the set of double quotes. press Ctrl+G to open the Immediate window.
then put your cursor anywhere between the Public Sub and End Sub lines, and
press F5.

the SQL statement from your make-table query should print to the Immediate
window. copy the entire statement by highlighting it and pressing Ctrl+C.

close the VBE window, which will take you back to the database window. click
the Queries tab. open a new query to Design View. close the Show Table
dialog without choosing anything. from the query's toolbar, click the SQL
button on the extreme left end. in the SQL pane, click Ctrl+V to paste the
SQL statment that you copied from the Immediate window. (you'll overwrite
the existing "SELECT;" showing in the SQL pane, which is correct.)

btw, if you're using A2000 or newer, recommend you turn OFF the Name
AutoCorrect option, if you haven't already. to do so: from the database
window's menu bar, click Tools | Options | General tab. UNcheck the box next
to Track name AutoCorrect info. click OK, then compact your database.

hth
 
T

tdcarm

Thanks Tina,

When I pressed the F5 with the cursor in the sub, I received a "Compile
error: User-defined type not defined" message, and the SQL statement did not
appear at the bottom.

Regards,
tdcarm
 
T

tina

okay, i'm guessing that you're using Access 2000. open the db to the
database window and go to the Modules tab again. hopefully you saved the
module you created. just double click the name to open it in the VBE window.
otherwise, click the New option. in the VBE window, on the menu bar click
Tools | References. scroll down the list until you see a Microsoft DAO
entry, and checkmark the box beside it, then click OK. if there is more than
one DAO entry, choose the one with the highest number (such as 3.6).

try running the sub again with F5, same as instructed before.

hth
 
T

tdcarm

That worked, many thanks!
Regards,
tdcarm

tina said:
okay, i'm guessing that you're using Access 2000. open the db to the
database window and go to the Modules tab again. hopefully you saved the
module you created. just double click the name to open it in the VBE window.
otherwise, click the New option. in the VBE window, on the menu bar click
Tools | References. scroll down the list until you see a Microsoft DAO
entry, and checkmark the box beside it, then click OK. if there is more than
one DAO entry, choose the one with the highest number (such as 3.6).

try running the sub again with F5, same as instructed before.

hth
Click to expand...
 
Top