CODEWITHSUNDEEP
sqlms-accessvba
jpl458
jpl458

Reputation: 615

Referencing a query in a different event in the database

Have the following code for a crosstab query that exports to Excel. At the beginning of the code, I reference a query that I created in the QBE grid called 1ACTMasterQuery and everything works.

But, I have copied that SQL into VBA in a string called MasterPivot, and it executes just fine. But I want the following to use PivotMaster rather than 1ACTMasterQuery.

How do I change the code so that uses the data from PivotMaster?

The error says that it can't find PivotMaster. I know that it is a select query and need to fake out the system so that it thinks its a table as in: 

     CurrentDb.CreateQueryDef "SKbyYrByMo", SKbyYrByMo

Tried several guesses but none of them work.

 SKbyYrByMo = "TRANSFORM Sum([1ACTMasterQuery].Extension) AS SumOfExtension" 
 & _
 " SELECT [1ACTMasterQuery].Year, [1ACTMasterQuery].Sku, 
 Sum([1ACTMasterQuery].Extension) AS [Total Of Extension]" & _
 " FROM 1ACTMasterQuery" & _
 " GROUP BY [1ACTMasterQuery].Year, [1ACTMasterQuery].Sku" & _
 " PIVOT [1ACTMasterQuery].MonthNo"

 'Create the view/query to like a table object
 CurrentDb.CreateQueryDef "SKbyYrByMo", SKbyYrByMo

 '-----Set Query Running Status--------------------------------------------- 
 -
 Me.QueryStatus.Visible = True
 Me.QueryStatus.SetFocus
 Me.QueryStatus.BackColor = vbGreen
 Me.QueryStatus.Text = "     Processing"

'---------------------------------------------------------------------------

DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"SKbyYrByMo", _
"C:\Users\John\Desktop\RSExcel.xlsx", _
True

CurrentDb.QueryDefs.Delete "SKbyYrByMo"

'-----End query Staus-------------------------------------------------------
Me.QueryStatus.Text = " "
Me.SKbYbM.SetFocus
Me.QueryStatus.Visible = False
MsgBox "Complete"

Thanks

Upvotes: 0

Views: 53

Answers (1)

Parfait
Parfait

Reputation: 107767

Consider the following routine to create and destroy needed queries with QueryDefs. Also, use a table alias in SQL statements to cut down on length as well as easily replace the source table:

Dim qdef As QueryDef
Dim strSQL As String 

' DELETE QUERIES IF THEY EXIST
For Each qdef in CurrentDb.QueryDefs
   If qdef.Name = "SKbyYrByMo" Or qdef.Name = "PivotMaster" Then
      CurrentDb.QueryDefs.Delete qdef.Name
   End If
Next qdef

' CREATE PivotMaster QUERY
strSQL = "SELECT d.Account_Number, d.Transaction_Type, d.Sku, " & _
         "       d.Extension, d.Year, d.MonthNo" & _
         " FROM dbo_Transaction_Table d"

Set qdef = CurrentDb.CreateQueryDef("PivotMaster", strSQL)
Set qdef = Nothing     ' SAVE NEW QUERY

' CREATE SKbyYrByMo QUERY
strSQL = "TRANSFORM Sum(t.Extension) AS SumOfExtension" & _
         " SELECT t.Year, t.Sku, Sum(t.Extension) AS [Total Of Extension]" & _
         " FROM 1ACTMasterQuery t" & _
         " GROUP BY t.Year, t.Sku" & _
         " PIVOT t.MonthNo"

' REPLACE SOURCE TABLE
strSQL = Replace(strSQL, "1ACTMasterQuery", "PivotMaster")

Set qdef = CurrentDb.CreateQueryDef("SKbyYrByMo", strSQL)
Set qdef = Nothing     ' SAVE NEW QUERY

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
        "SKbyYrByMo", "C:\Users\John\Desktop\RSExcel.xlsx", True

Upvotes: 1

Related Questions