Make table query won't run but Select will

[apex77]

I am using Access 2007 and have created a query using several linked tables
from a different database using ODBC connection. When I run the query as a
SELECT query the records are returned in short fashion (a few seconds). Only
about 200 records in all. But when I turn the query into a MAKE TABLE query,
it hangs and hangs and never completes. I can copy the data from the select
query and paste into the empty table, but that is very unprofessional. Any
ideas would be great thanks much.

 

[Ken Snell MVP]

Show us the SQL statements of the SELECT query, and of the MAKE TABLE query.

 

[apex77]

The SQL is:
SELECT AR_InvoiceHistoryDetail.ItemCode, tblSalesCode.ProductType,
AR_InvoiceHistoryDetail.ItemCodeDesc, AR_Customer.UDF_INDUSTRY,
AR_Customer.CustomerNo, AR_Customer.CustomerName,
AR_Customer.UDF_MANAGEMENTCO, AR_InvoiceHistoryDetail.UDF_SALES_LEAD,
AR_InvoiceHistoryDetail.UDF_SALES_ADVANCE,
AR_InvoiceHistoryDetail.UDF_SALES_CLOSE, AR_InvoiceHistoryHeader.InvoiceNo,
AR_InvoiceHistoryHeader.InvoiceDate, AR_InvoiceHistoryDetail.ExtensionAmt,
AR_InvoiceHistoryDetail.UDF_REINFORCEMENT_AMT_MONTHLY,
[UDF_REINFORCEMENT_AMT_MONTHLY]*11 AS AnnualReinf,
AR_InvoiceHistoryDetail.QuantityShipped,
AR_InvoiceHistoryDetail.UDF_INSTALL_DATE,
AR_InvoiceHistoryDetail.CommentText, " " AS [Account Manager] INTO
tblSalesdatadraft_hold
FROM tblSalesCode RIGHT JOIN ((AR_InvoiceHistoryDetail INNER JOIN
AR_InvoiceHistoryHeader ON AR_InvoiceHistoryDetail.InvoiceNo =
AR_InvoiceHistoryHeader.InvoiceNo) INNER JOIN AR_Customer ON
AR_InvoiceHistoryHeader.CustomerNo = AR_Customer.CustomerNo) ON
tblSalesCode.ItemCode = AR_InvoiceHistoryDetail.ItemCode
WHERE (((AR_InvoiceHistoryDetail.ItemCode) Not Like "/C") AND
((AR_InvoiceHistoryDetail.UDF_INSTALL_DATE) Between #3/1/2009# And
#3/31/2009#));

Thanks

 

[Ken Snell MVP]

Try saving the SELECT query as a saved query. Then create new query as the
maketable query and use that saved query as the data source.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

The SQL is:
SELECT AR_InvoiceHistoryDetail.ItemCode, tblSalesCode.ProductType,
AR_InvoiceHistoryDetail.ItemCodeDesc, AR_Customer.UDF_INDUSTRY,
AR_Customer.CustomerNo, AR_Customer.CustomerName,
AR_Customer.UDF_MANAGEMENTCO, AR_InvoiceHistoryDetail.UDF_SALES_LEAD,
AR_InvoiceHistoryDetail.UDF_SALES_ADVANCE,
AR_InvoiceHistoryDetail.UDF_SALES_CLOSE,
AR_InvoiceHistoryHeader.InvoiceNo,
AR_InvoiceHistoryHeader.InvoiceDate, AR_InvoiceHistoryDetail.ExtensionAmt,
AR_InvoiceHistoryDetail.UDF_REINFORCEMENT_AMT_MONTHLY,
[UDF_REINFORCEMENT_AMT_MONTHLY]*11 AS AnnualReinf,
AR_InvoiceHistoryDetail.QuantityShipped,
AR_InvoiceHistoryDetail.UDF_INSTALL_DATE,
AR_InvoiceHistoryDetail.CommentText, " " AS [Account Manager] INTO
tblSalesdatadraft_hold
FROM tblSalesCode RIGHT JOIN ((AR_InvoiceHistoryDetail INNER JOIN
AR_InvoiceHistoryHeader ON AR_InvoiceHistoryDetail.InvoiceNo =
AR_InvoiceHistoryHeader.InvoiceNo) INNER JOIN AR_Customer ON
AR_InvoiceHistoryHeader.CustomerNo = AR_Customer.CustomerNo) ON
tblSalesCode.ItemCode = AR_InvoiceHistoryDetail.ItemCode
WHERE (((AR_InvoiceHistoryDetail.ItemCode) Not Like "/C") AND
((AR_InvoiceHistoryDetail.UDF_INSTALL_DATE) Between #3/1/2009# And
#3/31/2009#));

Thanks

Show us the SQL statements of the SELECT query, and of the MAKE TABLE
query.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/