System Resource Exceeded

I have written the below code, which works fine until it has to pass the sql code to the query definition (CurrentDb.QueryDefs(qry2).sql = sql), as then I get a "System Resource Exceeded" error.   In the example I am testing, the number of OR clauses in qString will be 131.  Any ideas?

Thank you.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Public Function ImportServicePOs()
On Error GoTo ImportServicePOs_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_ServicePosWithVendorDetails"
qry2 = "qry_ServicePosWithVendorDetailsPQ"
tbl1 = "tbl_ServicePosWithVendorDetails"
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

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 & ";"

CurrentDb.QueryDefs(qry2).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

ImportServicePOs_Exit:
    Exit Function

ImportServicePOs_Err:
    MsgBox Error$
    Resume ImportServicePOs_Exit
   
End Function
Marco BalestraData Analyst/Internal AuditorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marco BalestraData Analyst/Internal AuditorAuthor Commented:
I forgot to mention that I am using Access 2010 on a Windows 7 operated machine.
Dale FyeOwner, Developing Solutions LLCCommented:
So, instead of all those OR statements, why not create a query with a join to tbl2.

Add tbl2 to the query grid and insert a join line between
PPS.PO_HEADERS_ALL.SEGMENT1 and tbl2.[PO Number]

This will eliminate the need for the criteria testing all of the possible [PO Number] values.
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Hi Dale:

Thank you for the reply.  The problem is that this is a passthrough query, while tbl2 is a locally generated table.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
can you not generate tbl2 on the server side, or is it being generated based upon an Access table?

Instead of a pass-through query, run a stored procedure, generate tbl2 as a temp table on the server and simply return the recordset to Access.
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Unfortunately, I do not have access to generating anything on the server side.  I only have a read-only access.
Dale FyeOwner, Developing Solutions LLCCommented:
Then create your query without the WHERE clause as a pass-through query.  Then create a new query that uses this query and tbl2

SELECT savedQueryName.*
FROM savedQueryName INNER JOIN tbl2 on SavedQueryName.SEGMENT1 = tbl2.[PO Number]

I'm going to try something else and will get back to you.
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Thank you.  I will try, although this would mean downloading all the purchase orders in the system with no filters.
Dale FyeOwner, Developing Solutions LLCCommented:
"... although this would mean ..."

Yeah, I know, that's why I was looking into something else.

You could also construct your where clause with a syntax:

strWHERE = "WHERE APPS.PO_HEADERS_ALL.SEGMENT1 IN ('PO1', 'PO2', 'PO3') instead of all the OR clauses.
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Good catch!  I will try this one tomorrow, and let you know.
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Thanks.
Dale FyeOwner, Developing Solutions LLCCommented:
I also noticed that your FROM clause includes the parenthesis that Access (Jet) throws in.  These will cause problems as SQL Server will not recognize those.  The FROM clause should read:

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"

I would try this fix before making any other changes.  Then if this doesn't resolve the problem, modify the WHERE clause with the IN ( ) syntax.

I've also stumbled on another way to do this that might be even more efficient, but lets see if these fixes don't resolve your problem first.

Dale
Dale FyeOwner, Developing Solutions LLCCommented:
Lets try the other syntax first, but I stumbled on an idea which also might work for you.  Don't know whether this query will run quicker than what you have or not.  It involves actually creating a temp table on SQL Server as part of the pass-through query.  Syntax of the PT query would look like:
SET NOCOUNT ON 

declare @TempData as table (PO_Number nvarchar(10))
insert into @TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN @TempData ON yourTable.FieldName = @TempDate.PO_Number

Open in new window

So instead of creating all of the OR statements for the current query, or the IN ( ) clause for my previous recommendation, you would create the top part of the query.  The only down side of this is that pass-through queries are limited to 32K characters, so you could end up bumping into a character limit (but this applies to all of the recommendations above).
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Dear Dale:

I removed the parenthesys, but to no avail.  The IN clause also did not work as it limits to 1000 possible values, where sometimes I have more that 1000 purchase orders.  Your last solution I could not try, as I have no access to creating anything on the server side.

