Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
creating excel file from access with docmd
I am creating an excel spreadsheet from access where I build the query in the code behind using docmd.
Dim excelquery As String
Dim excelName As String
excelquery = "SELECT a,b, c FROM tableSql where a = 123"
excelName = "Excel_" & Month(Date) & Day(Date) & Year(Date) & ".xls"
DoCmd.OutputTo acOutputQuery, excelquery, acFormatXLS, excelName, True
When I run it I get a message that the access database could not find the object "Select ..."
Is there another way to do this?
Dim excelquery As String
Dim excelName As String
excelquery = "SELECT a,b, c FROM tableSql where a = 123"
excelName = "Excel_" & Month(Date) & Day(Date) & Year(Date) & ".xls"
DoCmd.OutputTo acOutputQuery, excelquery, acFormatXLS, excelName, True
When I run it I get a message that the access database could not find the object "Select ..."
Is there another way to do this?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
in the command line that you use, it needs a saved query..
if you create a query using the QBE with this SQL statement
SELECT a,b, c FROM tableSql where a = 123
save the query as myQuery, then you can use in the command line
DoCmd.OutputTo acOutputQuery, "myquery", acFormatXLS, excelName, True
if you create a query using the QBE with this SQL statement
SELECT a,b, c FROM tableSql where a = 123
save the query as myQuery, then you can use in the command line
DoCmd.OutputTo acOutputQuery, "myquery", acFormatXLS, excelName, True
I am creating the query in the afterUpdate event of the form. I am not sure how to save it as a query when I build it.
first create a query then save it.
is this query you are building will be the same but different criteria?
is this query you are building will be the same but different criteria?
They query changes based on multiple fields on the form. There are many different combinations for the where statement.
you can do this
assuming you saved a query from above as myQuery
Dim excelquery As String
Dim excelName As String
dim qd as dao.querydef
excelquery = "SELECT a,b, c FROM tableSql where a = 123"
excelName = "Excel_" & Month(Date) & Day(Date) & Year(Date) & ".xls"
set qd=currentdb.querydefs("my
qd.sql=excelquery
DoCmd.OutputTo acOutputQuery, "myquery", acFormatXLS, excelName, True
assuming you saved a query from above as myQuery
Dim excelquery As String
Dim excelName As String
dim qd as dao.querydef
excelquery = "SELECT a,b, c FROM tableSql where a = 123"
excelName = "Excel_" & Month(Date) & Day(Date) & Year(Date) & ".xls"
set qd=currentdb.querydefs("my
qd.sql=excelquery
DoCmd.OutputTo acOutputQuery, "myquery", acFormatXLS, excelName, True
DAO.Querydef was not defined in my version of access. I did the follow and it worked. Do you think I should delete the table when done?
Dim curDatabase As Object
Dim qry As Object
Set qry = curDatabase.CreateQueryDef
DoCmd.OutputTo acOutputQuery, "ToExcel", acFormatXLS, excelName, True
Dim curDatabase As Object
Dim qry As Object
Set qry = curDatabase.CreateQueryDef
DoCmd.OutputTo acOutputQuery, "ToExcel", acFormatXLS, excelName, True
<Do you think I should delete the table when done?>
Yes you can, it is up to you.
but, like what i posted above, you can reuse the query by changing its definition.
Yes you can, it is up to you.
but, like what i posted above, you can reuse the query by changing its definition.
btw, what access version are you using?
to enable DAO.Querydef, you have to add to your References the Microsoft DAO 3.6 Object Library
to enable DAO.Querydef, you have to add to your References the Microsoft DAO 3.6 Object Library
Premium Content
You need an Expert Office subscription to comment.Start Free Trial