DoCmd.OutputTo - Excel coded sql string

[Geirr]

Hi all.

I have an (odd) question;
The code: DoCmd.OutputTo acOutputQuery, sql_File, "Excel Workbook (*.xlsx)", , True
Works like a charm when using a predefined query in Access, named sql_File.
But, I would like to hardcode the string inside a form module and set up as sql-string based on several situations

Why doesn't this work - I always getting the 3011 error (cannot find the items in sql string - nor fields or table):
Dim tmpSqlStr As String
tmpSqlStr = "SELECT * FROM dbo_11_Area"
DoCmd.OutputTo acOutputQuery, tmpSqlStr, "Excel Workbook (*.xlsx)", , True

I've tried all combination of quotes, brackets etc around the tmpSqlStr, with or without the dbo_ in table name without any luck.
The Sql String in the variable tmpSqlStr are stripped down to a minimum, but still can't get it to work. I believe there is a tiny detail - but I can't see it...
The BE is an std. sql-server (hence dbo_)

I really hope someone have smart fix for this

Best regards,
Geirr

 

[Ranman256]

uses a query , not sql:

docmd.OutputTo acOutputQuery ,"qsMyQuery" ,acformatXLS ,vFile

 

[Minty]

Make a query that you will use as a container for your sql string, then adjust it and save it:

Dim qdf as QueryDef

set qdf = CurrentDb.QueryDefs("YourStoredQueryName")
tmpSqlStr = "SELECT * FROM dbo_11_Area"
qdf.SQL = tmpSqlStr
qdf.Close

DoCmd.OutputTo acOutputQuery, YourStoredQueryName, "Excel Workbook (*.xlsx)", , True

 

[Geirr]

Thank for your input Minty, but when I tried with a direct copy of your code, I've got error message: Compile Error, Variable not defined, and the yellow mark on the YourStoredQueryName.

Just for excluding possibilities I tried with any combintaion (because usage/not usage and type of quotes never stops surprising me...) of single/double quotes, and with "& YourStoredQueryName &" i was back on 3011 error.
I even tried to make a empty query named YourStoredQueryName - with no luck....

Brg. Geirr.

 

[Gasman]

Thank for your input Minty, but when I tried with a direct copy of your code, I've got error message: Compile Error, Variable not defined, and the yellow mark on the YourStoredQueryName.

Just for excluding possibilities I tried with any combintaion (because usage/not usage and type of quotes never stops surprising me...) of single/double quotes, and with "& YourStoredQueryName &" i was back on 3011 error.
I even tried to make a empty query named YourStoredQueryName - with no luck....

Brg. Geirr.

YourStoredQueryName is meant to be replaced with exactly that, the name of your query.

 

[Minty]

Please post up the entire code for the repot run and give us the name of the query to use.

Make sure you use the </> option in the editor to enclose it all in code tags

 

[Gasman]

Initially

Then run the code

Sub SetQdf()
Dim qdf As QueryDef
Dim tmpSqlStr As String

Set qdf = CurrentDb.QueryDefs("YourStoredQueryName")
tmpSqlStr = "SELECT * FROM TestTransactions"
qdf.SQL = tmpSqlStr
qdf.Close
Debug.Print qdf.SQL
Set qdf = Nothing

End Sub

After

 

[Geirr]

Hi Gasman and Minty.

It works! I didn't realize that I should put the SetQdf in a separate sub.
But a new lesson are learned. Thank you both for the help.

Best Regards,
Geirr.

 

[Minty]

Well, actually you don't need to but that would ensure that the variables are all declared in the right places...

 

[Gasman]

Hi Gasman and Minty.

It works! I didn't realize that I should put the SetQdf in a separate sub.
But a new lesson are learned. Thank you both for the help.

Best Regards,
Geirr.

As Minty says, you do not have to.
That was just me testing the code.