How to fix MS Access error: Query must have at least one destination field.

Fix MS Access error: Query must have at least one destination field

I have had tolerated this MS Access error for so long and finally I decided to do something about it.

"Query must have at least one destination field."

Background info:

I have an Access form with a few buttons. One of the buttons opens up an Excel spreasheet by select some data from an Access table and then display them in Excel. I use VBA command DoCmd.OutputTo to do the job, see below. The DoCmd calls a pre-built Access query to select the data.

DoCmd.OutputTo acOutputQuery, "qry_2ndQuarter", acFormatXLS, "2ndQuarter.xls", True

Most of the time it runs without any problems, but once in a while it errors out with error message like this "Query must have at least one destination field.". It's caused by the fact that the SQL query inside qry_2ndQuarter is deleted by Access on random basis.

I debugged step by setp and the sql is definitely erased after running DoCmd.OutputTo command. So after years of frustration (used Access 2003 and Access 2007), I decide to tackle it by VBA code rather than trying to figure out why it got deleted randomly.

I used to manually rebuild the query by pasting SQL into it after it got erased. Now I use VBA code to automatically rebuild the query if the SQL of the query is automatically deleted by Access. This freed me to do more productive work than being a manual labor of copy and paste.

Dim strQueryName as String
strQueryName = "qry_2ndQuarter"

Set objDB = CurrentDb

If objDB.QueryDefs(strQueryName).SQL = "SELECT *;" & vbCrLf Then

'' Delete the query
objDB.QueryDefs.Delete(strQueryName)

'' Re-create the query
qdef = objDB.CreateQueryDef(strQueryName, "SELECT * FROM Sales WHERE quarter=2")
End If

'' Output to Excel
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, "2ndQuarter.xls", True

Note that after Access deletes the query's SQL, it seems that Access rebuilds it by putting in SELECT *; as the SQL (In some versions of Access, the emptied query can be SELECT; instead of SELECT *;). Access also adds a carriage return character after the semi-collon so in the code we need to check if the SQL is equal to "SELECT *;" & vbCrLf

Hope this article helps you happily complete your work and solve the exact problem you are probably facing.

Other Recent Articles from the MS Access category:

1.	Examples of MS Access DateDiff function used in query and VBA code
2.	MS Access DateDiff function
3.	How to find out your computer name and username by VBA
4.	Examples of MS Access DatePart function
5.	MS Access DatePart function
6.	Examples of MS Access DateAdd function
7.	MS Access DateAdd function
8.	IIF function basics - the CASE statement of MS Access
9.	MS Access Date Expression
10.	Solved: MS Access error "The text is too long to be edited"
11.	Create MS Access Combo Box essential properties by VBA code
12.	Create MS Access Combo Box essential properties manually
13.	How to do text search in MS Access programmatically
14.	Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
15.	How to easily get a list of field names in MS Access
16.	How to count distinct records in MS Access
17.	How to do transaction based processing in MS Access
18.	How to open a document (local/network file or web page) from MS Access
19.	How to use ADOX to create unique composite index - the VBA approach
20.	How to do cross-table update queries in MS Access - the right way
21.	Three efficient ways to get the number of records by using VBA
22.	How to create a composite unique index (not as a primary key) in MS Access
23.	Use VBA to get the correct number of records in a Recordset object
24.	Disable Access Prompt when a record is changed, table deleted, or action queries run
25.	How to hide and unhide a MS Access object
26.	How to return multiple values from a VBA function (Part 3)
27.	How to return multiple values from a VBA function (Part 2)
28.	How to return multiple values from a VBA function (Part 1)
29.	Three ways to programmatically duplicate a table in MS Access by VBA
30.	Create a DLL by CSharp or VB.Net for VBA
31.	How to correctly reference and call a DLL
32.	How to register a C# or VB.Net DLL
33.	Email address validation by Regular Expressions using VBA
34.	How to unselect radio buttons in MS Access after it has been selected
35.	How to Change Query Timeout Value for MS Access SQL Queries
36.	What is Northwind Traders database

	Custom Search