Make-table query freezing

[Guest]

I am using MS Access 2003 and am trying to make a back-end for an excel
pivot table. I have a vb form which I click a button and get the information
from my DB2 database(to make it so they have a 'view' of the data on their
own machine, so they do not have to constantly hit our main db). What I do is
create a passthrough query to DB2 and retrieve the information, then I dump
it into a table with my make table query. My question is does a make table
query rerun the passthrough query, when it is queried off of, or does it take
the last results the passthrough query had?
The pass-through query can return upto 600,000 rows, roughtly 200mb of data.
Why does it freeze, does the locked mde file that appears next to the Access
db have anything to do with it? How can I remove it?

Maybe I'm just going about this the wrong way...

It freezes whenever I open it in MS Access:
Here's the make table query.

SELECT STO_RGN_ID AS Region, AS_LST_NM AS LastName,
STO_OP_MKT_ID AS MarketID, STO_OP_MKT_DIR_NM_TX AS MarketDirector,
STO_OP_MKT_DSC_TX AS MarketDescription, UT_ID, STO_NM_TX AS StoreName,
P_CT_ID, PKY_ID, P_CT_DSC_TX,
FCL_YR_ID AS FiscalYear,
FCL_YR_ID0 AS FiscalYearID,
FCL_PER_ID AS FiscalYearPerID,
WK_END_DT_2 AS WeekEndDate, RET_DOLLARS AS ReturnDollars,
SALES_UNITS AS SalesUnits, DM_DOLLARS, WJXBFS1,
END_STO_INV_QT AS StoreInvQuantity,
END_STO_INV_COST AS StoreInventoryCost, WJXBFS2, WJXBFS3, WJXBFS4
INTO tblResult
FROM q_TDataPhoto;

 

[Guest]

My mistake on the locked "mde file" it's a "Microsoft Access Record Locking
Information" file. My question still stands on if that is what is causing my
troubles.


Any help will be greatly appreciated!

- Dave Hanson

 

[Phil Smith]

If you open an access DB, you get an MDE file.As long as the system has
rights to manipulate tha tfile, it is not your problem.
Are you sure it is freezing, and not just taking a whole lot of time?

Put a TOP command in the query, so it will only return a hundred
records. See what happens. Then up the top and see if still runs, and
try to get an idea of how long it takes, and figure out how long it
should take to run through all of the data.

 

[Guest]

Well, I don't think it's freezing when I run it; however, it took 938
seconds. The problem is when I go to edit the query in design view, should it
take 15 min to open? It's taking a long time to open. It's a lengthy query.
I don't know, thanks for any help you can give! Does Access 2003 run the
query before edit it in design view?

- Dave

 

[Guest]

Go to Tools, Options, General Tab and turn off Name Autocorrect on all of
your databases. IMHO it's a piece of do-do and the cause of many problems.
While there turn off Compact on Close if enabled. You'll be glad that you did.

 

[Guest]

That did the trick, thanks Jerry and Phil for you help.

I greatly appreciate it

- David Hanson