Marco Balestra
asked on
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)
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_ServicePosWithVendorD
qry2 = "qry_ServicePosWithVendorD
tbl1 = "tbl_ServicePosWithVendorD
Set fd = Application.FileDialog(mso
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(tb
sql = "SELECT DISTINCT " & tbl & ".[PO Number]"
sql = sql & "INTO " & tbl2 & " "
sql = sql & "FROM " & tbl
DoCmd.RunSQL sql
Set rst = CurrentDb.OpenRecordset(tb
rst.MoveFirst
pos = rst("PO Number")
qString = "(APPS.PO_HEADERS_ALL.SEGM
For X = 2 To rst.RecordCount
rst.MoveNext
pos = rst("PO Number")
qString = qString & " OR (APPS.PO_HEADERS_ALL.SEGME
Next X
sql = "SELECT APPS.PO_HEADERS_ALL.PO_HEA
sql = sql & "APPS.AP_SUPPLIER_SITES_AL
sql = sql & "APPS.AP_SUPPLIER_SITES_AL
sql = sql & "APPS.AP_SUPPLIER_SITES_AL
sql = sql & "FROM APPS.PO_HEADERS_ALL INNER JOIN APPS.AP_SUPPLIER_SITES_ALL
sql = sql & "WHERE " & qString & ";"
CurrentDb.QueryDefs(qry2).
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
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_ServicePosWithVendorD
qry2 = "qry_ServicePosWithVendorD
tbl1 = "tbl_ServicePosWithVendorD
Set fd = Application.FileDialog(mso
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(tb
sql = "SELECT DISTINCT " & tbl & ".[PO Number]"
sql = sql & "INTO " & tbl2 & " "
sql = sql & "FROM " & tbl
DoCmd.RunSQL sql
Set rst = CurrentDb.OpenRecordset(tb
rst.MoveFirst
pos = rst("PO Number")
qString = "(APPS.PO_HEADERS_ALL.SEGM
For X = 2 To rst.RecordCount
rst.MoveNext
pos = rst("PO Number")
qString = qString & " OR (APPS.PO_HEADERS_ALL.SEGME
Next X
sql = "SELECT APPS.PO_HEADERS_ALL.PO_HEA
sql = sql & "APPS.AP_SUPPLIER_SITES_AL
sql = sql & "APPS.AP_SUPPLIER_SITES_AL
sql = sql & "APPS.AP_SUPPLIER_SITES_AL
sql = sql & "FROM APPS.PO_HEADERS_ALL INNER JOIN APPS.AP_SUPPLIER_SITES_ALL
sql = sql & "WHERE " & qString & ";"
CurrentDb.QueryDefs(qry2).
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
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.SEGMENT
This will eliminate the need for the criteria testing all of the possible [PO Number] values.
Add tbl2 to the query grid and insert a join line between
PPS.PO_HEADERS_ALL.SEGMENT
This will eliminate the need for the criteria testing all of the possible [PO Number] values.
ASKER
Hi Dale:
Thank you for the reply. The problem is that this is a passthrough query, while tbl2 is a locally generated table.
Thank you for the reply. The problem is that this is a passthrough query, while tbl2 is a locally generated table.
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.
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.
ASKER
Unfortunately, I do not have access to generating anything on the server side. I only have a read-only access.
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.
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.
ASKER
Thank you. I will try, although this would mean downloading all the purchase orders in the system with no filters.
"... 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.SEGMEN
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.SEGMEN
ASKER
Good catch! I will try this one tomorrow, and let you know.
ASKER
Thanks.
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
& "AND APPS.PO_HEADERS_ALL.VENDOR
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
sql = sql & "FROM APPS.PO_HEADERS_ALL INNER JOIN APPS.AP_SUPPLIER_SITES_ALL
& "ON APPS.PO_HEADERS_ALL.VENDOR
& "AND APPS.PO_HEADERS_ALL.VENDOR
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
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_NumberSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Dale for your time and assistance.
Marco,
Glad to help.
Did you try the @TempData code?
Glad to help.
Did you try the @TempData code?
ASKER
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.
it would start out with something like:
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
AND APPS.PO_HEADERS_ALL.VENDOR
INNER JOIN @TempData ON APPS.PO_HEADERS_ALL.SEGMEN
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.
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"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
AND APPS.PO_HEADERS_ALL.VENDOR
INNER JOIN @TempData ON APPS.PO_HEADERS_ALL.SEGMEN
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.
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:
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
ASKER