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

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!

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


"tdcarm" wrote in message
...
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!

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


"tina" wrote:

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


"tdcarm" wrote in message
...
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!

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


"tdcarm" wrote in message
...
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


"tina" wrote:

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


"tdcarm" wrote in message
...
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!

That worked, many thanks!
Regards,
tdcarm

"tina" wrote:

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


"tdcarm" wrote in message
...
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


"tina" wrote:

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


"tdcarm" wrote in message
...
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!

you're welcome


"tdcarm" wrote in message
...
That worked, many thanks!
Regards,
tdcarm

"tina" wrote:

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


"tdcarm" wrote in message
...
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


"tina" wrote:

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


"tdcarm" wrote in message
...
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!