System Resources Exceeded - Passthrough Query and VBA in Ms Access

Question

I have written the below code which imports an excel file into an Access database, takes all the purchase order numbers from it, throws them into the criteria of a query, passes this criteria to a passthrough query, and then executes the query. All works fine if the number of POs is limited, but when there are many, the system returns the error System Resources Exceeded when passing the criteria to the query definition. The funny thing is that the actual query criteria (no matter how many POs) does however get passed to the query, but the script execution halts.

In consideration that I cannot create tables in the underlying Oracle database (ideally I could create a table with the PO numbers and do a join), any ideas on what I could do to improve my script and avoid this error?

Thank you.

Public Function ImportGoodPOs()
On Error GoTo ImportGoodPOs_Err

'This function imports the spreadsheet with te POs
Dim tbl As String, fd As FileDialog, fn As String, qry As String, tbl1 As String, tbl2 As String, db As DAO.Database, rst As DAO.Recordset, qString As String, qdExtData As QueryDef, qry2 As String, pos As String, sql As String
tbl = "tbl_TempPos"
tbl2 = "tbl_PoNumbers"
qry = "qry_GoodPosWithVendorDetails"
qry2 = "qry_GoodsPosWithVendorDetailsPQ"
tbl1 = "tbl_GoodPosWithVendorDetails"
Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

MsgBox "Browse for the PO file to import...", vbOKOnly, "Attention!"

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        fn = fd.SelectedItems(1)
    End If
Else
    'Exit code if no file is selected
    End
End If

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, tbl
DoCmd.TransferSpreadsheet acImport, , tbl, fn, -1

' setting a dummy query definition to avoid future errors
sql = "SELECT APPS.PO_HEADERS_ALL.SEGMENT1 FROM APPS.PO_HEADERS_ALL WHERE APPS.PO_HEADERS_ALL.SEGMENT1 = '201410377'"
CurrentDb.QueryDefs(qry2).sql = sql

Set rst = CurrentDb.OpenRecordset(tbl)
sql = "SELECT DISTINCT " & tbl & ".[PO Number]"
sql = sql & "INTO " & tbl2 & " "
sql = sql & "FROM " & tbl

DoCmd.RunSQL sql

Set rst = CurrentDb.OpenRecordset(tbl2)

rst.MoveFirst
pos = rst("PO Number")
qString = "(APPS.PO_HEADERS_ALL.SEGMENT1 = '" & pos & "')"

For X = 2 To rst.RecordCount
    rst.MoveNext
    pos = rst("PO Number")
    qString = qString & " OR (APPS.PO_HEADERS_ALL.SEGMENT1 = '" & pos & "')"
Next X

