Access VBA Macro to run pass through query

Question

I have a pass through query built in Teradata set to export data to an Excel spreadsheet. I'm trying to automate it, but when I run the macro or open the query, a window pops up asking for the data source. I have an ODBC connection created and I'm thinking there has to be a way to make the macro pass the data source name so it will run without interaction.

enter image description here

Edit: Adding Macro as requested

Function AutoExec()
On Error GoTo AutoExec_Err

DoCmd.OutputTo acOutputQuery, "Performance Interval Data", "ExcelWorkbook(*.xlsx)", _
"filepath\filename.xlsx", False, "", , acExportQualityPrint
    DoCmd.Quit acExit    

AutoExec_Exit:
    Exit Function

AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit

End Function

Have you set your connection string in the ODBC query? You can google for DSN-less connection strings — dbmitch
Function AutoExec() On Error GoTo AutoExec_Err DoCmd.OutputTo acOutputQuery, "Performance Interval Data", "ExcelWorkbook(*.xlsx)", "filepath\filename.xlsx", False, "", , acExportQualityPrint DoCmd.Quit acExit AutoExec_Exit: Exit Function AutoExec_Err: MsgBox Error$ Resume AutoExec_Exit End Function — TommyM

Jiggles32 Jiggles32 · Accepted Answer · 2018-05-18T21:16:45

Couple of concerns, (can't validate any of this right now as I do not currently have access to Access for testing), but it looks like:

You're trying to OutputTo a query, to the best of my knowledge that is not feasible.
Your file path is setup as filepath\filename.xlsx unless that is the actual location and name of your Excel sheet, something seems wrong there to me.
I don't really think this macro relates to an ODBC of any sort in its current state.

But, you should at least start with fixing the filepath issue. That should be the full path to your Excel file and the full name of the file as well. (i.e. C:\TEMP\TestExcelSheet.xlsx)

All that being said, you may want to just go with something like this (although its a little difficult to tell if this is what you actually want or not):

'Export Excel file from Query
DoCmd.TransferSpreadsheet acExport, , "acOutputQuery", _
"C:\TEMP\TestExcelSheet.xlsx", True

NOTE: "acOutputQuery" should be the actual name of your passthrough query, "C:\TEMP\TestExcelSheet.xlsx" would be your destination path, and True adds the query's headers into the sheet, False to ignore the headers.

Access VBA Macro to run pass through query

1 Answers