Passthrough SQL in VBA

[CoffeeGuru]

I am attempting to pass a SQL statement from Access to SQL Server

The tables are SQL temporary tables and this query should drop then build the table [TopStores]
The code appears to run as there are not any errors displayed, however the table is not created
The temporary tables which this is built from DO EXIST and are available to this instance of Access

I included the line Dim sql As String * 1024 as a result of this forum believing that this was the issue and the string was too long, but I cannot prove this is or is not still an issue as further research shows that the WATCH viewer in debug is unreliable.

This is my Code

Private Sub Command510_Click()
 'declare the variables
 Dim qdf As QueryDef
 Dim sql As String * 1024
  
'declare the SQL statement that will query the database
 Set qdf = CurrentDb.QueryDefs("Top x Stores By Revenue")
 sql = "IF OBJECT_ID('[TopStores]') IS NOT NULL DROP TABLE [TopStores]" & _
       "SELECT TOP " & Me.Topx & " Country" & _
       ",Retailer" & _
       ",[Store Number]" & _
       ",[Store Location]" & _
       ",sum(Revenue) AS Revenue" & _
       ",(SELECT sum([revenue]) FROM #SalesData52) AS Total_revenue" & _
       ",sum([Revenue])/(SELECT sum([revenue]) FROM #SalesData52) AS [Rev%] " & _
       "INTO [dbo].[EMEA_Cat].[TopStores] " & _
       "from [#salesdata52] " & _
       "GROUP BY Country" & _
       ",Retailer" & _
       ",[Store Number]" & _
       ",[Store Location] "
       
 DoCmd.OpenReport "Top 10 Stores by Revenue", acViewReport
 
End Sub

 

[CJ_London]

Not quite clear what you are doing - you say

The code appears to run as there are not any errors displayed, however the table is not created

but looking at the code you

1. set a querydef to an existing query - no idea what this query does and you never execute it
2. assign a sql statement to a string but do nothing with it
3. open a report -no idea what the recordsource to the report is

At no point can I see the code
running a make table query or
assigning the ODBC string required to make it a passthrough query or
linking to the newly created table or
running a passthrough query