Access 2007 Hits 2G limit when running MakeTable query

Do you compact/repair your database on a regular basis?  This can significantly reduce the overall size.

Are you deleting any temporary tables/data before creating new ones?

What are your select and make table queries (what is the SQL?) ?

1.  Compacted and repaired - 900mb
2. Ran make table query - tables isn't even made as the query errors out and the database size hits 2Gb

Not deleting any temporary tables before creating new one as it does not exist yet (would after MakeTable query is run,

SQLof Select Query:
SELECT IIf([Lawson_MAINVDTL].[Company]=1000,"RHMJOH","") AS RHMID, IIf([LAWSON_MAINVDTL].[Company]=1000,"LHMJOH","") AS LHMID, "" AS CostCenter, LAWSON_MAINVDTL.AGREEMENT_REF AS ContractNumber, LAWSON_MAINVDTL.INVOICE AS TransactionIDNumber, LAWSON_POLINE.LINE_NBR AS TransactionLIneNumber, LAWSON_APINVOICE.INVOICE_DTE AS TransactionDate, LAWSON_APINVOICE.INVOICE_TYPE, LAWSON_MAINVDTL.SUFFIX, LAWSON_PURCHORDER.PO_NUMBER, LAWSON_APINVOICE.BASE_INV_AMT, LAWSON_APDISTRIB.ORIG_BASE_AMT, LAWSON_PURCHORDER.PO_DATE, LAWSON_PURCHORDER.TOT_TAXBL_AMT, LAWSON_MAINVDTL.API_OBJ_ID, LAWSON_APDISTRIB.DIS_ACCOUNT, LAWSON_APDISTRIB.PO_AOC_CODE, LAWSON_APINVOICE.AOC_ALLOW_AMT, LAWSON_APDISTRIB.PO_AOC_CODE, LAWSON_APINVOICE.TRAN_TAX_AMT, LAWSON_MAINVDTL.ENT_UNIT_CST, LAWSON_MAINVDTL.ENTERED_QTY, LAWSON_APINVOICE.TRAN_TAX_AMT, LAWSON_MAINVDTL.PO_CODE, LAWSON_MAINVDTL.LINE_NBR, LAWSON_MAINVDTL.SEQ_NBR, Format(Date(),"yyyymmdd") AS DateExtracted, Format(Time(),"hhnnss") AS TimeExtracted, LAWSON_MAINVDTL.ITEM_TYPE, LAWSON_MAINVDTL.ITEM, LAWSON_MAINVDTL.ITEM_DESC
FROM (((LAWSON_MAINVDTL INNER JOIN LAWSON_APINVOICE ON (LAWSON_MAINVDTL.SUFFIX = LAWSON_APINVOICE.SUFFIX) AND (LAWSON_MAINVDTL.INVOICE = LAWSON_APINVOICE.INVOICE) AND (LAWSON_MAINVDTL.VENDOR = LAWSON_APINVOICE.VENDOR) AND (LAWSON_MAINVDTL.COMPANY = LAWSON_APINVOICE.COMPANY)) INNER JOIN LAWSON_APDISTRIB ON LAWSON_MAINVDTL.API_OBJ_ID = LAWSON_APDISTRIB.API_OBJ_ID) INNER JOIN LAWSON_PURCHORDER ON LAWSON_APINVOICE.PO_NUMBER = LAWSON_PURCHORDER.PO_NUMBER) INNER JOIN LAWSON_POLINE ON (LAWSON_PURCHORDER.PO_CODE = LAWSON_POLINE.PO_CODE) AND (LAWSON_PURCHORDER.PO_RELEASE = LAWSON_POLINE.PO_RELEASE) AND (LAWSON_PURCHORDER.PO_NUMBER = LAWSON_POLINE.PO_NUMBER) AND (LAWSON_PURCHORDER.COMPANY = LAWSON_POLINE.COMPANY)
WHERE (((LAWSON_APINVOICE.INVOICE_DTE) Between #1/1/2013# And #1/31/2013#))
ORDER BY LAWSON_APINVOICE.INVOICE_DTE;


SQL of MakeTable:
SELECT qryInvoiceDetails.RHMID, qryInvoiceDetails.LHMID, qryInvoiceDetails.CostCenter, qryInvoiceDetails.ContractNumber, qryInvoiceDetails.TransactionIDNumber, qryInvoiceDetails.TransactionLIneNumber, qryInvoiceDetails.TransactionDate, qryInvoiceDetails.INVOICE_TYPE, qryInvoiceDetails.SUFFIX, qryInvoiceDetails.PO_NUMBER, qryInvoiceDetails.BASE_INV_AMT, qryInvoiceDetails.ORIG_BASE_AMT, qryInvoiceDetails.PO_DATE, qryInvoiceDetails.TOT_TAXBL_AMT, qryInvoiceDetails.API_OBJ_ID, qryInvoiceDetails.DIS_ACCOUNT, qryInvoiceDetails.AOC_ALLOW_AMT, qryInvoiceDetails.PO_AOC_CODE, qryInvoiceDetails.ENT_UNIT_CST, qryInvoiceDetails.ENTERED_QTY, qryInvoiceDetails.TRAN_TAX_AMT, qryInvoiceDetails.PO_CODE, qryInvoiceDetails.LINE_NBR, qryInvoiceDetails.SEQ_NBR, qryInvoiceDetails.DateExtracted, qryInvoiceDetails.TimeExtracted, qryInvoiceDetails.ITEM_TYPE, qryInvoiceDetails.ITEM, qryInvoiceDetails.ITEM_DESC INTO tblInvoiceDetails
FROM qryInvoiceDetails;

Does Selecting directly from the Lawson database into the target table make any difference?

(ie: Write the qryInvoiceDetails to select directly into tblInvoiceDetails instead of using it as an 'intermediate step')

If the amount of data is simply too large, you can also try creating a separate empty Access database for your make-table, and use the External Data Wizard to link it to your original Access Database.

Another consideration - can you use the External Data wizard to link to the Lawson Database for your needs, rather than actually importing the data?

You might take a look at my article on temp tables,  there is code in that article which will create the temp table in an external Access mdb/accdb and then link that new table back into your application.   I cannot seem to copy/paste the hyperlink from my iPad, so search EE on "temp tables" and indicate you are looking for articles.

The pure size of the data is the issue.  We are using linked tables directly to the database but need to use the query to post the data to a table for further formatting/processing, etc.  

I've broken this one query up into several other queries and making several temporary tables.

In this particular case using an outside program to get the data solved this issue with the 2GB limit.