CODEWITHSUNDEEP
sqlms-accessvba
jpl458
jpl458

Reputation: 615

DOCmd can't find string containing SQL

Have the following code:

Dim SKbyYrByMo As String
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"

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

If I change the name of the query to one in the ACCESS query list it works fine. But when I use SKbyYrByMo I get the message

'The Microsoft database engine could not find the object 'SKbyYrByMo'.

Upvotes: 0

Views: 46

Answers (1)

JNevill
JNevill

Reputation: 50019

You have to create a query/view and then point the DoCmd.TransferSpreadsheet to that newly created object as the "table" parameter:

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
CurrentDB.CreateQueryDef "SKbyYrByMo", SKbyYrByMo

'Now you can reference that:
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel12Xml, _
"SKbyYrByMo", _
"C:\Users\John\Desktop\RSExcel.xlsx", _
True

'And Delete the query:
CurrentDb.QueryDefs.Delete "SKbyYrByMo"

The reason for this is that the third parameter in the TransferSpreadsheet method takes a string that is a "TableName":

A string expression that's the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.

If you don't create the query then all you have is SQL in a variable that is a string (Not a tablename). So we create the querydef and then pass the name of that querydef into TransferSpreadsheet method, then blow away the querydef since we don't really need that object hanging out in the database.

It would be nice if, instead, that method took a sql string...

Upvotes: 1

Related Questions