Solved

Confusion about recordsets and querydefs.

Posted on 2015-01-29
16
112 Views
Last Modified: 2015-02-02
My automated system imports Excel range data, compares it to a table.  It grabs new data, updates existing data and dismisses duplicates.  Then it renders and sends a complete results sheet (WITHOUT ID numbers) to the submitters and an unprocessed sheet (WITH ID numbers) to reviewers for editing.

My problem is that the second sheet is coming out without the ID numbers, which is needed to update the records when they come back to the database. Here's the code:
Option Compare Database
Public Sub RequestsTurnaround()

    Dim sPath As String, xlFile As String
    sPath = "G:\XE_ECMs\IPP Sharing Development\New Requests\"
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    xlFile = Dir(sPath & "*.xlsm")

While xlFile <> ""
    Debug.Print xlFile
'Import and delete the request file
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, , "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:Z5000"
    DoCmd.OpenQuery "DeleteProcessSheetsBlanksQ", , acReadOnly
    DoCmd.OpenQuery "TagBatchIDQ", , acReadOnly
    'Kill sPath & xlFile
   xlFile = Dir()
'Process
    DoCmd.SetWarnings False
'Updates Archetypes with matching IPPRID
    DoCmd.OpenQuery "UpdateRequestsQ", , acReadOnly
'Updates Low IP to "Matched"
    DoCmd.OpenQuery "TagLowIPQ", , acReadOnly
'Tags requests matching by ObjectNum AND Country as "Matched"
    DoCmd.OpenQuery "MatchedRequestsQ", , acReadOnly
'Designates request NOT matching BOTH ObjectNum AND Country as "Archetype"
    DoCmd.OpenQuery "SetArchetypesQ", , acReadOnly
'Appends New Archetypes to IPPRequestsT
    DoCmd.OpenQuery "AppendNewArchetypesQ", , acReadOnly
'Create, save and send request batch results
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults As Querydef
    Dim rsResults As DAO.Recordset
    
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\IPP Sharing Development\Templates\IPP_Request_Form.xlsm")
    
    Set qdfResults = CurrentDb.QueryDefs("ProcessSheetsQ")
    Set rsResults = qdfResults.OpenRecordset()
    XL.Visible = False
    wbTarget.Sheets("IPP_Request").Range("A6").CopyFromRecordSet rsResults
    XL.Run "SendRequesterBatchPending"
    Set wbTarget = Nothing

Wend

If DCount("*", "ProcessingBatchOutQ") > 0 Then
    Call SaveOpenBatch
End If

'ClearProcessSheets
    DoCmd.RunSQL "DELETE * FROM ProcessSheets"
    DoCmd.SetWarnings True
End Sub

Public Sub SaveOpenBatch()
    
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults As Querydef
    Dim rsResults As DAO.Recordset
    
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\IPP Sharing Development\Templates\IPP_Request_Form.xlsm")
    
    Set qdfResults = CurrentDb.QueryDefs("ProcessingBatchOutQ")
    Set rsResults = qdfResults.OpenRecordset()
    XL.Visible = False
    wbTarget.Sheets("IPP_Request").Range("A6").CopyFromRecordSet rsResults
    XL.Run "SaveOpenBatch"
    Set wbTarget = Nothing

End Sub

Open in new window

Ideally, the first part of the code saves the results from the "ProcessSheets" comparison table (WITHOUT ID); the records that need to be processed are saved from the "ProcessingBatchOutQ" query--WITH IDs--but they aren't there.

I need to make sure that the two sheets are being saved with the right results.  I'm using the same code to do both saves, just changing the QueryDefs.  Is that the problem?
0
Comment
Question by:Jay Williams
  • 8
  • 8
16 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40578339
So the query ProcessSheetsQ does not output ID numbers
but
query ProcessingBatchOutQ does contain ID numbers.
Correct?

OM Gang
0
 

Author Comment

by:Jay Williams
ID: 40578342
Yes.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40578360
Not trying to question your sanity but please post the SQL for both queries.
Also, you're using the same variable names (for the querydefs and the recordset objects) in both VBA procedures.  That shouldn't be a problem but you can rule it out by changing the second procedure to use unique variable names.


Public Sub SaveOpenBatch()
   
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults2 As Querydef
    Dim rsResults2 As DAO.Recordset
   
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\IPP Sharing Development\Templates\IPP_Request_Form.xlsm")
   
    Set qdfResults2 = CurrentDb.QueryDefs("ProcessingBatchOutQ")
    Set rsResults2 = qdfResults2.OpenRecordset()
    XL.Visible = False
    wbTarget.Sheets("IPP_Request").Range("A6").CopyFromRecordSet rsResults2
    XL.Run "SaveOpenBatch"
    Set wbTarget = Nothing

End Sub

OM Gang
0
 

Author Comment

by:Jay Williams
ID: 40578379
You SHOULD question my sanity! :-) Here they are:

