Avatar of kw66722
kw66722
 asked on

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?
Microsoft Access

Avatar of undefined
Last Comment
kw66722

8/22/2022 - Mon
SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
kw66722

ASKER
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.
Rey Obrero (Capricorn1)

first create a query then save it.

is this query you are building will be the same but different criteria?
kw66722

ASKER
They query changes based on multiple fields on the form.  There are many different combinations for the where statement.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rey Obrero (Capricorn1)

<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.
Rey Obrero (Capricorn1)

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
kw66722

ASKER
I am using access 2013.  I thought about doing that but I wanted something that I could keep copying and not worrying about libraries not be available.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rey Obrero (Capricorn1)

hmm, the DAO library should be available with version 2013.
kw66722

ASKER
I am selecting mine because it is part of the solution.