sql = "SELECT APPS.PO_HEADERS_ALL.PO_HEADER_ID, APPS.PO_HEADERS_ALL.SEGMENT1, APPS.AP_SUPPLIER_SITES_ALL.ADDRESS_LINE1, APPS.AP_SUPPLIER_SITES_ALL.ADDRESS_LINES_ALT, APPS.AP_SUPPLIER_SITES_ALL.ADDRESS_LINE2, APPS.AP_SUPPLIER_SITES_ALL.ADDRESS_LINE3, APPS.AP_SUPPLIER_SITES_ALL.CITY, APPS.AP_SUPPLIER_SITES_ALL.STATE, APPS.AP_SUPPLIER_SITES_ALL.ZIP, APPS.AP_SUPPLIER_SITES_ALL.PROVINCE, APPS.AP_SUPPLIER_SITES_ALL.COUNTRY, APPS.AP_SUPPLIER_SITES_ALL.AREA_CODE, APPS.AP_SUPPLIER_SITES_ALL.PHONE, APPS.AP_SUPPLIER_SITES_ALL.FAX, APPS.AP_SUPPLIER_SITES_ALL.FAX_AREA_CODE, APPS.AP_SUPPLIER_SITES_ALL.TELEX, APPS.AP_SUPPLIER_SITES_ALL.PAYMENT_METHOD_LOOKUP_CODE, APPS.AP_SUPPLIER_SITES_ALL.BANK_ACCOUNT_NAME, APPS.AP_SUPPLIER_SITES_ALL.BANK_ACCOUNT_NUM, APPS.AP_SUPPLIER_SITES_ALL.BANK_NUM, APPS.AP_SUPPLIER_SITES_ALL.BANK_ACCOUNT_TYPE, APPS.AP_SUPPLIER_SITES_ALL.TERMS_DATE_BASIS, APPS.AP_SUPPLIER_SITES_ALL.CURRENT_CATALOG_NUM, APPS.AP_SUPPLIER_SITES_ALL.VAT_CODE, "
sql = sql & "APPS.AP_SUPPLIER_SITES_ALL.INVOICE_AMOUNT_LIMIT, APPS.AP_SUPPLIER_SITES_ALL.PAY_DATE_BASIS_LOOKUP_CODE, APPS.AP_SUPPLIER_SITES_ALL.ALWAYS_TAKE_DISC_FLAG, APPS.AP_SUPPLIER_SITES_ALL.INVOICE_CURRENCY_CODE, APPS.AP_SUPPLIER_SITES_ALL.PAYMENT_CURRENCY_CODE, APPS.AP_SUPPLIER_SITES_ALL.HOLD_ALL_PAYMENTS_FLAG, APPS.AP_SUPPLIER_SITES_ALL.HOLD_FUTURE_PAYMENTS_FLAG, APPS.AP_SUPPLIER_SITES_ALL.HOLD_REASON, APPS.AP_SUPPLIER_SITES_ALL.HOLD_UNMATCHED_INVOICES_FLAG, APPS.AP_SUPPLIER_SITES_ALL.AP_TAX_ROUNDING_RULE, APPS.AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG, APPS.AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_OVERRIDE, APPS.AP_SUPPLIER_SITES_ALL.AMOUNT_INCLUDES_TAX_FLAG, APPS.AP_SUPPLIER_SITES_ALL.EXCLUSIVE_PAYMENT_FLAG, APPS.AP_SUPPLIER_SITES_ALL.TAX_REPORTING_SITE_FLAG, APPS.AP_SUPPLIER_SITES_ALL.VAT_REGISTRATION_NUM, APPS.AP_SUPPLIER_SITES_ALL.OFFSET_VAT_CODE, APPS.AP_SUPPLIER_SITES_ALL.CHECK_DIGITS, APPS.AP_SUPPLIER_SITES_ALL.BANK_NUMBER, APPS.AP_SUPPLIER_SITES_ALL.ADDRESS_LINE4, "
sql = sql & "APPS.AP_SUPPLIER_SITES_ALL.COUNTY, APPS.AP_SUPPLIER_SITES_ALL.ADDRESS_STYLE, APPS.AP_SUPPLIER_SITES_ALL.EDI_TRANSACTION_HANDLING, APPS.AP_SUPPLIER_SITES_ALL.EDI_ID_NUMBER, APPS.AP_SUPPLIER_SITES_ALL.EDI_PAYMENT_METHOD, APPS.AP_SUPPLIER_SITES_ALL.EDI_PAYMENT_FORMAT, APPS.AP_SUPPLIER_SITES_ALL.EDI_REMITTANCE_METHOD, APPS.AP_SUPPLIER_SITES_ALL.BANK_CHARGE_BEARER, APPS.AP_SUPPLIER_SITES_ALL.EDI_REMITTANCE_INSTRUCTION, APPS.AP_SUPPLIER_SITES_ALL.BANK_BRANCH_TYPE, APPS.AP_SUPPLIER_SITES_ALL.PAY_ON_CODE, APPS.AP_SUPPLIER_SITES_ALL.DEFAULT_PAY_SITE_ID, APPS.AP_SUPPLIER_SITES_ALL.PAY_ON_RECEIPT_SUMMARY_CODE, APPS.AP_SUPPLIER_SITES_ALL.PCARD_SITE_FLAG, APPS.AP_SUPPLIER_SITES_ALL.MATCH_OPTION, APPS.AP_SUPPLIER_SITES_ALL.COUNTRY_OF_ORIGIN_CODE, APPS.AP_SUPPLIER_SITES_ALL.FUTURE_DATED_PAYMENT_CCID, APPS.AP_SUPPLIER_SITES_ALL.CREATE_DEBIT_MEMO_FLAG, APPS.AP_SUPPLIER_SITES_ALL.OFFSET_TAX_FLAG, APPS.AP_SUPPLIER_SITES_ALL.SUPPLIER_NOTIF_METHOD, APPS.AP_SUPPLIER_SITES_ALL.EMAIL_ADDRESS, "
sql = sql & "APPS.AP_SUPPLIER_SITES_ALL.REMITTANCE_EMAIL, APPS.AP_SUPPLIER_SITES_ALL.PRIMARY_PAY_SITE_FLAG, APPS.AP_SUPPLIER_SITES_ALL.SHIPPING_CONTROL, APPS.AP_SUPPLIER_SITES_ALL.SELLING_COMPANY_IDENTIFIER, APPS.AP_SUPPLIER_SITES_ALL.GAPLESS_INV_NUM_FLAG, APPS.AP_SUPPLIER_SITES_ALL.DUNS_NUMBER, APPS.AP_SUPPLIER_SITES_ALL.RETAINAGE_RATE, APPS.AP_SUPPLIER_SITES_ALL.TCA_SYNC_STATE, APPS.AP_SUPPLIER_SITES_ALL.TCA_SYNC_PROVINCE, APPS.AP_SUPPLIER_SITES_ALL.TCA_SYNC_COUNTY, APPS.AP_SUPPLIER_SITES_ALL.TCA_SYNC_CITY, APPS.AP_SUPPLIER_SITES_ALL.TCA_SYNC_ZIP, APPS.AP_SUPPLIER_SITES_ALL.TCA_SYNC_COUNTRY, APPS.AP_SUPPLIER_SITES_ALL.PAY_AWT_GROUP_ID, APPS.AP_SUPPLIER_SITES_ALL.CAGE_CODE, APPS.AP_SUPPLIER_SITES_ALL.LEGAL_BUSINESS_NAME, APPS.AP_SUPPLIER_SITES_ALL.DOING_BUS_AS_NAME, APPS.AP_SUPPLIER_SITES_ALL.DIVISION_NAME, APPS.AP_SUPPLIER_SITES_ALL.SMALL_BUSINESS_CODE, APPS.AP_SUPPLIER_SITES_ALL.CCR_COMMENTS, APPS.AP_SUPPLIER_SITES_ALL.DEBARMENT_START_DATE, APPS.AP_SUPPLIER_SITES_ALL.DEBARMENT_END_DATE "
sql = sql & "FROM APPS.PO_HEADERS_ALL INNER JOIN APPS.AP_SUPPLIER_SITES_ALL ON (APPS.PO_HEADERS_ALL.VENDOR_ID = APPS.AP_SUPPLIER_SITES_ALL.VENDOR_ID AND APPS.PO_HEADERS_ALL.VENDOR_SITE_ID = APPS.AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID) "
sql = sql & "WHERE " & qString

qString = ""

CurrentDb.QueryDefs(qry2).sql = sql

sql = ""

DoCmd.OpenQuery qry2, acViewNormal, acEdit
DoCmd.OpenQuery qry, acViewNormal, acEdit
DoCmd.Close acQuery, qry2, acSaveYes
DoCmd.Close acQuery, qry, acSaveYes
DoCmd.OpenTable tbl1, acViewNormal, acEdit
DoCmd.SetWarnings True

Answer 1

Have you tried using ODBC links to the Oracle APPS.PO_HEADERS_ALL and APPS.AP_SUPPLIER_SITES_ALL tables? You should have permissions to do that. This would bypass the problems associated with the passthrough query. Just join the ODBC linked tables to the PO Number table you derived from Excel.

(original answer) I think Andre hit on the problem: Barattolo_67 reports the query is 335,229 characters long. Access specifications say the maximum query size is around 64,000 characters. So Barattolo_67 is clearly exceeding this limit, which is most likely the cause of the System Resources Exceeded error.

Note that the query returns only 89 fields, so that is not the problem. The SELECT part of the query is only 4,000 characters long. The big problem is the WHERE clause, which adds another 300,000 characters.