ProcessSheetsQ
SELECT ProcessSheets.RqstrName, ProcessSheets.DrwngNddInBP3rnc, ProcessSheets.ShrChckOrNR, ProcessSheets.ObjectNum, ProcessSheets.ObjectDesc, ProcessSheets.SharedToProject, ProcessSheets.Product, ProcessSheets.IPPSensitivity, ProcessSheets.SharingApproved, ProcessSheets.Country, ProcessSheets.DesignControl, ProcessSheets.RqstAprvlFrom, ProcessSheets.Note, ProcessSheets.PDMLnkFldrGrpLst, ProcessSheets.ApproverName, ProcessSheets.SupplierModel, ProcessSheets.DateRequested, ProcessSheets.ProductToNotify, ProcessSheets.ProjectContactName, ProcessSheets.ShrdToPrjctLnk, ProcessSheets.RqstAprvlFromEmail, ProcessSheets.RqstrEmail, ProcessSheets.RqstrID, ProcessSheets.DateApprovalDue, ProcessSheets.DateClosed, ProcessSheets.ApproverEmail, ProcessSheets.IPPRID, ProcessSheets.DateAssigned, ProcessSheets.ObjectType, ProcessSheets.Company, ProcessSheets.DRPFamNum, ProcessSheets.ProductContact, ProcessSheets.RqstAprvlFromID, ProcessSheets.ApproverID, ProcessSheets.XEApproverID, ProcessSheets.ObjectName, ProcessSheets.[Select], ProcessSheets.Status, ProcessSheets.ProdContEmail, ProcessSheets.DateCreated, ProcessSheets.Batch, ProcessSheets.ReasonForRequest
FROM ProcessSheets;

Open in new window

ProcessingBatchOutQ

Hmmm.  Won't let me see the SQL view. Think that's the problem?  It joins two table by two fields to constrain the results ([ProcessSheets].[ObjectNum] = [IPPRequestT].[ObjectNum] AND [ProcessSheets].[Country] = [IPPRequestT].[Country]).
0
 

Author Comment

by:Jay Williams
ID: 40578387
I put some data in and then I could get the SQL view. I'm all screwed up.  The NewArchetypesQ is a subset of ProcessSheets:
SELECT IPPRequestsT.RqstrName, IPPRequestsT.DrwngNddInBP3rnc, IPPRequestsT.ShrChckOrNR, IPPRequestsT.ObjectNum, IPPRequestsT.ObjectDesc, IPPRequestsT.SharedToProject, IPPRequestsT.Product, IPPRequestsT.IPPSensitivity, IPPRequestsT.SharingApproved, IPPRequestsT.Country, IPPRequestsT.DesignControl, IPPRequestsT.RqstAprvlFrom, IPPRequestsT.Note, IPPRequestsT.PDMLnkFldrGrpLst, IPPRequestsT.ApproverName, IPPRequestsT.SupplierModel, IPPRequestsT.DateRequested, IPPRequestsT.ProductToNotify, IPPRequestsT.ProjectContactName, IPPRequestsT.ShrdToPrjctLnk, IPPRequestsT.RqstAprvlFromEmail, IPPRequestsT.RqstrEmail, IPPRequestsT.RqstrID, IPPRequestsT.DateApprovalDue, IPPRequestsT.DateClosed, IPPRequestsT.ApproverEmail, IPPRequestsT.IPPRID, IPPRequestsT.DateAssigned, IPPRequestsT.ObjectType, IPPRequestsT.Company, IPPRequestsT.DRPFamNum, IPPRequestsT.ProductContact, IPPRequestsT.RqstAprvlFromID, IPPRequestsT.ApproverID, IPPRequestsT.XEApproverID, IPPRequestsT.ObjectName, IPPRequestsT.[Select], IPPRequestsT.Status, IPPRequestsT.ProdContEmail, IPPRequestsT.DateCreated, IPPRequestsT.Batch, IPPRequestsT.ReasonForRequest
FROM NewArchetypesQ INNER JOIN IPPRequestsT ON (NewArchetypesQ.Country = IPPRequestsT.Country) AND (NewArchetypesQ.ObjectNum = IPPRequestsT.ObjectNum);

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 40578390
If you aren't able to see the SQL view then that very well could be the problem.  Sometimes Access isn't able to display a graphical view (passthrough queries, etc.) but it should always be able to display the SQL.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 40578398
What is the ID field/column you need in your output?
OM Gang

