Solved

Issues with docmd.openquery

Posted on 2010-11-16
10
287 Views
Last Modified: 2012-05-10
I've never run into this issue before, but I created a click event that runs a qry using the following command and then exports the file.

DoCmd.SetWarnings False
DoCmd.OpenQuery "SalesByRegion", acViewNormal, acEdit
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableSbR", "C:\TableSbR.xls", True

It works, but when I run, it opens the query for viewing results instead of just running behind the scenes.  I've used thus same code before in other cases and never had it show results.

How do I get it to stop showing the query results?
0
Comment
Question by:vsllc
  • 5
  • 4
10 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"It works, but when I run, it opens the query for viewing results instead of just running behind the scenes. "

That's what OpenQuery does on a SELECT query.  That is the expected behavior.

You need to include the query name in the Transfer command ... in which case, the query itself will not open, but the results will be exported.

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Clarification:

"You need to include the query name in the Transfer command "

>> and remove the DoCmd.OpenQuery command.

mx
0
 

Author Comment

by:vsllc
Comment Utility
My query creates the table I'm exporting.  How do I do what you suggest?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"My query creates the table I'm exporting"
How can a SELECT query create a table?  

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"My query creates the table I'm exporting.
Then I am confused, ...If this is indeed an Action query (Make Table), then no results should appear on screen when the query is run.

Try it like this perhaps:

'The SQL for your Make Table query
CurrentDB.Execute "SELECT * INTO TableSbR.....",dbfailonerror
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableSbR", "C:\TableSbR.xls", True

JeffCoachman



0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:vsllc
Comment Utility
Nevermind, I miskeyed in my code.  A create table qry uses the results of the select qry.  I keyed the name of the select qry in my code.  Since the table was already created in a prev test the export worked without the create table name.

Just been working too long I guess!


0
 

Author Comment

by:vsllc
Comment Utility
mx-still wondering though how to do your suggestion assuming I want to just export the results from my select query to excel?  I've only ever exported tables.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
From Help:

"TableName   Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, **or the Microsoft Access select query whose results you want to export to a spreadsheet ***."

So, I would say substitute the name of a Select query in place of the table ...

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"YourTableOrQueryName" .......

mx
0
 

Author Comment

by:vsllc
Comment Utility
Thanks!
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You are welcome ...

mx
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now