The good news is that I found an acceptable solution; acceptable, not ideal.

I had noticed that despite the error, the SQL code was actually being transferred to the passthrough query.  Therefore, I just shifted the latter part of the execution inside the on error part.  Please see below revised code.  I know this is not ideal, but it works.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Option Compare Database

Public Function ImportServicePOs()
On Error GoTo ImportServicePOs_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_ServicePosWithVendorDetails"
qry2 = "qry_ServicePosWithVendorDetailsPQ"
tbl1 = "tbl_ServicePosWithVendorDetails"
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

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

ImportServicePOs_Exit:
    Exit Function

ImportServicePOs_Err:
    If Error$ = "System Resource Exceeded." Then
        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
       
    Else
        MsgBox Error$
        Resume ImportServicePOs_Exit
    End If
   
End Function
Dale FyeOwner, Developing Solutions LLCCommented:
1.  You still need to remove the parenthesis from the WHERE clause.  SQL Server will not be able to analyze that code and will most certainly return an error.

2.  You should try the last technique I suggested, I think you will be surprised that this is actually allowed, even when you only have SELECT permissions on the server.
SET NOCOUNT ON 

declare @TempData as table (PO_Number nvarchar(10) primary key)
insert into @TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * FROM @TempData

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Thank you Dale for your time and assistance.
Dale FyeOwner, Developing Solutions LLCCommented:
Marco,

Glad to help.

Did you try the @TempData code?
Marco BalestraData Analyst/Internal AuditorAuthor Commented:
Yes, and it worked.  The only problem is that I do not know how to pass the PO numbers to the PQ query you suggested.
Dale FyeOwner, Developing Solutions LLCCommented:
it would start out with something like:
Dim strCreate as string
Dim strPONumbers as strring

strCreate = "SET NOCOUNT ON " & vbcrlf & vbcrlf _
          & "declare @TempData as table (PO_Number nvarchar(10) primary key)" & vbcrlf _
          & "insert into @TempData values "

Set rst = CurrentDb.OpenRecordset(tbl2)
While not rst.EOF
    strPONumbers = strPONumbers & ", ('" & rst![PO Number] & "')"
    rst.MoveNext
Wend
strPONumbers = Mid(strPONumbers,3)  'This strips out the leading ", " values

qd.SQL = strCreate & strPONumbers & vbcrlf _
       & "SELECT * FROM @TempData"

Open in new window

If you build the querydef with this code, and run it, you should get a list of the PO Numbers in tbl2.

If that works, you would:
1.  remove the last line of that code and append the SELECT statement from your original query
2.  add an additional JOIN in the FROM clause that joins on

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
INNER JOIN @TempData ON APPS.PO_HEADERS_ALL.SEGMENT1 = @TempData.PO_Number

3.  Remove the WHERE clause, because the INNER JOIN to @TempData would have the same effect as the OR clauses.

However, my concern at this point is you made a comment in one of your previous posts that you could have over a thousand PO Numbers in tbl2.  Those PO numbers are strings, how long are they?  Your SQL string is limited to a total of 32K (32,768) characters, so you could bump into the character limit.  Even with a 10 character PO number, 1000 records is only going to be 10,000, and your select clause can't be more than a couple K, so I think you should be OK with this.

I wouldn't replace your original code, if it is working, but I would test this to see if it runs more efficiently.
Dale FyeOwner, Developing Solutions LLCCommented:
Marco,

I just read an article on using table variables versus temp tables, and the author of the article explained that he would recommend use to temp tables for this type of thing over the table variable because of the way SQL Server will optimize the query plan.  So instead of the syntax I mentioned above, you would be better off with a syntax like:
SET NOCOUNT ON 

declare #TempData as table (PO_Number nvarchar(10) primary key)
insert into #TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * FROM #TempData

DROP TABLE #TempData

Open in new window

 You will note that in this code, I added the DROP TABLE command to drop the temp table at the end of the process.  Not many changes to the code I provided above.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.