SELECT IPPRequestsT.RqstrName, IPPRequestsT.DrwngNddInBP3rnc, IPPRequestsT.ShrChckOrNR, IPPRequestsT.ObjectNum, IPPRequestsT.ObjectDesc, IPPRequestsT.SharedToProject, IPPRequestsT.Product, IPPRequestsT.IPPSensitivity, IPPRequestsT.SharingApproved, IPPRequestsT.Country, IPPRequestsT.DesignControl, IPPRequestsT.RqstAprvlFrom, IPPRequestsT.Note, IPPRequestsT.PDMLnkFldrGrpLst, IPPRequestsT.ApproverName, IPPRequestsT.SupplierModel, IPPRequestsT.DateRequested, IPPRequestsT.ProductToNotify, IPPRequestsT.ProjectContactName, IPPRequestsT.ShrdToPrjctLnk, IPPRequestsT.RqstAprvlFromEmail, IPPRequestsT.RqstrEmail, IPPRequestsT.RqstrID, IPPRequestsT.DateApprovalDue, IPPRequestsT.DateClosed, IPPRequestsT.ApproverEmail, IPPRequestsT.IPPRID, IPPRequestsT.DateAssigned, IPPRequestsT.ObjectType, IPPRequestsT.Company, IPPRequestsT.DRPFamNum, IPPRequestsT.ProductContact, IPPRequestsT.RqstAprvlFromID, IPPRequestsT.ApproverID, IPPRequestsT.XEApproverID, IPPRequestsT.ObjectName, IPPRequestsT.[Select], IPPRequestsT.Status, IPPRequestsT.ProdContEmail, IPPRequestsT.DateCreated, IPPRequestsT.Batch, IPPRequestsT.ReasonForRequest
FROM NewArchetypesQ INNER JOIN IPPRequestsT ON (NewArchetypesQ.Country = IPPRequestsT.Country) AND (NewArchetypesQ.ObjectNum = IPPRequestsT.ObjectNum);
0
 

Author Comment

by:Jay Williams
ID: 40578429
IPPRequestsT.IPPRID
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Expert Comment

by:omgang
ID: 40578463
That confirms the desired ID value is present in the query SQL.
Just for kicks and grins please try renaming the variables as I suggested in my post above.  Once you've instantiated those object variables they're resident in memory.  In the first procedure you instantiate both qdfResults and rsResults.  Then in the second procedure you declare and instantiate two additional object variables with the same names.  Now their scope should be procedure level but.......maybe the procedure is grabbing the existing, in-memory instance, of the object variables.  That might explain why you're getting results from the first recordset instead of the second as expected.  By renaming the object variables in the second procedure you can eliminate that as a possibility.  Also, not a bad idea to destroy your object variables at the end of the routines as they will otherwise reside in memory until the Access application quits.
OM Gang
0
 

Author Comment

by:Jay Williams
ID: 40578468
OK.  That all sounds good.  I'll give it all a whirl.  Thanks for hanging in with me.
0
 

Author Comment

by:Jay Williams
ID: 40578494
Well, same thing.  I think that's about all the fun I can stand for today.
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 40578539
OK.  The next thing we should look at is how these procedures are being called.  Do you have events (buttons, etc.) that a user clicks to initiate the process?  I'll check in tomorrow and we'll see if we can get this ironed out.
OM Gang
0
 

Author Comment

by:Jay Williams
ID: 40578564
OK, Thanks!  This procedure is called by Outlook VBA (I know it ain't pretty, but it works . . . sort of):
Private Sub ProcessRequests()
    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim oMail As Outlook.MailItem
    Dim myNameSpace As Outlook.NameSpace
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")
    Set myNameSpace = Application.GetNamespace("MAPI")

On Error GoTo notfoundFolder
    Set myItems = myNameSpace.Folders("XE_IPP").Folders("Inbox").Items
    Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
    For Each oMail In myItems
        If TypeName(oMail) = "MailItem" Then
            If oMail.Subject = "IPP Share Request" And LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                   oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\New Requests\" & oMail.Attachments.Item(1).FileName
                   Set moveFolder = myNameSpace.Folders("XE_IPP").Folders("Inbox").Folders("Requests")
                   oMail.Move moveFolder
                   
                    Dim accApp As Access.Application
                    Set accApp = New Access.Application
                    'This is where I want Access to run invisible
                    accApp.openCurrentDatabase ("G:\XE_ECMs\IPP Sharing Development\Processing.accdb")
                    accApp.Run ("RequestsTurnaround")
                    accApp.Quit
            End If
        End If
    Next
        
    On Error GoTo 0

    Exit Sub

notfoundFolder:
    End

End Sub

Open in new window

The whole system is supposed to temporarily keep things working until the SAP guys come up with a web-based solution.  I'm using overlapping Excel, Outlook and Access VBA to hand off and manage data for an intellectual properties sharing approval process.  The actual database part of it is pretty straightforward, but getting all the outside applications to work together is . . . a lot harder. :-)  They were doing it all by hand; you're right, it wasn't working.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40584342
Is it possible for you to
1) Upload a copy of the Access db with some sample data in it
2) Upload a copy of IPP_Request_Form.xlsm
?

OM Gang
0
 

Author Comment

by:Jay Williams
ID: 40584375
OM Gang, I did solve the problem this morning.  As you might expect, it was a relatively simple issue.  The range import was not set correctly and therefore not pulling in all of the columns on the sheet I needed to make the database updates.  Once I changed the range from A6:Z5000 to A6:AO5000 it all came in.  Just for fun, I also changed the variable names (for the querydefs and the recordset objects) in the second VBA procedure as you suggested.  You were right, though; the procedure in Access was calling the incomplete data.  Everything is hunky-dory now. :-)
0
 
LVL 28

Expert Comment

by:omgang
ID: 40584382
Glad you got that figured out!  Good work.
OM Gang
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now