Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DoCmd.OpenQuery (XPSP1)

    AM batch processing series of queries and SQL with DoCmd in VBA module behind a form.

    When queries run they are showing up; as opposed to previously when they did not show themselves.

    Below is the batch of VBA. What have I forgotten?

    TIA.

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qrkilltbl", acViewNormal, acEdit
    DoCmd.OpenQuery "qryFI_Types", acViewNormal, acEdit
    DoCmd.Close acQuery, "qryFI_Types"
    DoCmd.OpenQuery "qryFI_Types_PeerGroups", acViewNormal, acEdit
    DoCmd.Close acQuery, "qryFI_Types_PeerGroups"
    DoCmd.OpenQuery "qryTotals", acViewNormal, acEdit
    DoCmd.Close acQuery, "qryTotals"
    DoCmd.OpenQuery "qryFITypes_PeerGroups_Redemptions", acViewNormal, acEdit
    DoCmd.Close acQuery, "qryFITypes_PeerGroups_Redemptions"
    DoCmd.TransferSpreadsheet acExport, 8, "tblPeerGroupAvgspreadsheettoSheila", "g:technology management teamstatisticsPeer Group AvgPeer Group Avg" & Format(Date, "mmddyyTESTING") & ".xls", True, ""
    DoCmd.RunSQL "ALTER TABLE [tblFIType_PeerGroups_Redemptions] ADD Avg NUMERIC;"
    DoCmd.RunSQL "ALTER TABLE [tblFIType_PeerGroups_Redemptions] ADD MaxAvg NUMERIC;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=([SumOfREDEMPTION_AMT]/[CountOfID]) WHERE [CountOfID]>49;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]= [Avg]*1.2;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=125.00 WHERE [MaxAvg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=125.00 WHERE [Avg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=125.00 WHERE [Avg]<20.00;"
    DoCmd.OpenQuery "qrPeerGroupAvgtogotobank1", acViewNormal, acEdit
    DoCmd.Close acQuery, "qrPeerGroupAvgtogotobank1"
    DoCmd.OpenQuery "qrPeerGroupAvgtogotobank2", acViewNormal, acEdit
    DoCmd.Close acQuery, "qrPeerGroupAvgtogotobank2"
    DoCmd.OpenQuery "qrPeerGroupAvgtogotobank3", acViewNormal, acEdit
    DoCmd.Close acQuery, "qrPeerGroupAvgtogotobank3"
    DoCmd.Close acTable, "tblPeerGroupAvgbankdata"
    DoCmd.OpenQuery "qrappendtotalrecordcountotbankdatafile", acViewNormal, acEdit
    DoCmd.Close acQuery, "qrappendtotalrecordcountotbankdatafile"
    DoCmd.TransferText acExportDelim, "TblPeerGroupAvgbankdata Export Specification", "tblPeerGroupAvgbankdata", "c:PGA.txt", False, ""
    DoCmd.TransferText acExportDelim, "TblPeerGroupAvgbankdata Export Specification", "tblPeerGroupAvgbankdata", "g:technology management teamstatisticsPGA.txt", False, ""
    DoCmd.TransferText acExportDelim, "tblPeerGroupAvgbankdata Export Specification", "TblPeerGroupAvgbankdata", "g:technology management teamstatisticsPeer Group AvgPeer Group Avg" & Format(Date, "mmddyyyy") & ".txt", False, ""
    DoCmd.OpenQuery "qrPeerGroupAvgspreadsheettoSheila", acViewNormal, acEdit
    DoCmd.Close acQuery, "qrPeerGroupAvgspreadsheettoSheila"
    DoCmd.TransferSpreadsheet acExport, 8, "tblPeerGroupAvgspreadsheettoSheila", "g:technology management teamstatisticsPeer Group AvgPeer Group Avg" & Format(Date, "mmddyyTESTING") & ".xls", True, ""
    MsgBox "The Report is Complete", vbOKOnly, "Import Access Indicators"

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DoCmd.OpenQuery (XPSP1)

    You're using DoCmd.OpenQuery in part of your code. That will display the queries, since that is essentially what you're telling it to do. If these are action queries, you can use DoCmd.RunSQL instead of DoCmd.OpenQuery. If they're *not* action queries, why are you opening/running them in the first place?
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DoCmd.OpenQuery (XPSP1)

    Has this changed since Access 97? In Access 97, the online documentation states that OpenQuery will display the resulting record set for selection queries, crosstab queries and union queries, but will execute action queries and definition queries.

    There is no need to close action queries (DoCmd.Close acQuery, ...), since action queries close themself after they have run.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •